Solved

ADO Recordset problem

Posted on 2014-02-10
14
405 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 85
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 85
ID: 39848692
What line of code is throwing the error?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

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

Assisted Solution

by:Dale Fye
Dale Fye 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 85
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
 
LVL 85

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 85
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 85
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

635 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