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
'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
'' 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...