Making another copy of a table in the same database

[AmPrat]

Hey guyz,
i need to make a copy of a existing table in a Access database and save it with another name in the same database.

Example :
Database : Test1
Tables : A,B,C,D

After Making another copy of table C and Naming it as Z the database should be
DataBase: Test1
Tables : A,B,C,D,Z

See if you Guyz can Help...
Regards

Amprat

[veryjonny]

use :

select * into Newtable from Oldtable

[cafeenman]

This is pretty simple. I'm not sure if there's a way to directly copy a table, but you can use a make-table query to put all the records from the existing table into a new table with one line of code.

[Ecniv]

The Sql statement is the quickest BUT it may not assign the right values on fields (sometimes it assumes things for you). For example .. double in one table defaults to long in another... which can cause problems later.


If you have time on your hands, try coding it, but you'll need either DAO (Access) or ADOX to read table designs/properties and create new tables/fields... Then read up on index creating too...



BTW - Anyone know DAO or ADOX coding for indexes ? I tried in DAO but it wouldn't let me complete a coding version of inserting an index.

Vince

[cafeenman]

I'm not sure about this because I've never done it, but I did a fair amount of reading of the DAO methods in the VB manuals and I think you have to create an index when you create the field. Some things can't be done after the fact.

Sort of like this:

dim Fld as Field

' create field properties

tbl.append fld

after that it's too late to make some types of changes.

Sorry I can't tell you more.

[PilgrimPete]

To add an index with ADOX:

Private Sub CreateTable(ByVal strDB As String)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim idx As ADOX.Index
    
    'get the catalog
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\" & strDB
        
    'create a column
    Set col = New ADOX.Column
    With col
        .Name = "PK"
        .Type = adInteger
    End With
    
    'create the table
    Set tbl = New ADOX.Table
    With tbl
        .Name = "NewTable"
        .Columns.Append col
    End With
    'append the table
    cat.Tables.Append tbl
    
    'create an index
    Set idx = New ADOX.Index
    With idx
        .Name = "idx_PK"
        .Columns.Append col.Name
        '.Clustered = True  'Access doesn't support clustered indexes
    End With
    'append the index to the table
    tbl.Indexes.Append idx
 
End Sub

[PilgrimPete]

Vince: Here's the same thing in DAO. Looks very similar doesn't it..?

Sub CreateTable()
    Dim tdf As TableDef
    Dim fld As Field
    Dim idx As Index
    
    'create the tabledef
    Set tdf = CurrentDb.CreateTableDef("tblNew")
    
    'create a field...
    Set fld = New Field
    With fld
        .Name = "PK"
        .Type = dbInteger
    End With
    '...and add it to the tabledef
    tdf.Fields.Append fld
    
    'create an index...
    Set idx = New Index
    With idx
        .Name = "idx_PK"
        .Fields.Append .CreateField("PK")
        .Primary = True
    End With
    '...and add it to the tabledef
    tdf.Indexes.Append idx
    
    'finally append the tabledef to the database
    CurrentDb.TableDefs.Append tdf
    
End Sub