Creating a table from within a Module in MS access

All,
I have been searching for a simple example, or any example for that matter, of how one would create a table from a Module and not from a Make Table Query. If anyone knows of such a beast I would greatly appreciate the URL. Thanks in advance. - CES

Douglas
Fri Dec 15 16:04:54 CST 2006

You could also use DAO or ADOX.

For examples of using DAO, check the CreateTableDef, CreateField,
CreateIndex and CreateRelation methods in the Help file.

For ADOX, you need to declare objects as Table, Column, Index or Key, set
their properties appropriately, and append them to the appropriate
collection.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Klatuu" <Klatuu@discussions.microsoft.com> wrote in message
news:551847A3-0911-45D0-B30A-A25FA60ECD74@microsoft.com...
> You need to use SQL. If you look in VBA Help, look at the Table Of
> Contents
> below the search text box and Select Microsoft Jet SQL Reference, then
> select
> Data Definition Language, you will find everything you need.
>
> "CES" wrote:
>
>> All,
>> I have been searching for a simple example, or any example for that
>> matter, of how one would create a table from a Module and not from a Make
>> Table Query. If anyone knows of such a beast I would greatly appreciate
>> the URL. Thanks in advance. - CES
>>

CES
Fri Dec 15 16:57:47 CST 2006

Douglas J. Steele wrote:
> You could also use DAO or ADOX.
>
> For examples of using DAO, check the CreateTableDef, CreateField,
> CreateIndex and CreateRelation methods in the Help file.
>
> For ADOX, you need to declare objects as Table, Column, Index or Key, set
> their properties appropriately, and append them to the appropriate
> collection.
>

Thank you... I'm in the process of working through a couple of examples that I was able to paste together based on the CREATE TABLE statement. -- CES

Dim tmp As String
tmp = "TempTable"

Dim cmd As ADODB.Command
Dim strSQL As String


Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText


'create New Table
strSQL = "CREATE TABLE " & tmp & " (Field1 TEXT, Field2 TEXT, Field3 TEXT);"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow


Set cmd = Nothing

CES
Fri Dec 15 16:58:37 CST 2006

Douglas J. Steele wrote:
> You could also use DAO or ADOX.
>
> For examples of using DAO, check the CreateTableDef, CreateField,
> CreateIndex and CreateRelation methods in the Help file.
>
> For ADOX, you need to declare objects as Table, Column, Index or Key, set
> their properties appropriately, and append them to the appropriate
> collection.
>

Thank you... I'm in the process of working through a couple of examples that I was able to paste together based on the CREATE TABLE statement. -- CES

Dim tmp As String
tmp = "TempTable"

Dim cmd As ADODB.Command
Dim strSQL As String


Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText


'create New Table
strSQL = "CREATE TABLE " & tmp & " (Field1 TEXT, Field2 TEXT, Field3 TEXT);"
cmd.CommandText = strSQL
cmd.Execute

' refresh database window
Application.RefreshDatabaseWindow


Set cmd = Nothing