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

Copying database structure without the data

P: 1
I need to make a new database at the beginning of every year but have forgotten how to do this quickly and efficiently.

I have a database with about 10 tables, 10 forms related to the 10 tables and likewise 10 reports. The same objects in my 2006 database, 2005, 2004 etc.
I need to make a new database for 2007 using the identical structure.

There is some quick way of copying the 2006 database so that all the tables, forms , reports i.e. all the objects come across but empty, without the data.
Before I learnt how to do this, I used to create a new blank database and copy each table individually by copy and pasting structure only. This is slow and timeconsuming. There is a better way.
Help will be most gratefully accepted.
Dec 29 '06 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,072
You can use for tables the TransferDatabase command that has an option "structure only":

DoCmd.TransferDatabase acExport, "Microsoft Access", strDatabase, acTable, "tblTest", "tblTest", True

For forms/queries/etc you can use the CopyObject like:
DoCmd.CopyObject strDatabase, , acForm, "frmMain"

Dec 29 '06 #2

Expert Mod 15k+
P: 31,494
I've had to do this a couple of times now and what I do (mainly because remembering all the details is so unreliable) is I copy the whole damn thing then I write some very short code (I'll include it below) to clear out all the data from the tables. When that is all done I run a 'Compact and Repair' leaving me with all the items I need.
Expand|Select|Wrap|Line Numbers
  1. 'ClearNativeTables clears tables that are native to this database
  2. Public Sub ClearNativeTables()
  3.     Dim tblThis As TableDef
  5.     Call DoCmd.SetWarnings(False)
  6.     For Each tblThis In CurrentDb.TableDefs
  7.         With tblThis
  8.             If .Connect = "" And .Attributes = 0 Then _
  9.                 Call DoCmd.RunSQL("DELETE FROM [" & .Name & "]")
  10.         End With
  11.     Next tblThis
  12.     Call DoCmd.SetWarnings(True)
  13. End Sub
Dec 31 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.