VB & VBA - Copy Table Structure Only

Greetings all, my first post on this forum

My question relates to "code wise" copying of the structure of a table.

Actually, more completely, what I need to do is to refresh a table with data from 2 fixed length text files.

My first 3 concepts, I don't like:
1. Delete the table and programmatically recreate it with tabledef, createindex, createfield.... etc. This is fast, but A lot of code for what should be a simple process.

2. Select ...into table... with the select being a criteria that would select nothing.
3. accessing the table. selecting all the rows and deleting them.

These second two options are simple, but SLOW!!!

If I was to do this manually, I would ...
1. rename the table
2. Copy the table
3. Paste special/Structure only.
This is fast and simple...but manual...I need to do it programmactically.

and then use the code to populate the table from the txt files.


Does anyone know the "Simple" and "fast" way of completing this task?


Thanks
Michael

If you're doing this in Access VBA you could use DoCmd.CopyObject to make a copy of your table:

Code:

---

DoCmd.CopyObject , "NewTableName", acTable, "SourceTableName"

---

Then you could use a quick DELETE SQL statement via the Execute method of the CurrentDb object to delete all the records in the new database:

Code:

---

CurrentDb.Execute "DELETE FROM NewTableName"

---

This would give you an empty copy of a table.

I sort of suspect there's an easier way since you can copy a table structure only within a database, but this works.

Paul

Adding another 'empty' reply - because I'm having lots of wierd problems with this forum today (like timeouts and the reply count to this thread showhing zero even though I've replied to it).

Paul

This code is part of the process of copying a complete database, including tables, indexes, QueryDefs and Relations, but can be adapted to copy a single table. Full code on my site

VB Code:

Dim dbSrc As Database
    Dim dbNew As Database
    Dim tdefSrc As TableDef
    Dim tdefNew As TableDef
    Dim fdSrc As Field
    Dim fdNew As Field
    Dim ixSrc As Index
    Dim ixNew As Index
 
Set dbSrc = OpenDatabase("SourceDatabase")
Set dbNew = OpenDatabase("NewDatabase")
 
For Each tdefSrc In dbSrc.TableDefs
        'check if system table
        If InStr(1, tdefSrc.Name, "MSys", vbTextCompare) = 0 Then
            'not a system table
            Set tdefNew = dbNew.CreateTableDef(tdefSrc.Name)
            tdefNew.ValidationRule = tdefSrc.ValidationRule
            tdefNew.ValidationText = tdefSrc.ValidationText
            For Each fdSrc In tdefSrc.Fields
                'check for replication fields s_GUID, s_Generation, s_Lineage, Gen_XXX
                If InStr(1, fdSrc.Name, "s_", vbTextCompare) = 0 And InStr(1, fdSrc.Name, "Gen_", vbTextCompare) = 0 Then
                    Set fdNew = tdefNew.CreateField(fdSrc.Name, fdSrc.Type, fdSrc.Size)
                    On Error Resume Next
                    fdNew.Attributes = fdSrc.Attributes
                    fdNew.AllowZeroLength = fdSrc.AllowZeroLength
                    fdNew.DefaultValue = fdSrc.DefaultValue
                    fdNew.Required = fdSrc.Required
                    fdNew.Size = fdSrc.Size
                    tdefNew.Fields.Append fdNew
                    On Error GoTo 0
                End If
            Next
            'now copy indexes
            For Each ixSrc In tdefSrc.Indexes
                'Check for replication indices s_GUID, s_Generation
                If InStr(1, ixSrc.Name, "s_", vbTextCompare) = 0 Then
                    'Don't copy indices set as part of Relation Objects
                    If Not ixSrc.Foreign Then
                        Set ixNew = tdefNew.CreateIndex(ixSrc.Name)
                        ixNew.Clustered = ixSrc.Clustered
                        ixNew.IgnoreNulls = ixSrc.IgnoreNulls
                        ixNew.Primary = ixSrc.Primary
                        ixNew.Required = ixSrc.Required
                        ixNew.Unique = ixSrc.Unique
                        'Add Index field(s)
                        For Each fdSrc In ixSrc.Fields
                            Set fdNew = ixNew.CreateField(fdSrc.Name)
                            fdNew.Attributes = fdSrc.Attributes
                            ixNew.Fields.Append fdNew
                        Next
                        tdefNew.Indexes.Append ixNew
                    End If
                End If
            Next
            dbNew.TableDefs.Append tdefNew
        End If
    Next

Public Sub Copy_Table(ByVal SrcTable As String, _
ByVal DstTable As String, _
Optional ByVal SrcDatabase As String = "", _
Optional ByVal StructureOnly As Boolean = False)
' To copy from another Access DB specify the full path\name in SrcDatabase

SrcDatabase = Trim(UCase(SrcDatabase))

' If the source DB wasn't specified use this one
If SrcDatabase = "" Or SrcDatabase = "CURRENTDB" Then _
SrcDatabase = CurrentProject.FullName

DoCmd.TransferDatabase acImport, "Microsoft Access", _
SrcDatabase, acTable, SrcTable, DstTable, StructureOnly

End Sub ' Copy_Table

I have a DB on SQL server 2008 R2. On my laptop I only have Access 2007. I would like to create an Access Back end so that I can develop on holiday
If you use the SQL server Copy Wizard you lose the Primary Key and Identity
( there is supposed to be Enable Identity option but it does not appear for me)
If in Access you Import the tables you get the IDENTITY ( Autonumber) but lose the primary key
If in Access you copy a linked table ( Structure and Data) you get the Primary key and the Identity. But you have to do that manually - I have over 100 tables

The TransferDatabase copies a linked table to a linked table
So does CopyObject.

Is there an easy solution?