Solved

ADO Recordset problem

Posted on 2014-02-10
14
397 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Top 1 of each supplier 55 55
Solved: How can I prevent deletions from a form in datasheet view? 5 27
User Level Security 6 38
Calculation in Access 5 25
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…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

776 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