none
ACCESS DATABASE SIZE ISSUE

    Question

  • Hi !

    My company has an Access Database that almost reach the maximum size.

    They only give me a month to solve this issue since the data will be corrupted soon.

    What is the fastest and  the easiest way to solve access size issue?

    I saw there is some people did different way to solve this size issue.

    - Upsizing ms access

    - Migrating everything to SQL Server and create a new UI

    - Move the tables only to SQL Serve Linking it to MS Access


    GUKGUK
    Wednesday, October 13, 2010 8:34 AM

Answers

  • Multiple backends is a less than ideal solution: for one thing, you cannot establish relationships between tables in different databases, so you lose the advantages of referential integrity. Performance will indeed be affected, how much depends on your specific situation.

    If there are logical groups of tables that don't get used together very much, that may be a rational way to separate them.

    You'ld create two (or more) new, empty databases; use File... Get External Data... Import to import the appropriate tables into each; delete the tables from the frontend (you DID make a backup, or two or three, right?), compact all three databases, and use File... Get External Data... Link to link the frontend to the two backends.

    It's worth a try, but it would be much more useful to find out why your database is getting so big. Two gigabytes is a WHOLE LOT of data - as noted elsethread, millions and millions of records of normal size - so there's something odd going on. Please answer the questions the other volunteers asked - it would be embarrassing to go to all this effort only to find that a Compact and Repair would have taken you down to 100MByte.

     


    John W. Vinson/MVP
    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 9:17 AM
    Thursday, October 14, 2010 3:02 AM
  • GUKGUK,

    Splitting is really very easy. There is even a wizard to help you do it. You should be able to  split the database in less than 15 minutes.   You will also need to take some time to learn about the Link table manager, deploying updates etc.   

    I have writing this on the subject:

     Splitting your Access database into application and data
    At the end you will find links to additional information on splitting.

     


    Boyd Trimmell aka HiTechCoach Microsoft Access MVP
    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 9:17 AM
    Thursday, October 14, 2010 3:04 AM

All replies

  • "GUKGUK" wrote in message news:6f2836d4-f8bc-4cc1-b099-1c7c945026e4@communitybridge.codeplex.com...

    Hi !

    My company has an Access Database that almost reach the maximum size.

    They only give me a month to solve this issue since the data will be corrupted soon.

    What is the fastest and  the easiest way to solve access size issue?

    I saw there is some people did different way to solve this size issue.

    - Upsizing ms access

    - Migrating everything to SQL Server and create a new UI

    - Move the tables only to SQL Serve Linking it to MS Access


    GUKGUK
     
    What is the size after a compact and repair?
     
    Perhaps you just increase the frequency of this time honored and regular maintains.
     
    Some of my clients can go for 6 months without a compact and repair, some have to do it near daily. So
     
    So, how often do you do a compact and repair now?
    And, what is the size of the database after a compact and repair?
     
    Often changing your database administration and maintains process that you do now on a regular bases can buy you a lot of additional time while you consider something that gives you additional storage ability.
     
    Another possible strategy is to try and determine what part of the data is taking up so much space now. I mean, if a typical customer record is 120 characters, then
    the number of records you can store is:
     
    2 gig =  2,147,483,648 Bytes
    1 Character = 1 Byte
    120 Characters = 120 Bytes
     
    2 gig / 120 = 32 million records
     
    So, 10 million records would fit quite comfortably inside of access. So in addition to the above issues of regular maintains such as compact and repair (how often do you do this), what is the max number of records in the largest table (is it in the millions, or 10's of millions?).
     
    So, how many records in the largest table? Can this data be archived on a yearly or regular basis out to another file?
    How often to you compact and repair as part of your regular maintains schedule?
    How much does the database reduce after a compact and repair?
    Can you increase the frequency in which you do this regular maintains?
     
    Start by answering the above questions, as those answers can buy you time as you consider using something like sql server with more storage ability then access.
     
    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    Pleasenospam_kallal@msn.com
     
     
    Wednesday, October 13, 2010 8:58 AM
  • Hi GUKGUK,

    according to my (little) experience of moving to SS I can say that there is a very fast way.

    1. Move tables to SS. Some people advise to use SSMA (http://www.microsoft.com/downloads/en/details.aspx?FamilyId=133B59C2-C89C-4641-BEBB-6D04476EC1BA&displaylang=en this link for SS2008, you can find there links for SS2005 and SS2003 too). If you have rights to install programs you can try. I don't have these rights, so, I did this process manually + using built-in Upsizing Wizard.

    2. Delete all linked tables from your FE and create new linked tables. Now they are linked to SS. Create them with DSN and then just make this connection DSN-less using simple procedure described here http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0f2a5eac-fc54-4f64-9bf8-b7d7dafe16a9/#f4e995a2-6065-4fcb-9301-bd71ce77fd8e

    3. Make some changes within your code. The only changes I've made were adding twice dbSeeChanges to a recordset options.

    I think it's a really fast way which won't make you changing your code or db logic much.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, October 13, 2010 9:08 AM
  • First make a copy of the database and compact it. If it is still large, then

    Move the tables to SQL Serve Linking them to MS Access front-end.


    Arvin Meyer, MCP, MVP
    http://www.datastrat.com
    http://www.accessmvp.com
    http://www.mvps.org/access
    Co-author: "Access Solutions", published by Wiley

    <GUKGUK> wrote in message news:6f2836d4-f8bc-4cc1-b099-1c7c945026e4@communitybridge.codeplex.com...

    Hi !

    My company has an Access Database that almost reach the maximum size.

    They only give me a month to solve this issue since the data will be corrupted soon.

    What is the fastest and the easiest way to solve access size issue?

    I saw there is some people did different way to solve this size issue.

    - Upsizing ms access

    - Migrating everything to SQL Server and create a new UI

    - Move the tables only to SQL Serve Linking it to MS Access


    GUKGUK


    Arvin Meyer MCP, MVP MS-Access
    Wednesday, October 13, 2010 9:30 PM
  • On Wed, 13 Oct 2010 09:08:30 +0000, Andrey Artemyev wrote:
     
    >3. Make some changes within your code. The only changes I've made were adding twice dbSeeChanges to a recordset options.
     
    You also need to watch out for code that obtains a new Autonumber key
    right after a recordset .Add.  This works with an Access back-end but
    not SQL Server (Identity).  Switch it to the LastModified technique,
    which works for both Access and SQL Server back-ends.
     
    I explain this change and more in "Best of Both Worlds" at our free J
    Street Downloads page: http://ow.ly/M2WI.  It also includes some
    thoughts on when to use SQL Server, performance and security
    considerations, concurrency approaches, and techniques to help
    everything run smoothly.
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     
    Wednesday, October 13, 2010 11:54 PM
  • GUKGUK,

    Just to be clarify, is your database split into a front end and back end (tables only)?

    If not then this is the first step. Each user will need a separate copy of the front end.

    Now compact the back end to see how large it is. 

    If it is almost 2 gig then you have some choices:

    1) Upsize to an SQL server back end.

    or

    2) Split the table into multiple Access back ends. I generally prefer to have all my tables in a single back end to all for Referential Integrity to be enforced. I some of my apps I have logging/audit trails  that log all the activity for all users. This table gets large fast. I usully place this table in a separate back end database.


    Boyd Trimmell aka HiTechCoach Microsoft Access MVP
    Thursday, October 14, 2010 12:57 AM
  • Hi Boyd!

    After I read about spliting an Access Database, It seems it only seperating the front end from the back end.

    This is what I get from one of Microsoft article :

    One database (called the back-end database) contains only the tables and relationships and resides on a network file server. The other database (referred to as the front-end database) contains all the other database objects: queries, forms, reports, macros and modules and resides on each user's computerOne database (called the back-end database) contains only the tables and relationships and resides on a network file server. The other database (referred to as the front-end database) contains all the other database objects: queries, forms, reports, macros and modules and resides on each user's computer

    But from your suggestion , you can split your back end to many different database files.

    I just want to confirm with you .

    How long roughly to do split back end into couple of files?

    Do you know any article to do this back end spliting ?

    Is there performance issue?

    Thanks Boyd

     

     

     

     


    GUKGUK
    Thursday, October 14, 2010 2:48 AM
  • Multiple backends is a less than ideal solution: for one thing, you cannot establish relationships between tables in different databases, so you lose the advantages of referential integrity. Performance will indeed be affected, how much depends on your specific situation.

    If there are logical groups of tables that don't get used together very much, that may be a rational way to separate them.

    You'ld create two (or more) new, empty databases; use File... Get External Data... Import to import the appropriate tables into each; delete the tables from the frontend (you DID make a backup, or two or three, right?), compact all three databases, and use File... Get External Data... Link to link the frontend to the two backends.

    It's worth a try, but it would be much more useful to find out why your database is getting so big. Two gigabytes is a WHOLE LOT of data - as noted elsethread, millions and millions of records of normal size - so there's something odd going on. Please answer the questions the other volunteers asked - it would be embarrassing to go to all this effort only to find that a Compact and Repair would have taken you down to 100MByte.

     


    John W. Vinson/MVP
    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 9:17 AM
    Thursday, October 14, 2010 3:02 AM
  • GUKGUK,

    Splitting is really very easy. There is even a wizard to help you do it. You should be able to  split the database in less than 15 minutes.   You will also need to take some time to learn about the Link table manager, deploying updates etc.   

    I have writing this on the subject:

     Splitting your Access database into application and data
    At the end you will find links to additional information on splitting.

     


    Boyd Trimmell aka HiTechCoach Microsoft Access MVP
    • Marked as answer by Bessie Zhao Tuesday, November 09, 2010 9:17 AM
    Thursday, October 14, 2010 3:04 AM
  • Hi Armen,

    thanks, I will take it into my account.  I just wrote about changes I'd faced in my app. Of course, there are some other differences between different BE platforms. I've already read your article (presentation) twice and it's already helped me a lot. Thanks again!

    Hi GUKGUK,

    yes, how often do you usually run C&R?

    If it is a real data size I think this person will be very interesting in it :) http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/f3c20f6b-9ec5-43e6-ac83-cdf955b8386e

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Thursday, October 14, 2010 6:11 AM
  • Hi All!

    Thank you for helping me.

    I have tried several of your suggestions. It did reduce the size of the file.

    The access application that my company using currently split into front end and back end.

    There is no relationship in the database because when I click Relationship icon , it automatically pops up Show Table.

    The back end size is around 1.5 GB.

    The front end is deployed to different client pc and those client pcs access the back end from a shared drive.

    The step that I did to reduce the size are :

    1. Compact and Repair as Albert suggested. This step reduce the size to 1.4 GB only

    2. Create two new blank database then Get External Data-> Import -> Table that has large data

    3. Delete the tables that I have imported previously from the database.

    4. Compact and Repair the database again to take effect the deletion. The size of the database has reduced to 1,208 kb

    5. I link the current database to the new databases that i just created Get External Data -> Link Tables

    However I have another question guys,

    Is there possibility the front end insertion and deletion will be effected since I moved the big size tables to another databases and link it ?

    I check the select query and it seems fine.


    GUKGUK
    Thursday, October 14, 2010 7:17 AM
  • Hi Gukguk
     
    "GUKGUK" <=?utf-8?B?R1VLR1VL?=> wrote in message
    news:e9c8715a-1519-4e61-94cc-3739db708c58...
    > There is no relationship in the database because when I click Relationship
    > icon , it
    > automatically pops up Show Table.
     
    and
     
    > Is there possibility the front end insertion and deletion will be effected
    > since I
    > moved the big size tables to another databases and link it ?
     
    If you really don't have any referential integrity in place there will not
    be any effects for deletes and inserts, as well as for updates.
     
    Henry
     
     

    [MVP Office Access]
    Thursday, October 14, 2010 9:50 AM
  • For starters you could create another MS Access database for the tables only and store this database on a shared drive. Then provide the users with another MS Access database that is linked to the shared drive tables.

    I would also take a look at creating archive tables and move the archive data to the archive tables periodically using a VBA to determine the archive trigger date and then use an SQL query to move the data to your archive tables.

    Just throwing some thoughts out there.

    Hope this helps.

    Thanks

    Thursday, October 14, 2010 1:13 PM
  • GUKGUK wrote:

    My company has an Access Database that almost reach the maximum size.

    They only give me a month to solve this issue since the data will be corrupted soon.

    Do you have any graphics or embedded files in any of those tables?  If so put those
    files on the servers hard drive and have some kind of method pointing to those files.

    Tony

    Friday, October 15, 2010 10:25 PM