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