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:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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?
 
Upvote 0
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.
 
Upvote 0
This,
Code:
Set adoCN = New Connection
should be this.
Code:
Set adoCN = New ADODB.Connection
 
Upvote 0
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:
Upvote 0
Have you tried using the UNC path instead of the mapped drive?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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 [].
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top