copy table from closed database

[Stuart]

I have a table named "Estimate Template" in a closed database
named "Newdb.mdb", which is located in
"C:\Temp\Automate Office".

I create a new database with the following code, which is running
from an Excel VBA module:

Dim appAccess As Access.Application 'dimmed at module level

Sub NewAccessDatabase()
Dim dbs As Object, tdf As Object, fld As Variant
Dim strDB As String
Const DB_Text As Long = 10
Const FldLen As Integer = 40

' Initialize string to database path.
strDB = "C:\Temp\Automate Office\Newdb2.mdb"

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.9")

' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB

' Get Database object variable.
Set dbs = appAccess.CurrentDb

' Do Things

' Append Field and TableDef objects.
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
' Tidy up
Set appAccess = Nothing
End Sub

Q1: How can I copy the table "Estimate Template" from
Newdb.mdb to Newdb2.mdb and rename it (say)
"New Estimate Template".
(the table is pre-formatted with FieldNames, DataType
and FieldWidth etc, ready for spreadsheet data import).
I wish to keep this table as a Template for further use.

Q2: Do not understand the statement:
' Append Field and TableDef objects

Help would be much appreciated, please.

Regards.

 

[Marin Kostov]

I think this is little strange - the way you are referencing the objects.
My opinion: Use the object model, as written "by the book". What do I mean:
First, open the VBA Editor, and click Tools - References - Microsoft Data
Access Objects 3.60 Object Library. Now you will have all objects defined at
design time, and you will be able to use them and to code efficient.
Next, open the Online help of Access, and then Microsoft Data Access Objects
3.6 (DAO). Check CreateDatabase and CreateTableDef Methods. You can read
OpenDatabase too. There is sample code.
If you do not understand the concepts, you need to read some books about VB
and VBA. There are tutorials in the VBA help systems, in books, MSDN,
Internet...

 

[Stuart]

Many thanks. Will follow your advice.

BTW, the code was pinched from Auto2000.chm
(a file extracted from Auto2000.exe) which is
MS Technical Support's guide to Automation in
Office2000.

Regards and thanks.

 

[dandgard]

I suggest you look at DoCmd.TransferDatabase. This should get you wha
you want.

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"Path and Name of DB", acTable, "Name of Import table", _
"Name of table to put in"

Note that this will create an identical table to the one that is in th
first db and so you should not have a table created in the new db

 

[dandgard]

I suggest you look at DoCmd.TransferDatabase. This should get you wha
you want.

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"Path and Name of DB", acTable, "Name of Import table", _
"Name of table to put in"

Note that this will create an identical table to the one that is in th
first db and so you should not have a table created in the new db