moving tables between databases

[TheShabz]

Hi everyone.

I'm working on a workflow db that has multiple processes, each of which outputs multiple excel docs. This will be a very commonly used db which will have anywhere from 100-50k+ records in each run. I want to be able to save these resulting tables for archival purposes. Obviously, my db will exceed the 2gb max very quickly. A possible solution I have come up with is to have archive dbs that will store these tables. While I am able to open another db and save a table into it with VBA, I'm not able to figure out how to manipulate the folders with VBA. For example:

i have:
Workflow DB
-process A
-process B
-process C

i want to send outputs to:
process A Archive


- tbl A (folder)
--a1
--a2
--a3
- tbl B (folder)
--b1
--b2
--b3

process B Archive
- tbl A (folder)
--a1
--a2
--a3
- tbl B (folder)
--b1
--b2
--b3

process C Arvhive
- tbl A (folder)
--a1
--a2
--a3
- tbl B (folder)
--b1
--b2
--b3


Has anyone ever done something like this? Some tips would be appreciated

[NTC]

What I might consider would be to do a simply copy/paste the mdb file for the archive just by giving it a unique date in the file name.

Then just run a simple set of DeleteQueries - that clear out all the records from the tables that are to received new info. This leaves the tables in place and won't disrupt queries/forms/reports that rely on these objects.

Hope this helps.

[TheShabz]

I still want to separate out the different processes into different DBs. Further, the resulting table names aren't constant so I would have to go in and manually delete them. The real issue at hand is how to manipulate the folders in the database window. Is that even an object/property that can be manipulated in vba?

[KathyL]

Hi everyone.

I'm working on a workflow db that has multiple processes, each of which outputs multiple excel docs. This will be a very commonly used db which will have anywhere from 100-50k+ records in each run. I want to be able to save these resulting tables for archival purposes. Obviously, my db will exceed the 2gb max very quickly. A possible solution I have come up with is to have archive dbs that will store these tables. While I am able to open another db and save a table into it with VBA, I'm not able to figure out how to manipulate the folders with VBA. For example:


Has anyone ever done something like this? Some tips would be appreciated

I don’t know the number of fields in your records, but 50,000 to 100,000 records by itself may not get anywhere near 2gb. I’ve got a db with easily a million records and it’s size is under 200MB.
It would be very easy to use linked tables between databases, I do that all the time. You can then use action queries to add and delete records in the tables of the other database.

[TheShabz]

I'm thinking long term. I'd say there would be 5-10 tables added every week. over a 52 week year, it adds up. It's really for organization's sake. I just want them archived.