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

Posted on 2004-11-16
Last Modified: 2008-01-09
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!
Question by:dmeenan
    LVL 15

    Assisted Solution


    where are you getting the value for ClaimID from?

    LVL 1

    Assisted Solution

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

    Author Comment

    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.
    LVL 6

    Accepted Solution

    you may be better off using ado. DAO is an extremely old technology and has several problems.

    Also check this thread

    Author Comment

    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)?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now