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
strConnect = "ODBC;DSN=RMWORLD_SQL;DATABASE=RISKMASTER;UID=sa;PWD=3f4t45h3111;Address=DARTHVADER,1433"

    '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")
    recordSettemp.Close
   
    '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")
    recordSettemp.Close
   
    '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
    dbstemp.Close

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!
dmeenanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ColosseoCommented:
Hi

where are you getting the value for ClaimID from?

Scott
0
wyckedone1Commented:
Change:
sSQL1 = "select * from reserve_current where claim_id = " & ClaimID & " and reserve_type_code = " & lresType & ";"

to:
sSQL1 = "select * from reserve_current where claim_id = '" & ClaimID & "' and reserve_type_code = '" & lresType & "';"
0
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.
0
Partha MandayamTechnical DirectorCommented:
you may be better off using ado. DAO is an extremely old technology and has several problems.

Also check this thread

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21154131.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.