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 " & 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.






