Solved

ADO Recordset problem

Posted on 2014-02-10
14
396 Views
Last Modified: 2014-02-11
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?
0
Comment
Question by:Dale Massicotte
  • 7
  • 6
14 Comments
 
LVL 84
ID: 39848674
You need to set "cn" to a New connection:

Set cn = New ADODB.Connection
Set cn = CurrentProject.AccessConnection
0
 

Author Comment

by:Dale Massicotte
ID: 39848688
I added your line of code but had no affect on fixing the problem

All is the same
0
 
LVL 84
ID: 39848692
What line of code is throwing the error?
0
 

Author Comment

by:Dale Massicotte
ID: 39848693
Me.ReqPickupTime = rst.Fields("ReqPickupTime")
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
ID: 39849773
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
 
LVL 84
ID: 39850001
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
 

Author Comment

by:Dale Massicotte
ID: 39851168
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
ID: 39851359
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
 

Author Comment

by:Dale Massicotte
ID: 39851489
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
 

Author Comment

by:Dale Massicotte
ID: 39851504
Scott would an index on the SwiftTitle field help to speed up my query even more?
0
 

Author Comment

by:Dale Massicotte
ID: 39851511
Just to check

Msgbox cn.State returned 1

Is this the value I am looking for?
0
 
LVL 84
ID: 39851780
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
 
LVL 84
ID: 39851784
Msgbox cn.State returned 1
Yes. The states are:

adStateClosed      = 0      
adStateOpen =      1      
adStateConnecting = 2      
adStateExecuting = 4      
adStateFetching = 8
0
 

Author Comment

by:Dale Massicotte
ID: 39851807
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

914 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

14 Experts available now in Live!

Get 1:1 Help Now