By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,035 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

How to copy existing table structure to create empty temp table?

P: n/a
DoCmd.CopyObject copies data, but I only need structure.

I'm trying to clone several tables in my Access 2003 mdb. The goal is to
link to a series of Excel spreadsheets and then run various action queries
against the data in the linked tables (to massage and import it). Since I
can't run action queries against linked Excel tables, I need to create temp
tables that are clones of the required existing tables in the database then
append everything from the linked tables into the temp tables.

For example:

Dim tdf as TableDef
For Each varTbl In Array("tblThis", "tblThat", etc.)
strTblxls = varTbl & "_xls")
Set tdf = db.CreateTableDef(strTblxls)
strLink = "Excel 8.0;DATABASE=" & strTblxls
tdf.Connect = strLink
tdf.SourceTableName = "strTblxls$"
db.TableDefs.Append tdf
DoCmd.CopyObject, varTbl & "_tmp", acTable, varTbl
db.Execute "qryAppendXlsIntoTmp"
[run business logic here to massage and import data]
Next

I have some control over the naming and format of the spreadsheets (which
are an intermediary between a proprietary DB and Access), so that's why they
mirror the existing tables. The problem is DoCmd.CopyObject copies all the
data of the existing table, not just the structure. Is there a way to copy
just the structure of an existing table to and empty table with a "_tmp"
suffix? I could write a bunch of DDL queries, but I'm hoping for an easier
way.

Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I think I may have found it...

DoCmd.TransferDatabase

still testing...
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.