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.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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 " &amp; table_name &amp; "("

 

    For intCounter = 0 To num_fields - 1

        strCreateTable = strCreateTable &amp; "[" &amp; strFields(intCounter) &amp; "] varchar(150),"

    Next

 

    If Right(strCreateTable, 1) = "," Then

        strCreateTable = Left(strCreateTable, Len(strCreateTable) - 1)

        strCreateTable = strCreateTable &amp; ")"

    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 &amp; " " &amp; 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: