Navigate a Query

B

BernardAtherton

New member
Local time
Today, 00:23
Joined
Sep 19, 2003
Messages
7
Can VBA code access results of a query?

Hi there,

I have an access database that has a query in it that produces results that are view as a datasheet. I would like to code some VBA that reads the results one row at a time is this possible? If it is can anyone provide some code examples to get me started?

Many thanks.

Bernard
 
Definitely possible, but strictly speaking it's not really VBA that accesses records via code, it's either DAO or ADO, depending on your version of Access. What version do you use?
 
Yes, it's possible.

The two methods are DAO (Access 97)and ADO (current).

I, personally, use DAO. If you are using Access 2000 or above then you can set a reference to DAO in the code window by selecting Tools -> References, checking the correct box, and adjusting the priority.

An example:

Code: 
' create database and recordset objects
Dim db As DAO.Database
Dim rs As DAO.Recordset

' set database object to current database
Set db = CurrentDb
' set recordset object to your query (assumes query is [b]qryExample[/b]
Set rs = db.OpenRecordset("qryExample")

' things we can do with our recordset
With rs
    ' navigate our recordset
    .MoveNext
    .MovePrevious
    .MoveLast
    .MoveFirst

' add a record to our recordset
    .AddNew
    .Fields("MyField") = "NewValue"
    .Update

' edit a record in our recordset
    .Edit
    .Fields("MyField") = "NewValue"
    .Update

' delete a record from our recordset
    .Delete

' get a count of the records in our recordset
    .RecordCount

' close the recordset object (connection to the query)
    .Close

End With

' clear the defined objects
Set rs = Nothing
Set db = Nothing



When using the OpenRecordset line you can specify the type of recordset you wish to open. Press F1 over the command to get a list of these opening methods such as Snapshot, Dynamic, and AppendOnly.
 
Last edited:
At work we are using Windows NT machines, so I think its Access 2000, trouble is I am not at work at present so I cannot verify until tomorrow. Sorry. Do you have any code examples that I could see and try?

Thanks for your time and help.

Bernard
 
Mile is THE MAN! (or woman, I'm not sure), but anyway, if you're using Access 2000 or higher, and you need to use ADO for some reason, let us know and we can post some ADO code for you.
 
Wow, thanks all of you and Mile for your code example.

I will try this out tomorrow at work and let you know how I go.

I intend to have a macro that runs the query and then get the macro to run the new VBA which processes the results of the query one row at a time. Is this a good way to run the query and the VBA code?

Thanks again.

Bern
 
There's no need to open the query. Using the method above opens the query into the recordset rs.

I don't like to use macros as they offer little flexibility and an errors generated by them are not trappable. They are slower, too.

To open a query it's easier to just code:

Code: 
DoCmd.OpenQuery "qryExample"


.....anyhow, what do you mean by process the results of the query? What are you intending to do?
 
Bern, the query doesn't need to be opened before running the VBA code to process the query results. This line from Mile's code:
Set rs = db.OpenRecordset("qryExample")
opens the query programatically.

Mile is THE MAN! (or woman, I'm not sure)
Click to expand...
I was simply making a statment of fact that I did not know the Mile-O-Phile's gender. I was NOT stating an opinion. :(
 
Would it be easier if I changed my name to Male-O-Phile? Actually, on second thoughts, people might get the wrong idea from that. :cool:
 
Thanks again for the advice on not using a macro and getting the VBA to execute the Query.
With regard processing each row one at a time here is the problem I face.
The query returns many fields but I am interested in two :
Cust Number and Product customer has. For example:
Cust No | Product
1 | Credit Card
1 | Current Account
1 | Fixed Rate Loan
1 | Flexi Loan
1 | Savings account
1 | Investment account
2 | Current Account
2 | Credit Card
3 | Savings account
4 | Fixed rate loan
I want to read each row until change of userid, then when the userid changes I can say which products the customer has. So when I am asked to find a customer that ONLY has certain products I can easily find them. For example customer with JUST a Current Account and a Credit Card (eg cust number 2). I have been given SQL queries to do something similar but to be honest its too complicated for me!
 
I've been trying to accomplish this (query data into recordset) in ADO but having some problems with certain queries:

This works:
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Call rs.Open("qryContacts", cn, adOpenStatic)

This doesn't work:
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Call rs.Open("qryDuplicates", cn, adOpenStatic)

Contacts is a straight-forward select query.
Duplicates is fairly straight-forward with "Criteria" that designates text boxes on the form (e.g. Forms!frmContacts!txtLastName)

Is the presence of Criteria allowing one to work and the other to error?

Thanks,
Jeff
 
Last edited:
dcx693 said:
Mile is THE MAN! (or woman, I'm not sure), but anyway, if you're using Access 2000 or higher, and you need to use ADO for some reason, let us know and we can post some ADO code for you.
Click to expand...
Thanks to Mile-O-Phile for posting the DAO code for navigating records with DAO, been curious for awhile on how to to that.

dcx693,
How would I do the same thing in ADO instead?
 
Cosmos75 said:
Thanks to Mile-O-Phile for posting the DAO code for navigating records with DAO, been curious for awhile on how to to that.
Click to expand...

just be aware that that's only the basic stuff. There's more to it.
 
Here's an ADO version of Mile's code:
Code: 
' create connection and recordset objects
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

' set connection object to current database
Set cnn = CurrentProject.Connection
' set recordset object to your query (assumes query is qryExample)
Set rst = New ADODB.Recordset

' open the recordset
' note that in ado, the standard recordset is forward- and read-only
rst.Open "qryExample", cnn
' this would open the recordset as static with pessimistic locking:
'rst.Open "qryExample", cnn, adOpenStatic, adLockPessimistic

' things we can do with our recordset
With rst
    ' navigate our recordset
    .MoveNext
    .MovePrevious
    .MoveLast
    .MoveFirst

' add a record to our recordset
    .AddNew
    .Fields("MyField") = "NewValue"
    .Update

' edit a record in our recordset
    ' note that ADO does not require or provide a .Edit method
    .Fields("MyField") = "NewValue"
    .Update

' delete a record from our recordset
    .Delete

' get a count of the records in our recordset
' be careful in ado since the default type of recordset (forward-only)
'  will always return -1 for the recordcount
    Debug.Print .RecordCount
        
' close the recordset object (connection to the query)
    .Close

End With

' clear the defined objects
Set rst = Nothing
Set cnn = Nothing

End Sub
 
Another way to do it would be to set up a hidden list box that uses this query as its rowsource. You can then set up a loop from 0 to ListCount - 1 to read each line of the quey.
 
Mile-O-Phile said:
just be aware that that's only the basic stuff. There's more to it.
Click to expand...
I figured as much! BUt it gives me a starting point to play around with!
:D

dcx693,
Thanks for the ADO version!! This is great!
 
Supposed I have a table (Table1), with two fields (Field1 & Field2).

Table1
Field1, Field2
A,1
B,2
C,3

How would I copy the fields to a new table (tblNew)?

Say I wanted tblNew end up like this

Record 1 – A, 1, B, 2, C, 3
Record 2 – A, 1, C, 3, B, 2
Record 3 – B, 2, A, 1, C, 3
Record 4 – B, 2, C, 3, A, 1
Record 5 – C, 3, A, 1, B, 2
Record 6 – C, 3, B, 2, A, 1

(I already know how I need to navigate the fields to copy them, just need to know HOW to copy and paste them into a new table…)

Also, can I have the table like this

Record 1 – A, 1, B, 2, C, 3
Record 2 – A, 1, C, 3, B, 2
Record 3 – B, 2, A, 1, C, 3
Record 4 – B, 2, C, 3, A, 1
Record 5 – C, 3, A, 1, B, 2
Record 6 – C, 3, B, 2, A, 1
Record 7 – A, 1, , , ,
Record 8 - B, 1, , , ,
Record 9 - C, 1, , , ,

i.e. can I have a two-field record in a table that may have 6-fields?
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("table1")

With rst
For I = 0 To ListBox.ListCount - 1
x =
!Field1= ListBox.Column(0, I)
!Field2= ListBox.Column(1, I)
!Field3= ListBox.Column(2, I)
!Field4= ListBox.Column(3, I)
!Field5= ListBox.Column(4, I)
!Field6= ListBox.Column(5, I)
.Update
Next I
.Close
End With

I can never remember if columns start with 0 or 1.

If you only want to assign 4 values to a record, that's okay.
 
mikemaki, what does a listbox have to do with anything?
 
You must log in or register to reply here.

Similar threads

W
Very odd VBA error when using Split()
Replies
15
Views
564
WillockBoy
W
H
Solved Zero Results from Requery
Replies
8
Views
325
Pat Hartman
P
T
Disable Shift Key Bypass
2 3 4
Replies
62
Views
1,229
TheSearcher
T
W
Possible stream voor access???
Replies
5
Views
223
Isaac
Isaac
H
Extract a variable number of parts of text from a string
2
Replies
26
Views
557
AHeyne
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom