[SOLVED] Open of ADO Recordset failing, no clue why
I have been having quite a fight this morning with accessing a table in my FE DB via an ADO Recordset object.
I am working on creating cfg tables in both the client and server databases. Next up was to fetch the application version from each table. Then to compare and make sure the client and server code matches. Seems simple enough.
So my code for the server config table works perfectly / flawlessly. Using that I capture both the run-time domain (DEV / QA / PROD) and also the version string.
Next I built a class based on the class accessing the server table. Adjusted the SQL to be correct for the local FE table. The query string executes perfectly in a query window, but not so when run from an ADO object in VBA.
Da code...
At first I was getting this sort of error:
So I did a bit of searching for that, came across a MS document telling how to get additional information about such errors:
http://support.microsoft.com/kb/186063
So I added that to my error handler. However, not much help for the effort:
Since adding that code, I have also received this type of error... which is the type the code presently throws.
I have seen references that a reserved word has been used, which causes the error only when being run from VBA. No keywords jump out at me. Do you see one?
And pretty much the same query runs on the server... though that is a different ADO connection, which is doing pass-through queries.
Suggestions please??!?!?! Thanks!
I have been having quite a fight this morning with accessing a table in my FE DB via an ADO Recordset object.
I am working on creating cfg tables in both the client and server databases. Next up was to fetch the application version from each table. Then to compare and make sure the client and server code matches. Seems simple enough.
So my code for the server config table works perfectly / flawlessly. Using that I capture both the run-time domain (DEV / QA / PROD) and also the version string.
Next I built a class based on the class accessing the server table. Adjusted the SQL to be correct for the local FE table. The query string executes perfectly in a query window, but not so when run from an ADO object in VBA.
Da code...
Code:
'This API searches for the KeyValue based on the KeyName and Run-Time Domain
Public Function LocateKeyValue(keyname As String) As String
On Error GoTo Err_LocateKeyValue
Dim adoRS As ADODB.Recordset
Dim strSQL As String
'Define attachment to database table specifics
Set adoRS = New ADODB.Recordset
adoRS.ActiveConnection = CurrentProject.Connection
adoRS.Source = "cfg"
adoRS.CursorType = adOpenDynamic
adoRS.LockType = adLockPessimistic
'Define a query to look for the KeyValue based on the KeyName and Run-Time Domain
strSQL = "SELECT cfg.keyvalue " & _
"FROM cfg " & _
"WHERE (((cfg.keyname)='" & keyname & "') AND ((cfg.domain)='" & domain & "'));"
'Open query results
adoRS.Open strSQL
'Was no record found?
If adoRS.BOF Or adoRS.EOF Then
LocateKeyValue = ""
Else
'Fetch the values found
keyvalue = Nz(adoRS!keyvalue, 0)
'And return the fetched keyvalue
LocateKeyValue = keyvalue
End If
'Close the database table
adoRS.Close
Exit_LocateKeyValue:
'Clean up the connection to the database
Set adoRS = Nothing
Exit Function
Err_LocateKeyValue:
Call errorhandler_MsgBox("Class: clsObjCfgTbl, Function: LocateKeyValue()")
LocateKeyValue = ""
Resume Exit_LocateKeyValue
End Function
Code:
Date: 20110823 Time: 10:49:07 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Automation error
Unspecified error
http://support.microsoft.com/kb/186063
So I added that to my error handler. However, not much help for the effort:
Code:
Date: 20110823 Time: 11:29:40 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Automation error
Unspecified error
MessageText: Unspecified error
Code:
Date: 20110823 Time: 11:42:24 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Method 'Open' of object '_Recordset' failed
MessageText: Unspecified error
And pretty much the same query runs on the server... though that is a different ADO connection, which is doing pass-through queries.
Suggestions please??!?!?! Thanks!
Last edited: