How to create a new table based on an existing table 4

[mlesniak001]

Hi Everyone,

First I want to say as someone new to VBA, that this site / Forum has been very, very helpful to me. THANKS FOR ALL THE POSTINGS!!!

also, a quick question: How can I create a new table that is based on the defintion of an already existing table?

for example, my code process goes as such:
- import an Excel file into a new table "raw_import"
- create a second table, "clean_import" with the same definition (column names, dataType, etc.) as raw_import
- sort thru the entries in "raw_import", copying the good ones into "clean_import".

Is there a quick and easy way to grab the table definition of "raw_import" and use that to create "clean_import"?

Finally, since the data volumes are high, I do *not* want to copy the original table to one with a new name...

THANKS AGAIN!

 

[Craig0201]

Sub Foo()

Dim tdf As TableDef
Dim tdf2 As TableDef
Dim fld As Field

Set tdf = CurrentDb.TableDefs("raw_import")
Set tdf2 = CurrentDb.CreateTableDef("clean_import")

For Each fld in tdf.Fields
tdf2.Fields.Append fld
Next

End Sub

Should do the job with a little adaption.

 

[JerryKlmns]

This copies just the table structure

DoCmd.RunSql "Select raw_import.* Into clean_import From raw_import Where 1=2;"

[red]I've learned that in Tek-Tips.com[/red]

 

[jimlee]

check out this link

http://www.applecore99.com/tbl/tbl018.asp

jimlad

 

[mlesniak001]

Thanks everyone!

This code seems to work the best:

Sub sCopyTableStructure(strOldTableName As String, strNewTableName As String)
    If vbYes = MsgBox("Sure?", vbYesNo + vbDefaultButton2 + vbExclamation, " ") Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTableName, strNewTableName, True
    End If
End Sub

also FYI, Craig0201, I got an error "Object invalid or no longer set" on the line:
For Each fld in tdf.Fields

still, thanks for the help!!!