Copy structure of table to create empty table 3

[Dan01]

Hi, is there a way to copy a table's structure to a new table without bringing the data along? Thanks, Dan.

 

[Saanich]

Copy a table's structure using a make-table query

SELECT Table1.* INTO table2
FROM Table1
WHERE (((False)<>False));

 

[Dan01]

Saanich, that solution is pretty awesome.

Would you also know how to add an autonumber field to the empty table? Thanks, Dan.

 

[kakworks247]

You can also just copy the table (click on it and Ctrl+C). Then past the table (Ctrl+V) and it will ask if you want structure or structure and data. You can just insert the autonumber field from the table design screen.
HTH
Kimberly

 

[Dan01]

Thanks Kimberly. Trying to automate this, however. Dan.

 

[kakworks247]

Just ignore mine then. Didn't realize that.
Good luck
Kimberly

 

[JonFer]

Use the data definition sql to add fields to a table. You can find examples of it in help.

 

[Dan01]

JonFer, I believe there is an ALTER TABLE sql and a DAO Create Fields process, but in both cases I don't know how to add autonumber fields. Thanks, Dan.

 

[JonFer]

Here are the two ways.

1. Data def query using field type of &quot;AutoIncrement&quot; (not documented well - found by experimentation)

ALTER TABLE Wells ADD AutoNum AutoIncrement NOT NULL ;

2. VB

Sub AlterTable()

Dim db1 As Database
Dim td1 As TableDef
Dim fd1 As Field

Set db1 = CurrentDb
Set td1 = db1.TableDefs(&quot;TableName&quot

' create a new field of type Long and set
' the autonumber attribute. The default is
' to increment the value

Set fd1 = td1.CreateField(&quot;AutoNum&quot;, 4)
fd1.Attributes = dbAutoIncrField
td1.Fields.Append fd1

End Sub

 

[Dan01]

Thanks Jonfer. Great findings! Dan.