Database-driven programs built using Microsoft's Visual Basic for Applications (VBA) can utilise SQL (Structured Query Language) for data management. When you build database application, you can use SQL statements to query, update and insert data. Query statements are the most common in database applications, designed to retrieve stored data records. If a user interface is involved, the application may also need to pass data to the database, for example by including a string variable in an SQL query string. On the VBA platform, including a variable in any SQL statement requires only a few straightforward steps.
- Skill level:
Other People Are Reading
Prepare your variable. If your application is using a Graphical User Interface (GUI) to retrieve the text string you wish to include in an SQL statement, save it into a variable. The following example code demonstrates creating a string variable with the text content hard-coded into the program for demonstration: Dim custRef As String = "marysmith"
The variable name should be tailored to the content of the data you are using. In this case the string is a customer reference, perhaps for a retail or other service organisation. The variable could be used to retrieve customer records from a database table.
Build your SQL string. Before you add the variable, build the rest of your SQL statement and store it as a string variable. The following code demonstrates storing the first part of a basic query string with a "where" clause into a variable: Dim queryString As String = "SELECT * FROM Customers WHERE User = "
The query will select values in all columns from a database table named "Customers", selecting only those records that match a particular string you have not yet added. This will be the variable string and you will append it to the query string.
Append the variable to your SQL string. Extend the query string variable declaration to include your variable. The following code demonstrates including it as part of the "where" clause: Dim queryString As String = "SELECT * FROM Customers WHERE User = """ & custRef & """"
There are multiple quotation marks in place to make the query work effectively. First, we need the query to include one set of quotes around the string as follows: SELECT * FROM Customers WHERE User = "marysmith"
To include quotation marks in a string variable in VBA, you need to include two sets, as follows: SELECT * FROM Customers WHERE User = ""marysmith""
We need to use another set of quotes because we are including the string using its variable name: SELECT * FROM Customers WHERE User = """ & custRef & """
The ampersand characters surround the variable so that it is attached to the rest of the string. One final quotation mark on the end of the query marks the end of the query string variable declaration.
Tips and warnings
- If you find you are losing track of quotation marks and want to tidy your code up, you can use the "Chr$" function.
- If your variable represents a number rather than a string, the SQL statement does not need to contain quotes around the value, but you will still need one set around the variable name.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for