Announcement

Collapse
No announcement yet.

ADODB Connection referring to Local path NOT Network

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    'C:\Documents and Setting\username\My Documents\folder\dbName.mdb' is not a valid path.
    '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
    Code:
      '' Database variables
      Dim cn As ADODB.Connection, rs As ADODB.Recordset
      Dim strDB As String, strPassword As String
      Dim strTable As String, strField As String
      
      Set cn = New ADODB.Connection
      Set rs = New ADODB.Recordset
        
      strDB = ThisWorkbook.Path & "\folder\dbName.mdb"  strTable = "tblHoliday"
      strPassword = "mypassword"
       
      cn.Provider = "Provider=Microsoft.Jet.OLEDB.4.0;"
    
      cn.Open cn.Provider & "Data Source=" & strDB & "; Jet OLEDB:Database Password=" & strPassword & ";"
      
      ' Opens recordset with criteria as above
      Dim strSQL As String
        
        strSQL = "SELECT * FROM " & strTable & " " & _
          "WHERE idNo=" & myID & " " & _
          "AND aDate=#" & Format(myDate, "mm/dd/yyyy") & "# " 'AND " & _
          "Status='" & myStatus & "'"
    
        rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
    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...

  • #2
    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..

    Comment


    • #3
      [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:
      strSQL = "SELECT * FROM `T:\Folder\Folder\dbName`.tblName " & _
            "WHERE aDate>=#" & Format(Range("aDateFrom"), "mm/dd/yyyy") & "# " & _
            "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
      Last edited by Ktrasler; September 13th, 2011, 05:37. Reason: Marked as solved

      Comment


      • #4
        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...

        Comment


        • #5


          Originally posted by Ktrasler View Post
          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:
              Set adoRS = New ADODB.Recordset
              strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & " FROM " & "[X:\Projects\Fundamental Understanding\Database\GroupNumbers." & TableName & "] WHERE " & LookUpFieldName & "=" & LookupValue & ";"
              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???

          Comment

          Working...
          X