Access: Execute SQL against own MDB

http://www.apostate.com/wsh-adsi-recipes


 

 

You can use the SQL property of a QueryDef object to change the underlying SQL statement of a query. For example, the following block of Microsoft Visual Basic (Microsoft Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) code changes the underlying SQL statement of the query Employee List so that the query selects all records in the Employees table:

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

 


 

RunSQL Method

Example

The following example updates the Employees table, changing each sales manager's title to Regional Sales Manager:

Public Sub DoSQL()

Dim SQL As String

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _
"WHERE Employees.Title = 'Sales Manager'"

DoCmd.RunSQL SQL

End Sub

 

 


 

Build SQL Statements That Include Variables and Controls

Build SQL Statements That Include Variables and Controls

See AlsoSpecifics

When working with Data Access Objects (DAO) or ActiveX Data Objects (ADO), you may need to construct an SQL statement in code. This is sometimes referred to as taking your SQL code "inline". For example, if you're creating a new QueryDef object, you must set its SQL property to a valid SQL string. But if you are using an ADO Recordset object, you must set its Source property to a valid SQL string.

The easiest way to construct an SQL statement is to create a query in the query design grid, switch to SQL view, and copy and paste the corresponding SQL statement into your code.

Often a query must be based on values that the user supplies, or that change in different situations. If this is the case, you'll need to include variables or control values in your query. The Microsoft Jet database engine processes all SQL statements, but not variables or controls. Therefore, you must construct your SQL statement so that Microsoft Access first determines these values and then concatenates them into the SQL statement that's passed to the Jet database engine.

Building SQL Statements With DAO

The following example shows how to create a QueryDef object with a simple SQL statement. This query returns all orders from an Orders table that were placed after 3-31-96:

Public Sub GetOrders()

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

Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-96#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

End Sub

The next example creates the same QueryDef object by using a value stored in a variable. Note that the number signs (#) that denote the date values must be included in the string so that they are concatenated with the date value.

Dim dbs As Database, qdf As QueryDef, strSQL As String
Dim dteStart As Date
dteStart = #3-31-96#
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate" _
& "> #" & dteStart & "#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

The following example creates a QueryDef object by using a value in a control called OrderDate on an Orders form. Note that you provide the full reference to the control, and that you include the number signs that denote the date within the string.

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate" _
& "> #" & Forms!Orders!OrderDate & "#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

Building SQL Statements With ADO

In this section, we will build the same statements as in the previous section, but this time using ADO as the data access method.

The following example shows how to create a QueryDef object with a simple SQL statement. This query returns all orders from an Orders table that were placed after 3-31-96:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate >#3-31-96#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

The next example creates the same QueryDef object by using a value stored in a variable. Note that the number signs (#) that denote the date values must be included in the string so that they are concatenated with the date value.

Dim dbs As Database, qdf As QueryDef, strSQL As String
Dim dteStart As Date
dteStart = #3-31-96#
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate" _
& "> #" & dteStart & "#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)

The following example creates a QueryDef object by using a value in a control called OrderDate on an Orders form. Note that you provide the full reference to the control, and that you include the number signs that denote the date within the string.

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate" _
& "> #" & Forms!Orders!OrderDate & "#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)