Copy table structure only

[andrefrancis]

Can anyone help with the following ... which generates a syntax error when run in VBA:

Private Sub CopyTableStructure_Click()
Dim MSS0 As String
'set up MySQLString to create new tables with structure ONLY
MSS0 = "CREATE TABLE LU_WMCHDL_Inf_v2 AS SELECT * FROM"
MSS0 = MSS0 & " LU_WMCHDL_Inf_v1 WHERE 1=2;"
DoCmd.RunSQL MSS0
End Sub

Any ideas?

 

[RV]

Private Sub CopyTableStructure_Click()
Dim MSS0 As String
'set up MySQLString to create new tables with structure ONLY
MSS0 = "SELECT LU_WMCHDL_Inf_v1.* INTO LU_WMCHDL_Inf_v2"
MSS0 = MSS0 & " FROM LU_WMCHDL_Inf_v1 WHERE 1=2;"
DoCmd.RunSQL MSS0
End Sub

RV

 

[DB7]

Have you considered the "CopyObject" method?

DoCmd.CopyObject, "LU_WMCHDL_Inf_v2", acTable, "LU_WMCHDL_Inf_v1"

 

[RV]

Have you considered the "CopyObject" method?

DoCmd.CopyObject, "LU_WMCHDL_Inf_v2", acTable, "LU_WMCHDL_Inf_v1"

CopyObject will also copy any existing data into the new table, so won't meet the original request:

to create new tables with structure ONLY

RV

 

[DB7]

Absolutely, and bear with me for not being more diligent in finding the proper method, I thought as a work around...

DoCmd.CopyObject, "LU_WMCHDL_Inf_v2", acTable, "LU_WMCHDL_Inf_v1"
CurrentProject.Connection.Execute _
"DELETE FROM LU_WMCHDL_Inf_v2"

But again, I don't want to discourage delving further, into finding the more appropriate Action.

Now that I mention it, somewhat of a tedious way would be to,
open a teblDef of "LU_WMCHDL_Inf_v1"
Loop through its properties & create fields for new table with results.
Not sure if all properties are available, with the CReateField Action?

 

[boblarson]

This will work in Access 2003 (copy table and ONLY the structure. You may have to tweak for previous versions.

    Dim strPath As String
    strPath = CurrentProject.FullName
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, "MyTableNameHere", "MyTableNameHere", True

 

[DB7]

boblarson, thank-you very much!

I'm obviously not very diligent in searching for answers to particular database problems.
I was just about to post the following, when I read your thread.

Sub CopyTableII()
On Error GoTo xxx

Dim fld As Field, td As TableDefs, prp As Property
Dim tTable As TableDef
Dim fField As Field
Dim iIndex As Index

Set td = CurrentDb.TableDefs

Set tTable = CurrentDb.CreateTableDef("tblCountriesII")

For Each fld In td("tblCountries").Fields


Set fField = tTable.CreateField(fld.Name, fld.Type)
fField.Attributes = fld.Attributes
fField.Size = fld.Size
fField.AllowZeroLength = fld.AllowZeroLength
fField.Required = fld.Required
fField.DefaultValue = fld.DefaultValue
tTable.Fields.Append fField

If fld.Attributes = 17 Then
Set iIndex = tTable.CreateIndex("PrimaryKey")
iIndex.Primary = True
iIndex.Required = True
iIndex.Unique = True
Set fField = iIndex.CreateField(fld.Name)
iIndex.Fields.Append fField
tTable.Indexes.Append iIndex
End If


Next

CurrentDb.TableDefs.Append tTable

RefreshDatabaseWindow

Exit Sub
xxx:
If err.Number = 3219 Then 'if data type allows zerolength property
Resume Next
Else
MsgBox err.Description & vbCrLf & _
err.Number
End If

End Sub


...but yours is much more reliable and succinct.

Thanks again!

 

[boblarson]

I just knew that it was possible to do it manually, so it was just a matter of figuring out the proper code to do it automatically. Glad to help.

 

[NauticalGent]

Old thread, new concept:

"WHERE 1 = 0" - 'purt near clever...