Okay, so I have a client that wants me to support an MS Access database to improve upon the exisitng implementation. Fine, no problem, right?
I can't get into any of the forms because of an error stating "not enough disk space" ... or was it "not enough memory" (sorry, it's not in front of me). Either way, the database is run from a Network Drive with plenty of space (the local machine has plenty of space too) with no other applications running. I haven't had much luck tracking this down via google or Microsoft K-Base. Anyone have a similiar error or ideas?
Also, the first problem leads me to the greater question of scalability. The database in question is fully developed in MS Access (not just for GUI design). The .mdb file is around 25MB with around a dozen forms as well as tables, macros, queries, etc. I believe the largest table contains around 2500 records at maybe 5-10k of data each (it's a guess). How much can Access handle? Is it dependent on the PC it's running? Is there an accepted point where I should consider using MS Access to only creat front-ends?
Any help or reccomendations are appreciated. I'm definately frustrated at how this application is just not working right now. I have knowledge of both SQL Server and MySQL and may want to move completely to that, but it may be a tough sell unless I can convice the powers that be that it is entirely necesary.
1. MsAccess can handle large Db´s, 'probs' will occur at 1 to 1.5 Gb, probs may be slower, and index-building ..... The capacities of de PC, disk, internal mem is related to that too.
2. front end / back end, well that a choice you have to make. If the Dbase is a single-user, and it will not exceed for instance 250 Mb you can keep it just like it is.
On the other hand if the code must be regulary updated, or the added data in a certain period is large, well in that case make a front- backend dbase, in which case only the frontend must be updated with additional, or changend code, and the data (backend) doesn´t have to be updated also, and de user can go on during the coding period.
The size of the database is dependant on the version you are running. Access 97 has a 1 GB limit, 2000 has a 2GB limit and I am not totally sure about the rest.
As far as not being able to open the forms. You should check the envornment variables of the local machine. Find out where the Temp folder is and make sure you have read/write access to it. Then check the Virtual Memory for your system. Look at the properties for My Computer. In Windows 2000 it is in the Advanced Tab under the Performance Options. You might try bumping the paging up if it is low. Mine is 1.5 GB. Older versions of Windows will have this smaller. The other thing you might try is another local system. Copy the database and take it home or to another PC.
Just for the record, "Not enough memory" is the old stand by error in Access when something happens and it can't give you a better explanation. What I'm saying is that it probably has nothing to do with memory at all. In fact, you can get this error if you have TOO MUCH memory!!! See this article here
Split vs. Single: if there is a lot of GUI stuff, then split it. Let the server handle the data side of it and each user's PC handle the GUI side of it. I've found that Access over a network is much faster if you have a front end running locally.
Also, when is the last time it was compacted? compiled? decompiled? Make a copy of the database, then do the following, in this order:
1) Turn off the "Startup Routine"
2) Open a module and under debug "Compile and Save all Modules"
3) Close the module and click Tool - Database Utilities - Compact Database
4) When it reopens, close it.
5) Launch it from the command line with the switch /decompile
6) Go back in, open a module and under debug "Compile and Save all Modules"
If you make it through all that, you'll probably find it is smaller, faster and more reliable. If you don't then there's likely a problem somewhere in it.
Here's another note about lots of code (you said it was a "fully developed application"). Access 97 can only compile so much code at once. When it runs into it's "designed limit" of code compile space, it spils into Jet's data handling space. Once that is used up, it gives you an "Out of Memory" error. COnsequently, this is more likely to happen while you are developing it. A short term fix is to go in the registry and up Jet's memory allowance to the limit. The long term fix is to upgrade to a newer version of Access. I can't place my finger on the KB articel describing this one - sorry!
All in all, if you search the Microsoft KB for "Out of Memory and Access" you'll get lots of responses.