Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by Ajax View Post
    Personally I would do the brand new database now. All tables closely related to each other should be in one db and not split as you have based on size.
    That's where I'm heading, but for sheer size reasons it's getting too big for all the data in one spot.
    It's a horse racing database.

    As of right now:

    I have a large database (call it Results) with all data tables related to the results of the races that came external sources.

    I have a large database (call it PPs) with all the data tables related to the horse's past performances that came from external sources.

    I have a primary database (call it Horse) with all the queries that use the data in tables in Results and PPs to create either reports, brand new smaller tables with calculated results/data, and the macros that run imports and queries.

    I actually feel like it's better organized now (assuming it doesn't blow up again).

    I think I can also probably clean up the Horse data base a bit by deleting a lot of old queries that were 1 time research reports than I never run anymore. They cause clutter, confusion, and possibly issues with resources. What started out as a small project grew and grew into 5 years of imported and calculated data.

    Just pray it doesn't blow up again on me again tomorrow when I do some more testing. The last 2 days have been brutal for me and I can't handle any more today. I dedicated 5 years of daily work to this and there are so many tables and queries involved, it's very difficult to cope with mentally if I have reorganize again.

    Does all that sound reasonable?



    Thanks for the help so far. Hopefully I won't need any more.
    Last edited by wcrimi; 12-30-2019 at 09:43 PM.

  2. #17
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Geez, when you mentioned horse racing, I remembered this from a few years back....

    I think Ajax's advice is on target. The only thing I would add is - design a backup strategy and test it
    and keep a backup rotation (eg. 2 or 3 copies). 5 years of daily work deserves some "protection".
    Good luck with your project.

  3. #18
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    Quote Originally Posted by orange View Post
    Geez, when you mentioned horse racing, I remembered this from a few years back....

    I think Ajax's advice is on target. The only thing I would add is - design a backup strategy and test it
    and keep a backup rotation (eg. 2 or 3 copies). 5 years of daily work deserves some "protection".
    Good luck with your project.
    I generally do a backup every week and keep 4 generations. Every once in awhile I skip a week if I forget to do it. That's what happened this week. So my latest backup was 12/15 instead of 12/22. The major problem I was having was that when I brought the 12/15 backup back up to date, I had the same problem again. That's what I have been struggling with.

    Please read my previous post. Then you'll understand why I don't have all the data in one spot like Ajax suggested. It has become gigantic. I have to have data in multiple databases. The large tables in multiple databases are linked into a main database that has all the queries that produce the reports and some smaller output tables. The only thing I haven't done (as he suggested) is move those smaller output tables to yet another database so the queries are all alone.

    I remember that original post. :-) What started out as a small project has become a monster. Let's just hope the monster is still alive.

  4. #19
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    "Note that the file size limit is likely not the issue. It will be network related (dropping packets or connections) or other system resources. IIRC, during a c/r operation, Access creates a backup but temporary copy which gets removed when done. This could be corrupted if you run out of disk space or memory during the operation or Heaven forbid, are doing any of this over WIFI."

    Given what you know now, do you have any guess as to what the problem was (and/or could still be) if it wasn't file size limit?

    That's what scares me. I have reduced the size of the main database and reorganized the entire system better, but I was never actually that close to the file size limit when this started. Yet I was initially getting a resource error. Obviously there is no network, no wifi etc.. It's all on my PC. This is an image of one of the original errors.


    Click image for larger version. 

Name:	Msyserror.PNG 
Views:	28 
Size:	14.3 KB 
ID:	40569







  5. #20
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Review the Similar Threads at the bottom of the page and any links they may offer. You may find some info or suggestions.
    I have not experienced Resource Exceeded, but I have seen that issue mentioned in various forums over the years. As I recall there wasn't a single factor that caused the error, but the error was raised in a variety of situations.

    Again, good luck.

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Given what you know now, do you have any guess as to what the problem was (and/or could still be) if it wasn't file size limit?
    Sorry, not really. I have experienced the problem before, but it was during the operation of a db (not mine). If you research the issue as Orange suggests (here or elsewhere) there's a lot of posting about it, with the primary 'fix' being a registry tweak. The usual suspects don't seem to apply in the case of doing c/r on a db because you're not running any queries or code.

    I suggest you look into Task Scheduler to perform your backups or create a recurring Outlook appointment or task so that you don't forget. TS may not be viable in your case.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If a single database file was 1.3Gb then I believe you probably would be heading towards the max file size, as things expand rapidly during manipulation of data, and it sounds like you have some sizeable tables.

    In addition to the advice already given, in the Long term I would probably look at moving your backend into SQL Server Express which is free and has a 10Gb database file size limit, which would be much more usable for you.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #23
    wcrimi is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Nov 2014
    Posts
    142
    I seem to have this under control for now, but still do not entirely understand all the events or why they occurred. Thank you immensely for the technical and emotional support. If it should come up again, I'll be back.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. TransferSpreadsheet: Out of Disk Space/System Resource Exceeded
    By arctic_blizzard in forum Programming
    Replies: 10
    Last Post: 03-29-2018, 06:55 PM
  2. Getting error System resource exceeded
    By princydisney in forum Access
    Replies: 1
    Last Post: 08-22-2017, 04:55 PM
  3. System resource exceeded
    By PJ Crittenden in forum Import/Export Data
    Replies: 1
    Last Post: 10-10-2014, 10:28 AM
  4. System resource exceeded
    By fdormoy in forum Access
    Replies: 7
    Last Post: 07-17-2014, 10:50 AM
  5. System resource exceeded
    By ahmed_ae in forum Queries
    Replies: 1
    Last Post: 08-01-2013, 12:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums