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. Sub SQLTutorial() Now your connection to the database has been made. Next, you will assign SQL statements to the variables you already declared above. 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’” 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’.” 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. 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.” Set rsSelect = New ADODB.Recordset With rsSelect End With Set rsDelete = New ADODB.Recordset With rsDelete End With Set rsInsert = New ADODB.Recordset With rsInsert End With Set rsUpdate = New ADODB.Recordset With rsDelect 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 Writer Bio
