Access VBA SQL Examples

This tutorial contains examples of using SQL with VBA Access. As you will see below, to run SQL queries in Access with VBA you can use either the DoCmd.RunSQL or CurrentDb.Execute methods.

SQL Select

This example will use the SQL Select statement to open a recordset:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from Table1 where num=0", dbOpenDynaset)

SQL Update Table

This line of VBA code uses the DoCmd.RunSQL method to update a Table:

DoCmd.RunSQL ("UPDATE table1 SET num =0 where num=999")

Or you can use the .Execute method of the database object:

CurrentDb.Execute "UPDATE table1 SET num =0 where num=999"

SQL Alter Table

This line of VBA code uses the DoCmd.RunSQL to Alter a Table:

DoCmd.RunSQL ("ALTER TABLE Table3 ADD COLUMN Salary money")

Or with the the .Execute method of the database object:

CurrentDb.Execute "ALTER TABLE Table3 ADD COLUMN Salary money"

Drop Table

This line of VBA code uses the DoCmd.RunSQL to Alter a Table:

DoCmd.RunSQL ("DROP Table Table1")

Or with the the .Execute method of the database object:

CurrentDb.Execute "DROP Table Table1"

SQL Delete

This code uses the DoCmd.RunSQL to delete records from a table:

DoCmd.RunSQL ("DELETE FROM table1 where num=999")

Or with the the .Execute method of the database object:

CurrentDb.Execute "DELETE FROM table1 where num=999"

SQL Insert Into

This instance of DoCmd.RunSQL insert records into a table:

DoCmd.RunSQL ("INSERT INTO Table1 ( LastName, dob, num ) VALUES ('L1',#01/01/2001#,78)")

Or with the the .Execute method of the database object:

CurrentDb.Execute "INSERT INTO Table1 ( LastName, dob, num ) VALUES ('L1',#01/01/2001#,78)"

SQL Create Table

This code will create a Table using SQL:

CurrentDb.Execute "CREATE TABLE Table1(KeyID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Field1 TEXT, Field2 TEXT)"

Or with the the .Execute method of the database object:

CurrentDb.Execute "CREATE TABLE Table1(KeyID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Field1 TEXT, Field2 TEXT)"

Create Index

This line of VBA code uses the DoCmd.RunSQL to Alter a Table:

DoCmd.RunSQL ("CREATE INDEX " & IndexName & " ON " & TableName & "(" & ColumnName & ");")

Or with the the .Execute method of the database object:

CurrentDb.Execute "CREATE INDEX " & IndexName & " ON " & TableName & "(" & ColumnName & ");"

Drop Index

This line of VBA code uses the DoCmd.RunSQL to Alter a Table:

DoCmd.RunSQL "DROP INDEX ID ON Table1;"

Or with the the .Execute method of the database object:

CurrentDb.Execute "DROP INDEX ID ON Table1;"

Create Database

This code will create a database (no SQL):

Access.DBEngine.CreateDatabase "c:\Temp\testDB1.accdb", DB_LANG_GENERAL

SQL Queries

Open Query

You can use DoCmd.OpenQuery to open a saved query:

DoCmd.OpenQuery "qry_1", acViewNormal, acEdit

Execute Query

CurrentDB.Execute will execute a query:

CurrentDb.Execute "qry_1", dbFailOnError

Export Query to Excel

DoCmd.OutputTo will export a query to Excel:

DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, "c:\temp\ExportedQuery.xls"