Copy a mdb database table in VB6 2

[MrMajik]

I am trying to copy a mdb table (or recordset) in VB6. I want to create a new table and keep the original table structure without the records.

When I try this:

DoCmd.CopyObject, "MyNewTable", acTable, "MyTable"

it generates an error.

Also, I tried adding the App.Path & "\etc..." so the program knows which database to open but it does not work.

Any ideas?

Thank you,

MrMajik

 

[JerryKlmns]

This DoCmd.CopyObject, works inside MS-Access (that 's VBA) or using an Access Application Object (that 's VB6.0) instanciated appropriately.

So instead, run a make table query that creates the new table from the old one like this

SELECT MyTable.* INTO MyNewTable FROM MyTable;

and then run a delete query for all records of the new table

DELETE FROM MyNewTable;

Depending on the amount of data of the original table, this approach is a 2-lines code, but could take time.
To reduce the amount of data inserted in the new table use a WHERE clause that would return few records.

The professional approach is lengthy for VB6.0

In VBA try this

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.FullName, acTable, "MyTable", "MyNewTable", True

Which copies only the strusture while the DoCmd copies strusture and data. It works, I 've tested it.

 

[TysonLPrice]

Also:

select top 0 * into NewTable from OldTable

and avoid the delete step.

 

[PatrickIRL]

You could also try ...

SELECT * INTO NewTable FROM OldTable WHERE 1 = 2

This will give you a copy of the old table without the records.

Patrick

 

[MrMajik]

Thanks for all the great ideas!

Here is what I have working with one question.

When I try to close the pointer (rstChartData.Close) an error message says it is already closed so I removed the statement and it creates a table. Is this the way this is supposed to be set up? The table is created and there are no errors:

Private Sub Command1_Click()
Dim cnn As New ADODB.Connection
Dim rstChartData As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\urlgen.mdb"
rstChartData.Open "SELECT * INTO tblMyNewTable FROM tblMyTable WHERE 1 = 2", cnn, adOpenKeyset, adLockOptimistic
cnn.Close
End Sub


Thank you,

MrMajik

 

[Sheco]

You dont even need the recordset object.

 Dim cnn As New ADODB.Connection
 
 cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\urlgen.mdb"
 cnn.Execute "SELECT * INTO tblMyNewTable FROM tblMyTable WHERE 1 = 2"
 cnn.Close
 Set cnn = Nothing