• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Run-time error: <various numbers> Application-defined or Object-defined error opening a recordset from SQL in Excel VB

I have an Excel app that updates a SQL database via some VB code.  The SQL DB has a table named glossary and no views of the same name.  The table reserve_current has one table and a hundred views of the same name (owners are sg00... etc.)  I can't select from or update the reserve_current table no matter what I do.  Here is the stripped down code that I use to test.

Private Sub testRun()
' this Sub is used to start the test run and feed the seed values to UpdateInsert
Dim dbalance As Double, dAmt As Double, lres As Long
dbalance = 306.6
dAmt = 1234
lres = 370

UpdateInsert dbalance, dAmt, lres

End Sub

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Private Sub UpdateInsert(dBal As Double, dAmount As Double, lresType As Long)
Dim dbstemp As DAO.Database, recordSettemp As DAO.Recordset, sSQL1 As String, sSQL2 as string, sSQL3 as string
Dim strConnect As String,  testVal As Long

    'Reserve_current table
    Set dbstemp = OpenDatabase("", False, False, strConnect)
    sSQL1 = "select * from reserve_current where claim_id = " & ClaimID & " and reserve_type_code = " & lresType & ";"
    Set recordSettemp = dbstemp.OpenRecordset(sSQL1)  'this bombs no matter what I do.  I've hard coded the variables in sSqL1 but no joy.
    testVal = recordSettemp("rc_row_id")
    'get next row id  -- this works just fine
    sSQL2 = "select next_unique_id from glossary where table_id = 75;"
    Set recordSettemp = dbstemp.OpenRecordset(sSQL2)
    testVal = recordSettemp("next_unique_id")
    'update Glossary table -- this works just fine
    sSQL3 = "Update Glossary set next_unique_id = next_unique_id + 1 where table_id = 75;"
    dbstemp.Execute sSQL3

End Sub

* When the select from reserve_current bombs I get a Visual Basic error box [Run-time error: '3078' Application-defined or Object-defined error]
If I modify the sSQL1 statement to read "Select * from dbo.reserve_current where..." I get error number 3024
The table is definitely there.  The statement runs in ISQLW and the subsequent statements (selecting and updating the
glossary table) work fine.  Arrrrrgh!
3 Solutions

where are you getting the value for ClaimID from?

sSQL1 = "select * from reserve_current where claim_id = " & ClaimID & " and reserve_type_code = " & lresType & ";"

sSQL1 = "select * from reserve_current where claim_id = '" & ClaimID & "' and reserve_type_code = '" & lresType & "';"
dmeenanAuthor Commented:
ClaimID and ClaimantEID are Public long variables set elsewhere.  
When I change sSQL1 to "select * from reserve_current;" I still get [Run-time error: '3078' Application-defined or object-defined error].  I tried this to verify my syntax wasn't the problem.
you may be better off using ado. DAO is an extremely old technology and has several problems.

Also check this thread

dmeenanAuthor Commented:
if I re-dim dbstemp as ADODB.database and recordsettemp as ADODB.recordset what reference do I add (I presume I remove Microsoft DAO 3.6)?

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now