Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO Recordset problem

Posted on 2014-02-10
14
Medium Priority
?
411 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

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

Assisted Solution

by:Dale Fye
Dale Fye earned 200 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 1800 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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