Getting error "Object variable or with variable not set" in ADP DAO procedure.

O.K. my brain is about to explode.  I have the following code in an Access ADP (2003):
Private Sub cboSelectTag_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * From Taglines", dbOpenDynaset, 512)
    If Not rs.EOF Then
    Me.txtStoryPoint = rs(0)
    End If
End Sub

I get an error - Object variable or Block with variable not set.  The recordset is trying to adderss a SQL Server 2005 table with an identity field. I have a reference set for Microsoft DAO 3.6 Object Library.
tomllewisAsked:
Who is Participating?
 
stevbeCommented:
did you try the code in my first post?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
on which line do you get the error?
0
 
stevbeCommented:
I didn't think you could use DAO / CurrentDB in an ADP

Private Sub cboSelectTag_AfterUpdate()
    Dim rs As ADODB.Recordset    
    Set rs = New ADODB.Recordsert
    rs.Open "SELECT * TagLines", CurrentProject.Connection
    If Not rs.EOF Then
        Me.txtStoryPoint = rs(0)
    End If
    rs.Close
    Set rs = Nothing
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tomllewisAuthor Commented:
The code blows up on "Set rs = db.OpenRecordset("Select * From Taglines", dbOpenDynaset, 512)"

I am using Microsoft's book "Access Projects With SQL Server" which shows examples of both DAO and ADO.  I am not against ADO, I just find it needlessly complicated as I am only using it to code within Access.  I find the handling of parameters particularly bewildering.
0
 
stevbeCommented:
and in the example they use CurrentDB to talk directly to SQL Server?
0
 
tomllewisAuthor Commented:
Yes thet do, but the book is quite old.  2002, I think.  I know that this is old style but it has always worked in MDB projects.

I have no objections to ADO, I'm just not used to using it.  Most of my work is in SQL Server working with data cubes.  Occasionally I need to create an Access user interface to a SQL Server database.  What would the ADO code look like for this?
0
 
tomllewisAuthor Commented:
My bad.  Thank you very much - your code works perfectly.  I guess it's time to change to ADO.
0
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.

All Courses

From novice to tech pro — start learning today.