MS-Access / Getting Started

---

Data Access with DAO

Accessing data is the reason you use databases, and a large proportion of your programming will usually revolve around manipulating those objects that deal with data: queries and recordsets. In this section, you take a detailed look at how to access and manipulate your database data using DAO objects.

Working with QueryDefs

When you build a query with the graphical Query Designer, you are building a QueryDef object in the default Access workspace. When you save the query, you are also appending a reference to it in the QueryDefs collection. You can also build a QueryDef in code, which is one of the purposes of this section.

You can think of permanent (Access workspace) QueryDefs as SQL statements that are compiled the first time they are executed. This is similar in concept to the way code is compiled. Once compiled, permanent queries run marginally faster than their temporary, unsaved counterparts, because Access does not need to compile them before execution. Temporary QueryDefs are useful when you don't need to save them, as when you create their SQL statements during runtime. You would normally build and run SQL statements in line with your code when you need to change its clauses depending on current operating conditions or the value of some variable.

Creating a QueryDef

To create a QueryDef, execute the CreateQueryDef method against the Database object. In Microsoft Access workspaces, if you set a QueryDef's Name property to something other than a zero-length string, it is automatically appended to the QueryDefs collection, and saved to disk. Omitting the Name property, or explicitly setting it to a zero-length string, results in a temporary (unsaved) QueryDef.

The following code demonstrates how to create a QueryDef in a Microsoft Access workspace:

Public Sub CreateQuery (strName As String, strSQL As String)
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    Set dbs = CurrentDb

    'Create the QueryDef
    'If the user supplies a name, the QueryDef will be
    'automatically appended to the QueryDefs collection
    Set qdf = dbs.CreateQueryDef(strName, strSQL)

    'If the user supplies a name, refresh the Navigation Pane
     If vbNullString <> strName Then Application.RefreshDatabaseWindow

    Set qdf = Nothing
    Set dbs = Nothing
End Sub

You can create a pass-through query to an ODBC data source by setting the QueryDef's Connect property to a valid connection string, after the query has been created. Pass-through queries enable you to run SQL statements directly on another database such as SQL Server or Oracle.

qdf.Connect = strConnectionString

Parameters

Although you can't append parameters to a QueryDef using DAO, you can create them by declaring them in the SQL as shown in the following code:

Sub CreateQueryWithParameters()

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim strSQL As String

    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("myQuery")
    Application.RefreshDatabaseWindow

    strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
    strSQL = strSQL & "SELECT * FROM [Table1] "
    strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
    qdf.SQL = strSQL

    Debug.Print qdf.Parameters.Count
    For Each prm In qdf.Parameters
	Debug.Print , prm.Name, prm.Type
    Next prm

    qdf.Close
    Set prm = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
End Sub

You can also specify a query parameter's value in order to specify the value of criteria to filter the query's output, or the selected records on which the query operates. For example, the following procedure sets a reference to an existing query called myActionQuery, sets the value of its parameter (Organization), and then executes the query:

Public Sub ExecParameterQuery()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("myActionQuery")

'Set the value of the QueryDef's parameter
qdf.Parameters("Organization").Value = "Microsoft"

'Execute the query
qdf.Execute dbFailOnError

'Clean up
qdf.Close
Set qdf = Nothing
Set dbs = Nothing

End Sub