MS Access split large table into multiple tables

[SamGent]

I’m working on a project that has so much data that I’ve exceeded Access’s size limit (2 gigs). I’ve moved tables to other DB’s and linked the to the original, and again the size limit is near. I’m thinking that splitting a table into multiple tables in separate DB’s is the answer. There are only three fields in the table, so each table will contain the same type of date, IE an ID, a photo, and a note. The problem is that I am unable to access more than one table. Any ideas?

Thanks SamG

[Basscyst]

Usually when you get to that point, it's time to consider a more robust database solution. :thumbsup:

Basscyst

[SamGent]

Thanks, I know that, but there are budget issues. No funding to go to bigger and better. So in the mean time the computer guy has to fix the problems of others.

[Dr. Hanson]

Don't know if you're still around to read this, but here goes.

You have hit a Windows limit, not an Access limit.

No known solution can get you around the 2GByte limit for general database tables in Windows (the limit was once much smaller). In fact, the temporary file managment makes it difficult to create a database, or table, larger than about 1.5 to 1.8 GBytes. You can, of course, develop your own objects to manage this database but there are several simpler solutions.

For Example - If you examine your requirements carefully you may see that you can save your database as a table consisting of an ID, a text (or perhaps better, a memo) field, and a file path/name to the subdirectory containing the photo file. You can add more information by further partitioning your subdirectories by category. In the unlikely event you have actually developed a photograph search algorithm, you will find the files easier to address than the Access field. A small amount of effort, using common processes documented in multiple places elsewhere, will produce browse and transfer features to manage the ID and notes, the photo files and the directory structure.

Good luck. Think OBJECTS.

Regards.