Results 1 to 6 of 6
  1. #1
    ccordner is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    65

    Database size

    Hi



    I am a bit concerned about the size of my Access Database. There are no pictures or anything, just a lot of tables and queries: yet the size is 150-200MB already!

    I am a bit concerned about how much this will grow when the tables start to get a lot of data in: it's manageable when working on a wired LAN connection at the same location as the server, but when working via our WAN or on Wireless it can take ten or twenty minutes to save and close!

    I have set it to automatically 'compact and repair'. I did think about trying to modify the field types to save on space, but many of them are links to autonumber fields in other tables, so they have to be Long.

    Is this a common problem or is it likely I've made some glaring error?

    Thanks
    Chris

  2. #2
    bigroo's Avatar
    bigroo is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2012
    Location
    Austin, Texas
    Posts
    101

    Database Size

    Hi ccordner,

    If you have a lot of tables and lots of forms, this will blow the database size out, Also Linked tables dont help too much either.

    I compact all my databases on close to try and keep the size down as much as possible.

    I have one database which imports 9 text files which I run daily, some of the text files which I import, 3 infact have about 800,000 rows. All the other 6 text files have around 200 to 300,000 rows, so this is big. After compacting is around 460meg.

    James
    Austin, Texas

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    Try splitting your database, so that the data is in one .mdb, and everything else in another. When you say "lots" of tables and queries - how many is "lots"? How many records approx. are in the tables?

    John

  4. #4
    ccordner is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Nov 2011
    Posts
    65
    Thanks for this.

    My database is in two portions: one with tables, one with queries and reports etc.

    The tables bit is approx. 100MB, the queries/reports bit is around 180MB.

    The database size isn't really a problem on my computer, but most people access it via a network and it can be slow: especially shutting down.

    More importantly, I access it via Excel, through vba Code and I think (I haven't been able to prove this) that what is happening is that when the code closes the database, it doesn't always close it properly (because it takes so long) and corrupts the database, so I have to keep replacing the 'Front End'. This of course causes problems when I'm not there to do it.

    When I say lots, I mean maybe twenty tables, about half of which have a negligible number of records, but the rest have maybe four hundred records: which will rise drastically (That's about two weeks' worth of information and I would hope the database would retain it for at least twelve months without archiving).

    Given that much of this information is just integers, it seems very high!

    Is there a database package which might be preferable? I need to be able to access it via VBA code from Excel and hopefully import data from Excel (so I could transfer it across from the old Access file).

    I'm starting to have reliability concerns about Access, which strikes me as odd!

    Thanks
    Chris

  5. #5
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Those database sizes do seem to be high, especially the data part. As for the slowness bit - are all your users accessing the same front end on the network? If so that is the wrong approach - each user should have the front end on their own machine; having all users accessing the same fornt end file is just asking for problems, and that could easily be the source of your corruption issues. It could also explain the slowness, since all users are competing for the same resources, and it increases network traffic as well.

    You said that you have implemented compact and repair on close. From my own experience, that does not work for linked-table databases, and it's not a good idea anyway if there are multiple users. That could also explain the very slow shutdowns - if you compact and repair on close, how can MS Access do that if other users have the database open?

    Work on getting each user their own copy of the front end, and see if that helps.

    John

  6. #6
    Albert_Eines is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    3
    Description: Kernel for Access Repair is the technically advanced tool that helps in retrieving lost Access database by repairing MDB and ACCDB files. This tool performs a recursive scanning of the lost files and folders and successfully retrieves the data back.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database size
    By pontitt in forum Access
    Replies: 3
    Last Post: 06-17-2011, 11:45 AM
  2. Database Size?
    By AZ001 in forum Access
    Replies: 3
    Last Post: 06-16-2011, 12:31 PM
  3. Size of Database seems off
    By shanej100 in forum Access
    Replies: 2
    Last Post: 02-24-2011, 09:50 AM
  4. Database Size
    By Rick West in forum Access
    Replies: 3
    Last Post: 07-04-2010, 11:36 AM
  5. Database Size
    By JohnTyke in forum Access
    Replies: 4
    Last Post: 10-08-2009, 04:07 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