Large Access DB corruption

[johns88]

I have a large MSAccess 2000 database (1.5 GB) that I developed over time to provide quick access to transaction details and summaries for my company. (Yes, I know I should move this to SQL server but our SQL gurus are tied up on other projects)

I update this database every month and create many summary tables using a series of Macros that run multiple queries and VBA procedures to create and update these tables.

After each macro I need to compact and repair the db to keep the database size down.

I have noticed that during this process if the Access database grows to a size greater than 2 Gb the database appears to corrupt and I get errors such as "invalid argument" when running queries that I know have no errors. This happens in Access versions 2000, 2002, or 2003. I am running WinXP on a 1.2G P3 with 512Mb Ram.

When this happens, I have to go back to an earlier version of the DB and start again with fewer procedures in each macro. If I do this along with the compact and repair after each macro, then all the procedures complete without a problem.

My questions are:

Is this an inherent limitation in MSAccess that I just have to live with?

Are there some steps I can take such as change a registry setting or increase memory etc. that will solve this problem?

This database will just get larger every month as I need to update this monthly.

Thanks in advance for your help.

John

 

[genomon]

You got it! - This is from Access help:

Microsoft Access database table specifications
Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 2048. The actual number may be less because of tables open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of characters in a Memo field 65,535 when entering data through the user interface; 1 gigabyte when entering data programmatically.
Size of an OLE Object field 1 gigabyte
Number of indexes in a table 32
Number of fields in an index 10
Number of characters in a validation message 255
Number of characters in a validation rule 2,048
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object fields) 2,000
Number of characters in a field property setting 255

 

[Golom]

The database max is 1GB (see genonom's post above) for Access 97 and 2GB for Access 2K and above.

You might try moving large tables to another DB and then linking them back to this one. That should reduce your physical size in the database while you wait for the wonderful world of SQL Server to arrive.

 

[DanJR]

what about using MySQL to store the data?