ADODB Connection referring to Local path NOT Network

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
Hello, I've been trying to fix this all day with no luck. The goal of this code is to use a function to look up a value in a table in an Access database (.accdb) and return a different value.

I am running the code below, and the adoCN works just fine. It creates a locked .accdb file showing that it is connected. Then when I try to run adoRS.open reffering ot the adoCN connection which refers to a network drive location, it errors with "Could not find file 'C:\Users\xxxx\Documents\cg.mdb'".

It tries to look in a local C:drive location, and on top of that it tries to look for an .mdb file on the C:drive. Even though the adoCN.provider is ACE 12.0.

Please help. How do I get it to point to the network location and for a .accdb file?

Module 1:
Code:
Dim adoCN As ADODB.Connection
Dim strSQL As String


Const DatabasePath As String = "X:\Projects\Fundamental Understanding\Database\GroupNumbers.accdb"


'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return


Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant


Dim adoRS As ADODB.Recordset


If adoCN Is Nothing Then SetUpConnection


    Set adoRS = New ADODB.Recordset
    strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & TableName & " WHERE " & LookUpFieldName & "=" & LookupValue & ";"
[B]    adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly     'This is where the code errors b/c it looks in C:drive not where I point in the adoCN connection.[/B]
    
    If adoRS.BOF And adoRS.EOF Then
        DBVLookUp = "Value not Found"
        Else
        DBVLookUp = adoRS.Fields(ReturnField).Value
    End If


adoRS.Close


End Function


Sub SetUpConnection()


On Error GoTo ErrHandler


Set adoCN = New Connection
With adoCN
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = DatabasePath
    '.DataSource = DatabasePath
    .Open
End With
Exit Sub


ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub
Module 2:
Code:
Sub LookUpGroup()'Use the DBVLookup function


ProjectNumber = DBVLookUp("cg.vwGroups", "GroupNumber", "P60000", "ProjectNum")


End Sub
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Why are you setting the ConnectionString property of the connection to the path to the database?

Shouldn't it be the DataSource property that's set to the database path?
 

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
I've tried using .DataSource as well, as you can see I have it commented out. It errors when I use it, saying "Object doesn't support named arguments.". If I put a string of the database path directly in instead of using DatabasePath variable, it gives me the same error.

Using ConnectionString makes the connection to the database.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
This,
Code:
Set adoCN = New Connection
should be this.
Code:
Set adoCN = New ADODB.Connection
 

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
This,
Code:
Set adoCN = New Connection
should be this.
Code:
Set adoCN = New ADODB.Connection
Same issue. I tried it with both the .ConnectionString and the .DataSource properties.

The connection in the SetUpConnection sub is working just fine. It creates a connection and shows the locked read only .accdb in the directory. It's when it moves back to the DBVLookUp function and tries to open the Recordset when it blows up. When I step through and look at the adoCN value at this point, it is fine. It even says that the DataSource is in the X:\Drive. But it is still trying to open from the C:\Drive.

Code:
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Have you tried using the UNC path instead of the mapped drive?
 

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
Just tried replacing mapped drive with UNC. Same error.

It's finding the database just fine using either path in the SetUpConnection sub.
 
Last edited:

khabi21

New Member
Joined
Oct 12, 2016
Messages
35
Any chance the period in the table name is causing the issue??
I'm not very familiar with SQL. I'm checking with our IT department to see if they can change the name to remove the period.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
I'm confused, in your first post you say this is the error you are getting,
Code:
"Could not find file 'C:\Users\xxxx\Documents\cg.mdb'"
which, to me anyway, seems to indicate that the database you are trying to access is named 'cg.mdb'.

However the database you appear to be trying to set as the data source is 'GroupNumbers.accdb' and it's located in a completely different place.

Is the database 'cg.mdb' linked to the database 'GroupNumbers.accdb'?

As for having a period in the table name, not really a good idea but to get round that all you should need to do is enclose the table name in [].
 

Forum statistics

Threads
1,082,300
Messages
5,364,388
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top