How To Create A Table In Access Using SQL And VBA

This post will show you how to create an empty table in access using sql and vba

This is a simplified version of my other post,

“Access VBA Create Table”

That post was the more elaborate way of table creation with VBA. This one will just show how to generate an empty table.

In the Sub Procedure called “CreateThis”, we essentially are saying we want a table created called “Customers” that has 6 fields.

The sub will “hand off” the work to the function called “CreateTable”. This function will do all the work for us. We just tell it what we want in the sub procedure.

Sub CreateThis()
    'Produced by http://loeblcomservices.com

    Dim strFields As String
    Dim blnTableCreated As Boolean
    
    
    strFields = "CustomerID,CustomerName,Address,City,State,ZipCode"
    blnTableCreated = CreateTable(strFields, 6, "Customers")
    
    MsgBox blnTableCreated
    
    
End Sub

Public Function CreateTable(table_fields As String, num_fields As Integer, table_name As String) As Boolean
    Dim strCreateTable As String
 
    Dim intCount As Integer
 
    Dim strFields() As String
    Dim strValues() As String
 
    Dim strInsertSQL As String
 
    Dim intCounter As Integer
    Dim intData As Integer
 
    On Error GoTo errHandler
 
    'split the string on the comma delimiter
    strFields = Split(table_fields, ",")

 
    'this creates the table structure...
    strCreateTable = "CREATE TABLE " & table_name & "("
 
    For intCounter = 0 To num_fields - 1
        strCreateTable = strCreateTable & "[" & strFields(intCounter) & "] varchar(150),"
    Next
 
    If Right(strCreateTable, 1) = "," Then
        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1)
        strCreateTable = strCreateTable & ")"
    End If
 
    CurrentDb.Execute strCreateTable
 
    intCounter = 0 'reset
    intData = 0 'reset
 
    If Err.Number = 0 Then
        CreateTable = True
    Else
        CreateTable = False
    End If
    
 
    Exit Function
errHandler:
        CreateTable = False
        MsgBox Err.Number & " " & Err.Description
 
End Function
 

In the function called “CreateTable” we are just formulating a SQL string (CREATE TABLE) comprised of the 6 fields, and then having the Access database engine create the table.

Watch me do it:

Let me know if you need help.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!