SQL CREATE TABLE from existing table

[Guest]

I need to write an SQL statement that creates a table, but it's a fairly
complex table which I have created in the structure view. Is it possible to
have the sql code created by access in some way?

bye
snaggy

 

[Douglas J. Steele]

There's nothing built into Access to allow you to create DDL, but there are
some 3rd party products that can do it.

Unfortunately, I don't have a list of names available at the moment. I think
DataPro, from SSW, is one of them
http://www.ssw.com.au/ssw/DataPRO/

 

[Ed Adamthwaite]

Hello Snaggy
this is a bit quick and dirty, but I find the following works well for me:

Sub CopyTable(TableToCopy As String, TableToCreate As String)
Dim sSQL As String
sSQL = "SELECT TOP 1 " & TableToCopy & ".* " _
& "INTO " & TableToCreate & " " _
& "FROM " & TableToCopy & ";"
CurrentDb.Execute sSQL
'Delete the single record
sSQL = "DELETE * FROM " & TableToCreate & ";"
CurrentDb.Execute sSQL
End Sub


It inserts 1 record into a copy of the source table and then deletes the
record from it.

HTH, Ed.