ADO Recordset problem

I cannot use DAO.  This is MS ACCESS PROJECT .adp

I am trying to update some fields by pulling a record via ADO.  I keep getting the
'object required' error.  Here is my code:

   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cn = CurrentProject.AccessConnection

   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM jobs WHERE [SwiftTitle] = '" & Me.[cmbSwiftSelect1] & "'"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
      
       Me.ReqPickupTime = rst.Fields("ReqPickupTime")
            
   End With

   Set rs = Nothing
   Set cn = Nothing

Open in new window


Does anything look wrong?
Dale MassicottePresidentAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
nothing is returned - even if I use
Either your SQL is wrong, or your connection is wrong. With an ADP I'm not sure what the connection points to, although I'd assume it would be the SQL Server + Database used for the ADP connection.

Try using Connection instead:
Set cn = CurrentProject.Connection

Then check the State:

Msgbox cn.State

I generally work with ADO recordsets like this:

rst.Open "SELECT * FROM Blah", cn

Try that (substituting your objects and SQL) without the other qualifiers.

What version of Access are you doing this on? We're seeing more and more reports of odd behavior with ADPs running on newer versions.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need to set "cn" to a New connection:

Set cn = New ADODB.Connection
Set cn = CurrentProject.AccessConnection
0
 
Dale MassicottePresidentAuthor Commented:
I added your line of code but had no affect on fixing the problem

All is the same
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What line of code is throwing the error?
0
 
Dale MassicottePresidentAuthor Commented:
Me.ReqPickupTime = rst.Fields("ReqPickupTime")
0
 
Dale FyeCommented:
that generally means that either the field "ReqPickupTime" or the control Me.ReqPickupTime do not have the name, as spelled.  Probably the field name.  Check the spelling of the field name in your table.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Check to be sure your recordset is filled:

If Not (rst.BOF and rst.EOF) Then
  '/ data was found
  Me.ReqPickupTime = rst("ReqPickupTime")
End If
0
 
Dale MassicottePresidentAuthor Commented:
Scott

I checked with your code and nothing is returned - even if I use

      .Source = "Jobs"    '<------------- Main table containing 200,000+ records

So what am I missing?
0
 
Dale MassicottePresidentAuthor Commented:
Sticking with 2003 MS Access working on a MSSQL2008 server.

Wish not to upgrade for 2 reasons

.adp's are buh-bye for the most part

and
 
later versions of MS Access do not show multiple tabs along the windows task bar for different forms.


I wish to create a windows application to replace it (at some point)

as you can see I have a lot to learn until then.

I am about to accept this question as answered soon now that I see there is a connection and I am returning values   :)
0
 
Dale MassicottePresidentAuthor Commented:
Scott would an index on the SwiftTitle field help to speed up my query even more?
0
 
Dale MassicottePresidentAuthor Commented:
Just to check

Msgbox cn.State returned 1

Is this the value I am looking for?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Scott would an index on the SwiftTitle field help to speed up my query even more?
Generally yes, it would. But you can over-index too, so it's something of a guessing game. An index is a good idea if you have a lot of non-repeating values. Fields that hold larger text values are sometimes not a good fit - but, again, you just have to try them to see if they have an adverse affect.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Msgbox cn.State returned 1
Yes. The states are:

adStateClosed      = 0      
adStateOpen =      1      
adStateConnecting = 2      
adStateExecuting = 4      
adStateFetching = 8
0
 
Dale MassicottePresidentAuthor Commented:
Thanks for all the value-added input

Would have helped if I noticed rs <> rst    lol


oops
Dim rs As ADODB.Recordset
Me.ReqPickupTime = rst.Fields("ReqPickupTime")


nonetheless I still believe that this is valuable info for people that don't open their eyes and look at their own code.  The devil is always in the details.
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.