Split table into multiple databases and keep relationships

[frogmann]

Hello,
I've seen various forums online that address this, but none that seem to clarify the issue for me.
I have a large .mdb file due to one table in particular which is very large (1,455,000 records and counting). The DB is still under 2GB but I will need a solution for this soon (and yes, will also look to migrating to something more robust, but for now need this solution).
The DB is already split into a front and back-end. I would like to split the large table that resides on the back-end into two different tables, keeping all of the same fields in each table but halving the number of records in each. Each of these tables would ideally reside in its own .mdb file. I can split the tables manually via make-table/append queries, but how do I maintain the relationships between these tables and the front-end (plenty of queries/forms/reports, etc that currently refer to the one table). More specifically: Is it possible to keep relationships between two .mdb files, and if so, how would I split the table in question in order to keep the relationships in doing so?
Thanks much for any help you may have to offer.

 

[dhookom]

I believe you are proposing much more work than simply upsizing to a more robust database server such as SQL Server or SQL Server Express.

Duane
Hook'D on Access
MS Access MVP

 

[frogmann]

I take it by your response, dhookom, that separating the table into two distinct dbs would necessitate relinking all relationships manually, + whatever else? I didn't know if there is an analyzer function that might do the work for me, or what have you. Can't afford SQL Server... will probably migrate backend to MySQL, but that is also a large undertaking with all of the code on the front end.
Thanks for your reply.

 

[dhookom]

SQL Server Express is free. I have it running on my laptop. It's a great alternative to SQL Server for building and testing. Later, you might want to upgrade to full SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

frogmann,

What you are asking to do might work but will be EXTREMELY slow...

You would have to use one file for new and updatable records and the other as a warehouse.

Secondly you will need to make a Union query that unions the split tables. Assuming you are in the know, your autocorrect is off so you can make the linked table names different than the original. Next you can make the union query the same as the original table name. Finally you need to update any forms that update or add records to point to the linked table that is assigned the task.

As for relationships, you can make a relationship to a linked table, however you can not set referential integretity or anything really useful.

Instead of splitting the Records among files, you might consider splitting the fields. The total space among the files in aggregate will be more but this should be more friendly than the union but still A LOT SLOWER than what you have now.

 

[frogmann]

Hello,
Thanks to all for your responses. Yes, it seems clear that splitting the table is not the way to go. I believe I have time yet to migrate to something more robust, so that is the way I will go. Your help on this was very much appreciated.