Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Large FE database (Access 97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large FE database (Access 97)

    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

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Large FE database (Access 97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large FE database (Access 97)

    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.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Large FE database (Access 97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Large FE database (Access 97)

    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.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large FE database (Access 97)

    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.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Large FE database (Access 97)

    This is not a user problem, it is a developer problem.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Large FE database (Access 97)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Large FE database (Access 97)

    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. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> 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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Large FE database (Access 97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Large FE database (Access 97)

    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.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Large FE database (Access 97)

    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.
    Wendell

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Large FE database (Access 97)

    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

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Large FE database (Access 97)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •