ADODB Connection referring to Local path NOT Network

  • Hi All


    I am having a problem with an ADODB connection.


    On my local drive it has been working fine, but the problem comes when I put it on the network terminal. The path names are correct. If I test the strDB to the immediate window it returns the correct path but when the code gets to the rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic it fails with the following error.


    The path name is automatically reverting to the c:Drive


    Code
    1. 'C:\Documents and Setting\username\My Documents\folder\dbName.mdb' is not a valid path.
    2. 'Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.


    The first part of the code is


    I have tried adding chDir() but I can't get syntax correct.


    Just to confirm, this works fine on the local drive so I have been connecting to a local path fine...


    Any ideas... I hope this can be done...

  • Re: ADODB Connection referring to Local path NOT Network


    All


    Another thing that is strange with this is that the connection to the database seems fine. Once the connection is opened I can see that the access database has the locking file open.


    I have tried modifying the path to the actual network path, i.e \\server\folder\folder"


    I have also tried modifying the way in which the rs is created, such as Set rs.ActiveConnection = cn


    Both of the above do nothing different, i still get the error relating to the C:\drive.



    Can anyone assist... I am completely stumped..

  • [SOLVED] ADODB Connection referring to Local path NOT Network


    Well, I have solved my problem. Once I knew the connection was fine it got me just playing with the SQL... Then it hit me - after a very long time...


    Code
    1. strSQL = "SELECT * FROM `T:\Folder\Folder\dbName`.tblName " & _
    2. "WHERE aDate>=#" & Format(Range("aDateFrom"), "mm/dd/yyyy") & "# " & _
    3. "And aDate<=#" & Format(Range("aDateTo"), "mm/dd/yyyy") & "#"



    The thing I still don't get is Why!


    Why would I have to specify the pathname in the SQL when I have already established a connection.





    Thanks

  • Re: [SOLVED] ADODB Connection referring to Local path NOT Network


    Apologies anyone who has been looking at this.


    I have realized that I made a very stupid mistake...


    I managed to get it working if I reference the backend of my database, and then realized the problem was with the linked tables in the frontend.
    I removed all the linked tables and re-linked them and it seem to solve my problem...


    Apologies for wasting anyone's time...


  • I'm hoping you're still active on here Ktrasler. I'm running into this exact same issue and I can't seem to resolve it. When I make a new connection the locked access database file comes up just fine, but when I go to open a recordset it tries to find the C:// .mdb file. However, I'm connected to a .accdb file on a network drive. I tried entering the 'folder\folder\dbfilename\tablename as you said, but it's not working. I don't understand how that would work because the file name is going to have a .accdb at the end, and then you need to put the table name after that... How would that work?


    It ends up looking like this:

    Code
    1. Set adoRS = New ADODB.Recordset
    2. strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & "[X:\Projects\Fundamental Understanding\Database\GroupNumbers." & TableName & "] WHERE " & LookUpFieldName & "=" & LookupValue & ";"
    3. adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly


    This doesn't work though because it says it can't find the input table. GroupNumber.accdb is the file name. The TableName is vwGroups.


    The adoCN connection is made just fine. I don't get why the strSQL needs another path name when it is referring to adoCN in the adoRS.open???