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:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,633
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,633
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,633
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,633
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 [].
 

Watch MrExcel Video

Forum statistics

Threads
1,101,904
Messages
5,483,646
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top