This article is aimed at getting you started using Access Recordsets & QueryDefs. This article assumes that you have written a little VBA Code and are now ready to start working with Data Access Objects (DAO).

DAO Programming

There are 2 populare data enabled programming models for Access, the first is called DAO (Data Access Objects) and the second is called ADO (Active Data Objects).

Developers often have a preference for one over the other, but DAO is in our opinion more powerful and simple to use. Although for a while DAO fell out of favour with Microsoft, in Office 2007 with the introduction of multivalued fields and attachements focus is now back on DAO and JET database technology. ADO by contrast was designed for use with ASP and was a lightweight technology suited to web applications, and now this technology has moved on to ASP.net and lives a life outside of Access.

With some older versions of Access you need to enable the DAO libraries before you can start programming. This was because between Access 97 and Access 2003, Microsoft shifted emphasis first to ADO and then back again to DAO. Now with Access 2007 emphasis is firmly back in the DAO camp.

Top of Page

Why Program With Recordsets & QueryDefs

Doing things with SQL is nearly always more efficient that writing program code. However, there are many circumstances when you need program code to hang a sequence of operations together, or repeat a series of complex tasks.

At it's simplest level this programming allows you to mimic a series of actions that you could perform interactively with a table of data, and at its most complex it allows the construction and execution of sophisticated SQL in program code where the SQL is genarated on-the-fly in your code.

Top of Page

Getting Started With DAO

As indicated in the text above, to get started with some versions you need to make the DAO programming library available. So if you are using Access 2000, 2002/XP then you will need to enable the following references.

In a program module, display the references dialog

In Access 2000/XP, if you do not have the "Microsoft DAO Library" added then search down the list and check the box to select it.

For more information on references see List of references that you must set when you work with Access 2002

Top of Page

Working With An Existing Action Query

Firstly we consider a couple of different ways we can run an action query. The following query prompts for you to enter the name of a country to be changed, and the value to change the name to. The query is parameterised (Query - Parameters Menu).

Anything you type in a query which is inside [...] and is not a field name is considered to be a parameter. To ensure that all our program code will work it is important to define these as formal parameters using the dialog shown above.

The first method you can use to execute a query is to use a Macro with the OpenQuery command . You can also use the SetWarnings command to switch OFF and then back ON warning (be careful if anything goes wrong and warnings are OFF, you don't get prompted when you try and delete anything from your database, like a form! This is a DANGEROUS strategy).

The next strategy could be to use program code to execute the above Macro commands using the same OpenQuery command.

Again you can switch OFF Warnings at you peril!

Finaly a third approach is to use the Execute command ( a useful quick way of emptying tables), now whilst this works for queries like in the following :-

Currentdb.Execute "DELETE * FROM tblCustomers"

Neither of these three techniques will work with our paramterised query. They won't work when parameters are involved, beacuse this involves user interaction,

Moving on, the code below shows how this can be achieved using DAO programming code.

In the above the program code supplies the parameters.

Top of Page

Recordsets & Select Queries

Having seen now how we can execute action queries which can if required have parameters, we turn our attention to select queries and recordsets.

A Recordset is just like looking at the Datasheet view of the results from a query or a table using program code. And just as you can interactively move across columns, down rows, change values and search etc., similar operations can be undertaken in program code.

What follows is a tiny insight into what can be achieved here. The first example shows how to simply move through a recordset and edit some values.

In the above example, we are using a Dynaset one of several different kinds of recordsets (Dynasets are probably the best choice for most purposes, as they suffer from the least restrictions).

A better solution to the above unrestricted select statement would be to change the SQL to read "SELECT * FROM Customers WHERE Country = 'uks'"

Here is just one of many alternatives to the above, this one uses the find operation to locate specific records.

Top of Page

Using Both A Recordset And A QueryDef

The next example shows how you can open a recordset on a parameterised query.

Top of Page

DBEngine vs CurrentDB

There are 2 ways to refer to the database. The main difference is that using DBEngine(0)(0) is meant to be slightly faster, but the advantage of CurrentDB is you can create objects on-the-fly and you don't need to refresh the db object to see them.

Top of Page

Recordset and DAO.Recordset

With Access 2003 you must use the DAO prefix if you have both DAO and ADO libraries loaded to make sure you do not refer to the ADO objects (which can be given a prefix ADODB if desired). So if you start getting basic errors try using a DAO prefix to make an explicit reference here.

Top of Page

Modifying SQL On-The-Fly

The next example shows how you can build SQL on-the-fly and have the changes saved to the underlying query.

Top of Page

Creating Temporary Queries

If use create a query without giving the query a name, then the query is temporary and does not get saved. If you supply a name then the query will be created.

Now to create a query.

This article has provided a number of examples showing how you can start to work with both recordsets and querydefs in your code. The sample code can be downloaded from here.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz