Easily create SQL queries and execute them on a database. For an example on how to use this library with data objects, see the VBA-Drupal-Library. For other examples on using the library, refer to the unit tests.
Open Microsoft Visual Basic For Applications and import each cls and bas and frm file into a new project. Name the project SQLlib and save it as an xlam file. Enable the addin. Within Microsoft Visual Basic For Applications, select Tools>References and ensure that "Microsoft ActiveX Data Objects x.x Library", "Microsoft Scripting Runtime", and SQLlib is selected.
This Library allows developers to create static or dynamic SQL statements using VBA objects. If the table names and field names are all known by the developer, and only field values, and conditional values will be supplied by the user, an SQLStaticQuery might be the best option. All user-supplied information will be sanitized before being added to the query. It also provides a login box to discourage hard-coding database authentication details. The dynamic query generating objects are best for cases where table names and field names are part of larger data objects, and the queries themselves are created by a larger system. This larger system should provide data sanitizing options to ensure malicious data does make it into a query. the VBA-Drupal-Library is an example of such a system.
The unit tests demonstrate many ways to use each of the classes. To run the tests, Import all the modules from the testing directory into a spreadsheet, install the VBA-Unit-Testing library and type '=RunTests()' in cell A1. Ensure the Setup steps have all been successfully completed.
Create a new database connection:
Dim MyDatabase As SQLDatabase
Set MyDatabase = Create_SQLDatabase
MyDatabase.DBType = "mssql"
MyDatabase.DSN = "foodb"
Several different types of database execution can occur:
This form can be displayed to ask for the database credentials. This avoids having to hard-code username and passwords in the scrips.
'Open UserForm
Login.Show
'After Button is pressed assign values
MyDatabase.UserName = Login.Username
MyDatabase.Password = Login.Password
Unload Login
Developers can create static queries, while ensuring that user inputed data will interact with the database successfully. Items in bold are required
Dim MyStaic as SQLStaticQuery
Set MyStatic = Create_SQLStaticQuery
MyStatic.Query = "SELECT name FROM users WHERE id=:id"
MYStatic.addArgument ":id", 4
Will produce the SQL
SELECT name FROM users WHERE id=4;
The SQL statement can be easily reused with different user-supplied values for the ID without the need to recreate the object.
The SQLInsert Object has many options. Items in bold are required.
The Insert object can create both INSERT VALUES and INSERT SELECT statements. Multiple inserts can be performed in one statement if the values array is 2 Dimensional.
To produce this SQL Stament:
INSERT INTO users (username, first_name, password) VALUES ('admin', 'Alice', 'secret');
Use the Following in VBA-SQL-Library
'Initialize the object and assign a table name
Set MyInsert = Create_SQLInsert
MyInsert.table = "users"
'Set The Fields
MyInsert.Fields = Array("username", "first_name", "password")
'Set the Values
MyInsert.Values = Array(str("admin"), str("Alice"), str("secret"))
'Execute the query
MyDatabase.Execute MyInsert
To produce this SQL Stament:
INSERT INTO bank_account (account_number, open_date, user_id)
SELECT (10, 570000051, user_id) FROM users WHERE username = 'admin';
Use the Following in VBA-SQL-Library
'Create the SELECT Statement
Set SQL = Ceate_SQLSelect
'We don't escape the "user_id" because it is a field name, not a string
Sql.Fields = Array(10, 5770000051, "user_id")
Sql.Table = "users"
Sql.addWhere "username", str("admin")
'Initialize the object and assign a table name
Set MyInsert = Create_SQLInsert
With MyInsert
.table = "bank_account"
.Fields = Array("account_number", "open_date", "user_id")
Set .From = Sql
End With
'Execute the query, returning the newly created primary Key
ID = MyDatabase.InsertGetNewID(MyInsert)
To produce this SQL Stament:
INSERT INTO users (username, first_name, password) VALUES ('admin', 'Alice', 'secret'), ('editor', 'Bob', 'super-secret');
Use the Following in VBA-SQL-Library
'Initialize the object and assign a table name
Set MyInsert = Create_SQLInsert
MyInsert.table = "users"
'Set The Fields
MyInsert.Fields = Array("username", "first_name", "password")
'Set the Values
Dim Values2D(1) As Variant
Values2D(0) = Array("'admin'", "'Alice'", "'secret'")
Values2D(1) = Array("'editor'","'Bob'", "'super-secret'")
MyInsert.Values = Values2D
'Execute the query
MyDatabase.Execute MyInsert
The Select Object has many options. Items in bold are required
We can execute a select statement and receive the results as a single value, or an array of values:
SELECT id FROM users WHERE username='admin';
Set MySelect = Create_SQLSelect
With MySelect
.Fields = Array("id")
.Table = "users"
'Need to escape the string
.AddWhere "username", str("admin")
End With
ID = MyDatabase.Execute(MySelect, "id")
WHERE clauses can be added and grouped together. The following changes the query to:
SELECT id FROM users WHERE username='admin' AND id<10;
MySelect.AddWhere "id", 10, "<", "AND"
A SQLWhereGroup can abe be added using SQLSELECT.AddWhereGroup. This is necessary for a where clause like:
SELECT id FROM users WHERE (a=1 AND b=2) OR (c = 3 AND d = 4)
The SQLSelect Object can create Queries with "GROUP BY ... HAVING ..." sections.
... GROUP BY user_type HAVING age>1;
MySelect.GroupBy = Array("user_type")
MySelect.AddHaving = "age", "1", ">"
A query can be run as DISTINCT by flagging the Distinct property
MySelect.Distinct
We can add table aliases and joins as well
SELECT u.id, c.hex FROM users u INNER JOIN colors c ON u.favorite=c.name ORDER BY u.id DESC
Set MySelect = Create_SQLSelect
With MySelect
.Fields = Array("u.id", "c.hex")
.addTable "users", "u"
.innerJoin "colors", "c", "u.favorite=c.name"
.OrderBy "u.id", "DESC"
End With
We can include multiple tables without a join if you need to.
SELECT u.id, c.hex FROM users u, colors c WHERE u.favorite=c.name ORDER BY u.id DESC
Set MySelect = Create_SQLSelect
With MySelect
.Fields = Array("u.id", "c.hex")
.addTable "users", "u"
.addTable "colors", "c"
.AddWhere "u.favorite" "c.name"
.OrderBy "u.id", "DESC"
End With
To produce this SQL Statement:
UPDATE users SET username='old_admin' WHERE username='admin'
Set MyUpdate = Create_SQLUpdate
With MyUpdate
.Fields = Array("username")
.Values = Array("old_admin")
.Table = "users"
'Need to escape the string
.AddWhere "username", str("admin")
End With
MyDatabase.Execute MyUpdate
To produce this SQL Statement:
DELETE FROM users WHERE username='admin'
Set MyDelete = Create_SQLDelete
With MyUpdate
.Table = "users"
'Need to escape the string
.AddWhere "username", str("admin")
End With
MyDatabase.Execute MyDelete
The library includes a handful of helper functions.
If you would like to run the unit tests, import all the library files including the files in "testing" into an Excel workbook. In some cell, type "=RunUnitTests()". Any failures will open a messagebox stating the expected output and what was actually received by the library.
All classes are documented using Naturaldocs syntax.