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
The first part of the code is
- '' 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...