Open of ADO Recordset failing, no clue why (1 Viewer)

mdlueck

Sr. Application Developer
Local time
Today, 13:51
Joined
Jun 23, 2011
Messages
2,631
[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...

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
At first I was getting this sort of error:

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
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:

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
Since adding that code, I have also received this type of error... which is the type the code presently throws.

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
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!
 
Last edited:

MarkK

bit cruncher
Local time
Today, 10:51
Joined
Mar 17, 2004
Messages
8,178
What is the value of domain in this SQL?
Code:
  strSQL = "SELECT cfg.keyvalue " & _
    "FROM cfg " & _
    "WHERE (((cfg.keyname)='" & keyname & "') AND ((cfg.domain)='" & [COLOR="Red"]domain[/COLOR] & "'));"
The code you posted never assigns it a value.
 

mdlueck

Sr. Application Developer
Local time
Today, 13:51
Joined
Jun 23, 2011
Messages
2,631
ggggrrrr.... ACCESS!!!!

Solved via adding [] around the column names, as I had in another working query. Success via plagiarism!

Code:
  '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 & "'));"
 

mdlueck

Sr. Application Developer
Local time
Today, 13:51
Joined
Jun 23, 2011
Messages
2,631
What is the value of domain in this SQL?
Code:
  strSQL = "SELECT cfg.keyvalue " & _
    "FROM cfg " & _
    "WHERE (((cfg.keyname)='" & keyname & "') AND ((cfg.domain)='" & [COLOR=Red]domain[/COLOR] & "'));"
The code you posted never assigns it a value.

It is a class attribute which the value of has been set prior to calling this method which runs the select.

Mouseover was correctly showing the value of this variable.

Copy/paste the resulting value of strSQL was executing perfectly in an interactive query window.
 

mdlueck

Sr. Application Developer
Local time
Today, 13:51
Joined
Jun 23, 2011
Messages
2,631
Quite a useful list. Bewildering that there are so many no-no words! Thank you.
 

mdlueck

Sr. Application Developer
Local time
Today, 13:51
Joined
Jun 23, 2011
Messages
2,631
Thank you for the link to the "Database Issue Checker Utility". The only complaint it had was of that one column name. Already fixed / changed this morning. The others were merely background noise. :D
 

Users who are viewing this thread

Top Bottom