SQL Statements to Alter Table from Excel VBA to MySQL Database

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.

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"

    End If

    strSQL = strSQL & ";"

    On Error GoTo FailedCreateDB

    ConnectDB

    ConnDB.Execute (strSQL)

    AddColumnToTable = True

CloseSub:

    DisconnectDB

    Exit Function

FailedCreateDB:

    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"

    GoTo CloseSub

End Function

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!