Link to home
Start Free TrialLog in
Avatar of Brian Pierce
Brian PierceFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Copy Access Database Structure with VBA - Access 2010

I have a database with a back-end that contains the data tables. In order to begin each new period I need to create a new backend file that has the same tables and structure as the original backend.

 I'm ok with de-linking the old backend and re-linking to the new, but I need an efficient way of creating a new backend.

How can use VBA to create a copy of the existing backend (with a new name), keeping the same structure and all other properties/settings but with empty tables.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

<In order to begin each new period I need to create a new backend file that has the same tables and structure as the original backend.>
Why?

This will be a nightmare to manage if you are ever called upon to provide info on data that spans multiple Periods...

If you have Orders, you do not create new backends for each year.
You simply keep the order in one table and include a date/time stamp on all the records
(create a "DateTimeStamp" field in your table and set the default value to: Now()  )
...The you can create a query to filter the Year (or period).

JeffCoachman
<no points please>

<In order to begin each new period I need to create a new backend file that has the same tables and structure as the original backend.>
Why?
+1 for this. Outside of archiving purposes, I have yet to see a situation where this makes sense. If you have any need of that data - and you will, for reporting and comparisons - then you should keep the data in a single table, and query it out by dates.
LSM

You can have some points buddy.
;-)

I never considered "archiving" specifically

That is whey I asked why this was needed, so we could understand the question better and propose a fitting solution, ...or provide alternatives

;-)

Jeff
I have written a file for to create a copy of my database, i'm using it for sending my database to new customers, so it should  create for then the back-end automatically, and if i have to update the back-end it also updating all new fields and tables.

Is this what you need ?
Avatar of Brian Pierce

ASKER

I need to creat a new incident file for each period and have each period stored in its own file - the file just gets huge and unmanagemable otherwise.

I need to be able to  create a new file each period.

If they need to view incidents for a previosu period then they can simply re-link to that file

I've got all the linking and de-linking working great - but I'm having to manually copy the db structure to an new file currently.

I need to ceate some VBA code that wll create a new (backend) file with the same structure as the currentone.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just what I need