SQL VBA Tutorial
By Tiesha Whatley
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.
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."
Click on "Insert" and then "Module" in the Visual Basic Editor.
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.
'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.
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'"
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'."
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.
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."
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
Set .ActiveConnection = Conn .CursorType = adOpenStatic .Source = strSelectQuery .Open
Set rsDelete = New ADODB.Recordset
Set .ActiveConnection = Conn .CursorType = adOpenStatic .Source = strDeleteQuery .Open
Set rsInsert = New ADODB.Recordset
Set .ActiveConnection = Conn .CursorType = adOpenStatic .Source = strInsertQuery .Open
Set rsUpdate = New ADODB.Recordset
Set .ActiveConnection = Conn .CursorType = adOpenStatic .Source = strUpdateQuery .Open
'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
Tiesha Whatley has been writing for over 10 years. She has been published in "Marie Claire," "Ebony" and "Modern Bride" magazines. She holds a Bachelor of Science in English from the University of Alabama at Birmingham and has been working in the wedding planning industry for over 13 years.