This Blog post continues the current series dealing with building robust business applications with Excel VBA and MySQL Database.
In the Databases world, we distinguish between statements that deal with the STRUCTURE of the Database (or Schema), and statements used for manipulating and querying the CONTENT of the Schema.
Data Definition Language (DDL) include those statements that CREATE a table, ALTER a table, DROP a table, and RENAME an object in the Schema.
Data Manipulation Language (DML) include those statements that CREATE new Records in a table, READ data from tables, UPDATE existing Records in tables, and DELETE existing Records from tables. These are also known as the CRUD statements (CREATE, READ, UPDATE and DELETE).
In addition to DML and DDL statements, we also identify Data Control Language statements that deal with PERMISSIONS: GRANT and REVOKE; and Transaction Control Language statements that address the CONSISTENCY of the data: COMMITT and ROLLBACK.
Last week, we used the Recordset object of the ADODB service to handle a table structure (or record-sets) coming back from the Database, typically as a response to a SELECT statement.
Not all SQL statements return a table-like structure, or anything at all. DDL statements are a good example.
Today, we will send an SQL statement for execution without expecting anything in return.
Since nothing is coming back, we rely on error handling in our VBA code to know if the execution completed successfully.
The following function is a generic function I wrote for adding a column to any table in the Database.
The AddColumnToTable function expects a Schema name, a Table name, the new Column name, and its Data Type.
Optionally, the function accommodates for a “Not null” flag and a “silent” flag. The “silent” flags the function to avoid popping an error message to the user. The calling function will determine successful execution by the returning value only: True or False.
I am using the ConnectDB and DisconnectDB functions and the ConnDB global variable I presented last week.
Function AddColumnToTable(ByVal strSchema As String, ByVal strTable As String, ByVal strColumnName As String, ByVal strColumnType As String, Optional ByVal bolNotNull As Boolean = False, Optional ByVal bolSilent As Boolean = False) As Boolean
'Adds a column to a table in the DB
Dim strSQL As String
strSQL = "ALTER TABLE " & LCase(strSchema) & ".`" & LCase(strTable) & "`"
strSQL = strSQL & " ADD COLUMN " & LCase(strColumnName)
strSQL = strSQL & " " & strColumnType
If (bolNotNull) Then
strSQL = strSQL & " NOT NULL"
strSQL = strSQL & ";"
On Error GoTo FailedCreateDB
AddColumnToTable = True
AddColumnToTable = False
If Not bolSilent Then MsgBox "Failed adding a column to the DB table " & strTable & ". Please verify Database settings are correct.", vbOKOnly + vbCritical, "Database Error"
You should now have a fairly good foundation for writing additional generic VBA functions to support the full set of statements required when developing a business application with Excel VBA and MySQL Server.
Don’t keep all this “goodness” to yourself, share it wide and far – THANK YOU!
Join today to the Excel VBA Inner Circle with Mor Sagmon.Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.