Possible damage from 2gb limit

[chrisleng]

I think my database may have hit the 2gb limit, what damage has been done?



Some background:

I have made a reporting/processing suite in access that links to other databases/ODBC for its data,

About a year ago I hit the limit while messing with importing tables etc and it deleted all the VBA modules out and left the tables corrupted, I had backups but lost a days work which was a pain,

Since then I tend to take a copy every time I finish a feature, how ever yesterday i implimented a rather large feature so dont have a backup of it,

Today i noticed all the temp tables had dissappeared from the database, the only strange behavior yesterday was a string in a query that worked fine suddently started giving errors but i think i'd made an error in a function the query was calling elsewhere and for some reason this string was giving the error.




So assuming all the VBA code still looks to be present, along with forms and queries do you think i've got away with it and can continue using it?

[JoeM]

Do you Compact and Repair your database regularly? That will get your database from bloating and help keep the size down.

If it is still close to 2 GB after Compacting and Repairing, I think you need to make some changes, such as:
- splitting your databases (if you have not already done so)
- using something else that allows more memory to store the data on the back-end (i.e. SQL, MySQL, Oracle) - you can still use Access as the front-end
- if you cannot use another program to store the data on the back-end, look at creating mutliple back-ends (multiple Access dastabases) to store your data, or archive some of your data off from time-to-time

[chrisleng]

Hi,

Yes, the database is set to compact on close and generally all the temp tables are deleted once they are used, it was just while I was doing some testing I hadn't been deleting tables or closing enough,

The database size is now about 5MB as there are no tables in it currently and it has been compacted, I'm just wondering if i should worry basically? or can i just import the tables again and continue developing?

Cheers,

[JoeM]

Did you split your database so that all the data tables are in the "back-end", and all your Queries, Forms, Reports, and VBA are in a separate Front-End?
That may help with the size also (and is actually the preferred/recommended way of designing Access databases, especially if you have multiple users using it).

[chrisleng]

Yes, this is just the reporting front end i'm asking about, its just while I'm trialing new features I use "import" instead of "link" to take a copy of the data so it doesn't matter if I get an update query wrong etc, when its in a "production environment" it links to a number of back ends,

The problem was I basically imported all of the back ends to test some features and it pushed it over the limit which seemed to delete all the tables! i'm wondering if its caused any other damage (eg to VBA or Queries) or if I can continue using it?

[JoeM]

The problem was I basically imported all of the back ends to test some features and it pushed it over the limit which seemed to delete all the tables! i'm wondering if its caused any other damage (eg to VBA or Queries) or if I can continue using it?

No idea. If you have an old copy before this happened, you could use that (since the front-end shouldn't contain any permanent data anyway. Or if you don't, maybe at the very least import all your front-end end objects to a new database (to account for any potential corruption issues).

II trust you only copied data to your front-end for testing purposes, else you could have big data issues (since people coudl be working off different copies of the underlying data and some have been altered). I still think that physically importing data to your Front-End is a bad idea. If you want to work/test with some copy of your data, copy the data you want to another/different back-end, and link to that.

[chrisleng]

I had issues with the importing so went back to the main version and all seems ok

Yes, definitely! I mainly import data from our ERP system to process and run reports against, and make a few temp tables for additional processing, all are deleted on close and a compact run, does this not seem like good practice?

[JoeM]

As long as you are only using it for creating reports and are not trying to alter the data, temporarily copying data to your front-end should be all right, as long as you keep cpmpacting the database (which it sounds like you are).