Creating an archive database 3

[KornGeek]

I have an Access 2000 database that accumulates a large amount of data over time. I would like to put in an archive feature that exports records over a year old (or some other user-selected date) into an archive database. I then plan to have a modified front-end to run queries and print reports on the archives.

The problem I'm running into is that I'm trying to use the DoCmd.TransferDatabase method to export the necessary tables. However, for this to work, the new database must already exist.

I'm sure this is easy enough to do, but I can't seem to find the answer of how to do it. I've seen posts where people say they already figured that part out, but unfortunately they didn't explain how for little old me.

How can I programmatically create a new Access database file so that I can export my tables into it?

Thank you.

 

[psemianonymous]

I think you use the "DBEngine.CreateDatabase" command. But I can't verify that from here right now, so it's a guess.

 

[FancyPrairie]

This doesn't reference your question (althought I believe CreateDatabase Method is correct). However, I'm not sure how you plan to show the archived data or report on it. But, here's what works great for me. Each form and report I have contains a menubar. On each of the menu bars I have an item that states "View Archived Data". When the user selects it, I simply relink to the archived database and display "Achived Data (ReadOnly)" on my forms and reports so the user knows they are viewing archived data rather than live. I then change the menu item to read "View Live Data". I don't need to modify any forms or reports to handle the archived data.

Note that you can use a command button to do the same thing rather than a menu item.

To see the code on how this is done, check out this thread thread181-482248

 

[KornGeek]

foolio12,
Thanks for the reply. I try that and let you know how it worked.

FancyPrairie,
Thanks for the suggestion. The customer actually requested a variant front-end to be used for viewing archived data. I'm going to try their approach on this project (after all, they're paying, so they have the final say), but I might use your approach on some future projects.

 

[KornGeek]

The createdatabase method did work. I had some problems with the syntax because I couldn't find the help file on that method.

Here's what I did:

DBEngine.CreateDatabase strArchiveName, dbLangGeneral

where strArchiveName is the full path and file name of the database I am creating, and dbLangGeneral is a system constant.

Thanks for the help and have a star.

 

[jelee2]

Hi, this sounds like something I have to do also. But it seems you would only need to do these two steps once, and then utilize an append query (to transfer "new" archive records to the archive database) and then a delete query (to delete the newly archived records),correct? I'm not much of a programmer, so it's been painstaking trying to figure out how to do everything .

 

[KornGeek]

jelee2,
The way I implemented this, the user can create several archive files. This way, the archive will not grow too much over time either. For instance, once a year they could archive their data, and each archive would contain a year's data.

If you are planning to do it with a single archive file, I would recommend using linked tables and append and delete queries. You would never need to programmatically create the database. You could simply do that in advance.

Good luck, and feel free to ask for assistance if you get stuck at any stage of the development.