Large FE database (Access 97)

Topic

New Reply

Using Access97, has anyone else had any problems with large FE databases?

I’m doing some contract work for a company, and the FE database is on the order of 20MB. We seemed to have started having problems with Access when doing development work in the database (each person has his own copy of database). Most of the problems seem to occur while in the VBA editor, the most annoying is frequent Access crashes. But today I had a problem in which a subform apparently wouldn’t load with the mainform, so any reference to it of course bombed. Then all of a sudden it start loading and I had no more problems!

I thought it was just me, possibly because I’m using an old (i.e., slow) system with not much of memory. However, my boss is having the same problems with Access crashing, and he has a new machine with alot of memory. He said he has found it helpful to deselect then reselect the DAO library reference, then recompile. I’ve found it better to decompile, then recompile. He also has an Access2000 version of same database, and I understand it is OK.

It is all very annoying.

Reply | Quote

Replies

Mark
Not sure of what you mean be an FE database, however I an running 600MB access databases in Access 97 in a production environment without a problem. This database is shared between multiple users (usually 5) with the database hosted on a server. I have my own copy of the database for development on my computer and have not had seen that problem.

I assume you have the latest patches applied to office, which OS are you using? My experience is with Win 98, NT4, and Windows 2000.

Sincerely
HumbleIrishman

Reply | Quote

By FE I mean a frontend database in a split frontend/backend (FE/BE) situation. OS version doesn’t seem to matter, and I know I have latest SR of Access97.

Reply | Quote

Mark

I am running a 130MB FE into my 630 MB database. Each of the 5 users currently has their own copy of the front end. I am experiencing no problems with this for the last 2 years. I have tried having all the users use the same copy of the front end and having each user have their own copy. There were no problems with either environment, except for the increased network time with all the users sharing the same copy of the FE database. In this environment, there are actually a number of different FE using the same BE database. I have described only one of the FE database applications. Activity on this application was relatively heavy. Some straight data entry and some inquiry based on telephone calls.

The databases are compacted nightly by a batch program.

If there are any details I can supply that would be helpful, please let me know.

Reply | Quote

This is not a user problem, it is a developer problem.

Reply | Quote

We ran a front-end (to SQL Server) that ran between 40 and 60MB (200 tables – most linked, 600+ queries, 150+ forms, 100+ reports, 40+ modules) for a couple of years in Access 97 and didn’t have a great deal of trouble – at that point some people were actually using a server version as well. So I don’t think you’ve run into Access limits. My guess is you may have some minor corruption somewhere. In the database we had one very complex form that we finally had to rebuild from scratch – it just got flakier and flakier – I suspect we finally hit the limit on the number of controls or some other weird thing. But that was about the only significant hickup. We converted it to 2000 about 2 years ago, and it continues to run fine, though it has been simplified some with a complex add-in. I presume you’ve tried creating a empty database and importing everything.

Reply | Quote

I hadn’t tried importing everything into a new database yet, although I don’t know what my boss has tried. It just started happening to me a couple of weeks ago, and I didn’t find out until yesterday that he was having problems also (I work at home, and we get together weekly at which time I get a new copy of the database). They do a few things I’m not wildly enthusiastic about. For example, I think they are converting back/forth between Access97 and Access2000. I think you are right, in that there has been some corruption introduced into the database. It has also become very slow just doing the normal things you do while working on forms and report (like saving). Of course, I had been attributing much of that to a combination of the size of the database and my own slow equipment.

Reply | Quote

Mark, did you mean 200Mb instead of 20Mb? I wouldn’t call 20Mb a large FE at all. I haven’t seen that kind of problem even with 200Mb front ends in A97, but they ran like snails.

Reply | Quote

No, I really meant 20MB! That is big by my standards. I don’t think I can even comprehend a 200MB frontend! How many objects are in it?

Reply | Quote

The appropriate word is “was”. It was a developer tool I built several jobs back to handle migrating data from huge mailing lists into databases and it usually weighed in around 140Mb to around 200Mb. It wasn’t so much the number of tables but all the queries, code and temporary objects that bloated it. You just couldn’t stop and compact when you were migrating a whole series of files into the appropriate tables. Of course, it didn’t hurt that there were usually at least 100K records in it and we would get lists of 50k to 100K names at a time. The queries were so big, I had to run them on a dual processor machine just to get them to run.

In the 97 databases for this company’s products, there are perhaps 250 forms and 200 tables, plus another 150+ reports, and the size is less than 30Mb straight out of VSS. Of course, that roughly doubles when you move it to 2002.

Reply | Quote

This database has far fewer tables, but more report,forms, and queries (well over 1000 objects). One thing I’ve really learned is that there needs to be tighter controls over standards. With over 600 queries, I know there is alot of duplication. Thing is, you never really know how/where a query is being used. So instead of changing an existing one, a new one is created. Some standard for documenting queries would really be helpful; that is, where used, purpose, etc.

Reply | Quote

I agree it is just too easy to create a new query rather than reuse an existing query with changes.

Some kind of cross reference tool would be great.

Reply | Quote

When you have a very large number of queries in a database, just making changes might be too dangerous to risk. If someone decides to change the order of fields or inserts one somewhere and you happen to be populating a listbox or combobox from that query, there will probably be a number of outraged reports of breakage before you track down the reason. Then when you fix the list or combobox source, you break whatever was relying on the changed query.

Reply | Quote

I totally agree with you on NOT making changes to queries. I certainly know what happens when you change a query that is used for a particular reason and how it can screw it up so easily, and the pain that goes after.

I just didn’t finish the sentence to clarify it.

Reply | Quote

We have used the cross-reference report in the FMS Total Analyzer for that exact purpose. It won’t find references in code however when they are in a SQL string, so you may also want to use something like SpeedFerrett. People hate to spend time house cleaning, but I actually spent a week doing that earlier this year on the database I mentioned, and managed to get rid of about 140 queries.

Reply | Quote

With regard to reducing the number of queries in a db you may find the article by Frank Kegley called “Just-In-Time” Queries worth a look. It

Reply | Quote

I’ve been using Rick Fisher’s “Find and Replace”, though I haven’t used the cross-reference tool yet. Probably a good time to give it a spin!

Reply | Quote