Copy database design to new database

[christinamasalha]

Hello everyone,

Im quite new to MS Access (i am using MS Access 2003 on Windows XP ) and i have a little question ( i hope).

I have created a small database application and it is up and running nicely.I would like to create a copy of this database but i need all the tables to be empty. My question is: Is there a way to copy only the design elements of a database (without the data in the tables) into a new one?

Any help would be appreciated.
Thanks,
Christina

[NeoPa]

I think the safest way, ensuring you have everything exactly the same except for the data in the tables, would be to :

1. Make a file copy of your database.
2. Open the database in Access.
3. Create a quick routine in a general purpose module to process through all tables (CurrentDB.TableDefs) and empty them (Use SQL in the form DELETE FROM [TableName]).
4. Compact & Repair the database.

Let me know if you need help creating the routine. I've done similar things a couple of times before so it shouldn't be too hard if you need it.

[Killer42]

1. Make a file copy of your database.
2. Open the database in Access.
3. Create a quick routine in a general purpose module to process through all tables (CurrentDB.TableDefs) and empty them (Use SQL in the form DELETE FROM [TableName]).
4. Compact & Repair the database.

Why just create new db and use Import function? Worried about missing something?

[NeoPa]

If you import the objects across you then still need to empty the tables.
But yes, a number of items or properties are not stored in the main containers.
You could go through with a fine tooth-comb but it's easier to explain and execute the other method I thought.

[MMcCarthy]

If you import the objects across you then still need to empty the tables.
But yes, a number of items or properties are not stored in the main containers.
You could go through with a fine tooth-comb but it's easier to explain and execute the other method I thought.

You can actually copy and paste the tables accross. You will be given the option of structure only or structure and data. The only issue is you will need to re-enter the table name each time.

Mary

[Killer42]

If you import the objects across you then still need to empty the tables.
But yes, a number of items or properties are not stored in the main containers.
You could go through with a fine tooth-comb but it's easier to explain and execute the other method I thought.

Fair enough, I suppose the copy-and-wipe leaves much less scope to miss things.

However, I do take exception to the "still need to empty the tables" statement. When importing tables, you have the option to import Definition and Data or Definition only.

[Killer42]

You can actually copy and paste the tables across. You will be given the option of structure only or structure and data. The only issue is you will need to re-enter the table name each time.

Wouldn't you also lose any relationships? Not to mention import/export specs, and who knows what else?

[MMcCarthy]

Fair enough, I suppose the copy-and-wipe leaves much less scope to miss things.

However, I do take exception to the "still need to empty the tables" statement. When importing tables, you have the option to import Definition and Data or Definition only.

You are of course right Killer and this also allows you to set the options so that relationships, specs, etc are imported.

This is by far the best solution.

Mary

[NeoPa]

Fair enough, I suppose the copy-and-wipe leaves much less scope to miss things.

However, I do take exception to the "still need to empty the tables" statement. When importing tables, you have the option to import Definition and Data or Definition only.

My bad - simple ignorance.
I wasn't aware at that level of detail :(

[MMcCarthy]

My bad - simple ignorance.
I wasn't aware at that level of detail :(

At least you have an excuse. I just didn't put my brain in gear :D

Good catch Killer.

[NeoPa]

You are of course right Killer and this also allows you to set the options so that relationships, specs, etc are imported.

This is by far the best solution.

Mary

I would actually still recommend my original approach.
There are still numerous properties that will not be transferred using this (import the various individual objects) approach. Many of them, undoubtedly quite insignificant. I prefer to know exactly what I'm doing though so would have to check them all out first before being sure, thus losing any benefit of the method.
These properties (like Startup settings; module names; password & Security settings; various other database properties) can be reset afterwards but that still creates extra, unnecessary work.

[Killer42]

You are of course right Killer and this also allows you to set the options so that relationships, specs, etc are imported.

This is by far the best solution.

Thanks.

But in fact unless I'm moving a single table or query, I usually tend to use NeoPa's method of copying the MDB then zapping the data. I tend to have queries sitting around with names like "Q Zap everything!". :)

I was just sort of playing devil's advocate, to get the options thrashed out here.

[MMcCarthy]

I would actually still recommend my original approach.
There are still numerous properties that will not be transferred using this (import the various individual objects) approach. Many of them, undoubtedly quite insignificant. I prefer to know exactly what I'm doing though so would have to check them all out first before being sure, thus losing any benefit of the method.
These properties (like Startup settings; module names; password & Security settings; various other database properties) can be reset afterwards but that still creates extra, unnecessary work.

True for startup settings and password and security settings but shouldn't be a problem with module names.

[Killer42]

I would actually still recommend my original approach.
There are still numerous properties that will not be transferred using this (import the various individual objects) approach. Many of them, undoubtedly quite insignificant. I prefer to know exactly what I'm doing though so would have to check them all out first before being sure, thus losing any benefit of the method.
These properties (like Startup settings; module names; password & Security settings; various other database properties) can be reset afterwards but that still creates extra, unnecessary work.

Oh! Security settings - forgot about those. :o

I never bother with them. In fact I think the only database I ever created with any security on it (beyond setting read-only attribute on the MDB:)) was in Access 2, back around early 90's.

[NeoPa]

Oh! Security settings - forgot about those. :o

That perfectly illustrates why I prefer to avoid that sort of approach. Not because of the problems I know to expect, but for those I don't foresee and only find out about later :o
I have had to use it though, when my db got corrupted and before I found a way around it.

In fact I think the only database I ever created with any security on it (beyond setting read-only attribute on the MDB:)) was in Access 2, back around early 90's.

You've been using Access longer than I have then. I thought I was the vet ;)

[Killer42]

That perfectly illustrates why I prefer to avoid that sort of approach. Not because of the problems I know to expect, but for those I don't foresee and only find out about later :o

Amen!

...You've been using Access longer than I have then. I thought I was the vet ;)

I've been using it for a loooong time, but not professionally. I mostly just dabble a bit, which is why I don't know as much about it as I should.

I've been a dabbler in VB since version 1, and bought Access when it was originally released, at a hugely reduced promotional price.

[christinamasalha]

Hello All,

Thanks alot for your responses.
i am going to use mary's approach of copying the tables from one db to another since my application is pretty simple and therefore small :)


Thank you all again for helping me out of jam, i really appreciate it.

Christina.

[NeoPa]

Certainly, if your database is relatively small and it consists fundamentally of the main Databse objects (Tables; Querydefs; Forms; Reports; ASPs; Modules; etc) then that will work perfectly for you.