Send any SQL statement for execution by a remote Database Server from your VBA program * DDL statements allow you to create and alter the schema from your program * Today we write a generic VBA function for adding a column to a table in the Database
This Blog post continues the current series dealing with building robust business applications with Excel VBA and MySQL Database.
If you have not read the previous posts about preparing MySQL Server and Excel for communications and connecting and sending SQL Select statements, read them first.
What are DDL and DML statements?
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.
Sending an SQL statement for execution
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.
ALTER TABLE statement for adding a column to a Table in the Database
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.
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!
WANT TO LEARN PROGRAMMING WITH EXCEL VBA?
I created a complete 8-course program for you.
From programming to databases with Excel VBA to deliver business-grade solutions.
Take the first course today: Computer Programming with Excel VBA.
Click the button to see the complete program and start now!