ADODB Connection referring to Local path NOT Network

[khabi21]

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:

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:

Sub LookUpGroup()'Use the DBVLookup function


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


End Sub

 

[Norie]

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]

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]

This,

Set adoCN = New Connection

should be this.

Set adoCN = New ADODB.Connection

 

[khabi21]

This,

Set adoCN = New Connection

should be this.

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.

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly

 

[Norie]

Have you tried using the UNC path instead of the mapped drive?

 

[khabi21]

Just tried replacing mapped drive with UNC. Same error.

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

 

[khabi21]

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]

I'm confused, in your first post you say this is the error you are getting,

"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 [].