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,

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

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),"

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: