Results 1 to 9 of 9

Thread: ADODB Connection referring to Local path NOT Network
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry ADODB Connection referring to Local path NOT Network

    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 & ";"
        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.
        
        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 by khabi21; Jun 10th, 2019 at 06:01 PM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    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?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    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.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    This,
    Code:
    Set adoCN = New Connection
    should be this.
    Code:
    Set adoCN = New ADODB.Connection
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    Quote Originally Posted by Norie View Post
    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 by khabi21; Jun 10th, 2019 at 06:50 PM.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    Have you tried using the UNC path instead of the mapped drive?
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    Oct 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    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 by khabi21; Jun 10th, 2019 at 07:08 PM.

  8. #8
    New Member
    Join Date
    Oct 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    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.

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,142
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: ADODB Connection referring to Local path NOT Network

    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 [].
    If posting code please use code tags.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •