SQL VBA Tutorial

Structured Query Language (SQL) is the computer language used for managing relational databases. Visual Basic for Applications (VBA) is the programming language developed by Microsoft to use with the Microsoft Office applications to create dynamic content. Microsoft Access is the database program inside of the Microsoft Office suite that uses both SQL and VBA to manage data and provide automation to systematic database functions. Using the programming shell behind Microsoft Access, you can connect to the main database, search for data, add new data and delete data by combining SQL and VBA programming code.

• 1

  Open the database you want to work with in Microsoft Access. Open the Visual Basic Editor by clicking on the "Database Tools" tab and then "Visual Basic."

• 2

  Click on "Insert" and then "Module" in the Visual Basic Editor.

• 3

  Type in the code to connect to the database. You will have to establish a connection in order to use SQL statements to the database.

  Sub SQLTutorial()

  'Set up your variables to hold the information

  Dim Conn as ADODB.Connection 'This is the actual connection

  Dim rsSelect as ADODB.Recordset 'This is where you will save the data

  Dim rsDelete as ADODB.Recordset

  Dim rsInsert as ADODB.Recordset

  Dim rsUpdate as ADODB.Recordset

  Dim strSelectQuery as String 'This is the actual SQL SELECT command

  Dim strDeleteQuery as String 'This will hold the SQL DELETE statement

  Dim strInsertQuery as String 'This is the SQL INSERT statement

  Dim strUpdateQuery as String 'This will hold the UPDATE statement

  'Type in the details to connect to the database that you want

  Set Conn = New ADODB.Connection

  With Conn

  .ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" &_

  "Data Source=C:\Documents\SampleDatabase.mdb"

  .Open

  End With

  Now your connection to the database has been made. Next, you will assign SQL statements to the variables you already declared above.

• 4

  Type in a "SELECT" SQL statement to select data from the database. A SELECT query is usually made up like this: "SELECT columns FROM table". You can add criteria to the SELECT statement by adding in the "WHERE" clause. For instance, you have a table called "Customers" that you want to search for all of the customers with the last name of "Smith." The VBA and SQL code will look like this:

  strSelectQuery = "SELECT * FROM tblCustomers WHERE LastName = 'Smith'

  The asterisk(*) is a wildcard, meaning that you want to pull all of the information, or columns, on anyone with the last name of "Smith." To select certain columns, you would use:

  strSelectQuery = "SELECT FirstName, LastName FROM tblCustomers WHERE LastName = 'Smith'"

• 5

  Type in the "DELETE" statement if you want to delete rows of data from a table. The code for that is:

  strDeleteQuery = "DELETE FROM tblCustomers WHERE LastName = 'Smith'"

  This statement will delete all rows where the customer has a last name of "Smith" from "Customers" table. The basic syntax for a DELETE statement is "DELETE FROM table WHERE column = 'value'."

• 6

  Type in the syntax to insert a new row into a table. Use the "INSERT" statement.

  strInsertQuery = "INSERT INTO tblCustomers VALUES (John, Smith, 123 Main Street, Cleveland, Ohio)"

  If you had a Customers table that has FirstName, LastName, Address, City and State columns, this statement will insert in the data into the right column. The comma tells the code to skip to the next column before entering in the values. Be sure that you are typing in the values in the correct order of the columns in the table so that your data is consistent.

• 7

  Type in the SQL statement to change a row of data. This is the "UPDATE" statement.

  strUpdateQuery = "UPDATE tblCustomers SET LastName='Jones', FirstName="Jim" WHERE LastName='Smith'"

  This statement changes everyone who has a last name of "Smith" to "Jones" and their first names to "Jim." You can change several columns of data at once in one UPDATE statement by separating the columns with commas. The basic syntax for an UPDATE is "UPDATE table SET column1=value1, column2=value2, column3=value3,... WHERE column = value."

• 8

  Type in the VBA code that will run the query and save the results to recordsets. Close out the VBA code.

  Set rsSelect = New ADODB.Recordset

  With rsSelect

  Set .ActiveConnection = Conn

  .CursorType = adOpenStatic

  .Source = strSelectQuery

  .Open

  End With

  Set rsDelete = New ADODB.Recordset

  With rsDelete

  Set .ActiveConnection = Conn

  .CursorType = adOpenStatic

  .Source = strDeleteQuery

  .Open

  End With

  Set rsInsert = New ADODB.Recordset

  With rsInsert

  Set .ActiveConnection = Conn

  .CursorType = adOpenStatic

  .Source = strInsertQuery

  .Open

  End With

  Set rsUpdate = New ADODB.Recordset

  With rsDelect

  Set .ActiveConnection = Conn

  .CursorType = adOpenStatic

  .Source = strUpdateQuery

  .Open

  End With

  'Type in the VBA code to do work with the data you have gathered through the SQL Statements.

  'You can use the data to post in forms, in other tables or in reports.

  'Close the recordsets and connection with you are done

  rsSelect.Close

  rsDelete.Close

  rsInsert.Close

  rsUpdate.Close

  End Sub