[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-07-20
7
Medium Priority
?
627 Views
Last Modified: 2013-12-05
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.
0
Comment
Question by:tomllewis
  • 3
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19533085
on which line do you get the error?
0
 
LVL 39

Expert Comment

by:stevbe
ID: 19533106
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
 

Author Comment

by:tomllewis
ID: 19533278
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Expert Comment

by:stevbe
ID: 19533326
and in the example they use CurrentDB to talk directly to SQL Server?
0
 

Author Comment

by:tomllewis
ID: 19533485
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
 
LVL 39

Accepted Solution

by:
stevbe earned 2000 total points
ID: 19533498
did you try the code in my first post?
0
 

Author Comment

by:tomllewis
ID: 19533542
My bad.  Thank you very much - your code works perfectly.  I guess it's time to change to ADO.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

872 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