Navigate a Query (1 Viewer)

BernardAtherton · Sep 29, 2003

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

dcx693 · Sep 29, 2003

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?

Mile-O · Sep 29, 2003

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.

BernardAtherton · Sep 29, 2003

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

dcx693 · Sep 29, 2003

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.

Rich · Sep 29, 2003

dcx693 said:
Mile is THE MAN! (or woman, I'm not sure)

BernardAtherton · Sep 29, 2003

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

Mile-O · Sep 29, 2003

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?

dcx693 · Sep 29, 2003

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)

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.

Mile-O · Sep 29, 2003

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.

BernardAtherton · Sep 29, 2003

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!

rockman · Sep 30, 2003

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

Cosmos75 · Oct 1, 2003

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.

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?

Mile-O · Oct 1, 2003

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.

just be aware that that's only the basic stuff. There's more to it.

dcx693 · Oct 1, 2003

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

mikemaki · Oct 1, 2003

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.

Cosmos75 · Oct 1, 2003

Mile-O-Phile said:
just be aware that that's only the basic stuff. There's more to it.

I figured as much! BUt it gives me a starting point to play around with!

dcx693,
Thanks for the ADO version!! This is great!

Cosmos75 · Oct 3, 2003

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?

mikemaki · Oct 3, 2003

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.

Mile-O · Oct 4, 2003

mikemaki, what does a listbox have to do with anything?

Navigate a Query (1 Viewer)

New member

Registered User.

Back once again...

New member

Registered User.

Rich

Guest

New member

Back once again...

Registered User.

Back once again...

New member

Senior Member

Registered User.

Back once again...

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Back once again...

Similar threads

Users who are viewing this thread