Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Split Table Design File Size (Access 2003/XP)

    Hi everybody:

    I've developed an application for a single user that runs from the network (his PC is packed, and I like things to be backed up). It imports tables from two production databases, incorporates data into some additional .mdb tables, does a bunch of calcs and provides the user with a number of complex forms for a variety of purposes. The file size is 286.4 meg.

    One major component will be now used by another employee, the first one got a memory upgrade, and since awhile back we were experiencing some module damage, I was thinking of splitting the database into a client-server design, with two front-ends. So I used the database splitter wizard and then imported the import and update modules into the backend, thinking that by eliminating all the forms, reports and related code, the database size would be reduced considerably. Wrong!

    Even after opening the backend file uncompiled, compacting it, recompiling and recompacting it, it is still 261.4 meg. I realize that Microsoft claims that an Access file can go to 2 gig, but I plan to do more development and want to keep things as slim as possible.

    Any ideas? Should I go to a 3-tier design and, if so, how could that be done in VBA?

    Thank you, in advance, for your help.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    Forms reports queries etc don't usually consume a lot of space. If you saw 20 MB reduction by eliminating them, that would be a lot. Sounds like you have a lot of breathing room before you hit the 2 GB barrier.

    Your BE should contain *only* tables, while your FE should contain *only* forms, queries, reports, etc. I don't understand why you imported some modules into the BE. If you have multiple users, then each should have his own FE installed on his local drive, and linked to the BE on the server.

    AFAIK, Access is not suitable for 3-tier design.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    Because the be modules import data into the backend from other production databases and then integrate it with other .mdb tables. I don't want multiple users' front-end apps running the backend import code!

    Don't worry, I know how to set up the users.

    Can you tell me what the reasons are that "Access is not suitable for 3-tier design"?

    Thanks!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    > Can you tell me what the reasons are that "Access is not suitable for 3-tier design"?

    Access is not a database server. In a split frontend and backend design, only the frontend is "active"; the backend is merely a passive storage container for the data. If you would include a middle "tier", it wouldn't be able to contribute anything.

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

    Re: Split Table Design File Size (Access 2003/XP)

    I would suggest you reconsider putting modules in the back-end. We see considerable file bloat when you do any design changes using a database that uses the 2002/2003 format - the 2000 format seems less prone to that. For example, we have a front-end database that is about 12MB in compacted state. I made a few simple design changes to queries, added a simple form, ran a few tests, and it had ballooned to 50MB in 2002 format. The same steps in a 2000 format database caused it to grow to 13MB. Putting modules into the back-end and doing design changes and testing in the back-end will almost certainly cause it to bloat considerably and also increases the possibility of corruption.

    To solve the issue of not having two users do the import funtion, I would either implement Access User Security and restrict users so that only one could run the process, or I would create two different front-ends, and only have one FE include the module that does the import. If this doesn't make sense, please post back.
    Wendell

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    Could she not also automate the module to only run once per day? Or of only one user is logged in? I realize it takes a little bit more code, but maybe that could help?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

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

    Re: Split Table Design File Size (Access 2003/XP)

    Just create a separate "ImportOnly" FE which only includes what you need to run the imports. Then give this FE to only 1 of the users.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Split Table Design File Size (Access 2003/XP)

    Then who DOES run the backend code? If the database is designed properly, the code can only be run through the user interface, not directly from the database window, since that is hidden to the users. If someone is opening the backend and running the code in it, when do they do it? If the code is running on a timer, how do you handle errors? Your suggested design is fraught with potential pitfalls. A major one is that moving the data to a backend reduces the instance of corruption causin data loss. Any code container, including modules can become corrupted. When that happens, you fall back to a backup or copy a new front end over the old. You can't do that if the modules become corrupted in a backend.
    Charlotte

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    Perhaps you could explain in a little more detail.

    I would never allow a user to run my backend import and update code, since I need to supervise it., although I do have user security implemented. I also have a log table in the backend to which each function writes a line on completion or, if an error occurs, writes the function name, error number and description.

    I'm thinking of leaving it as a front-end back end split design, but was wondering about the possibility of moving my import and update backend code into commponents.

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

    Re: Split Table Design File Size (Access 2003/XP)

    You could move that code into a library database, if that's what you mean by components, and then set a reference to the library in the front end of the application, or of whatever application you use to run the code. If you are talking about some other kind of component, you'll have to explain. Even in 2003, Access is not client-server, and it is not really DotNet compliant.

    I hope your log table only tracks the import and update stuff, otherwise, it will bloat the back end beyond reason. You also need to have some means of exporting its contents to a text file or xml and truncating the log. Actually XML would be a better repository for the log than keeping it in the database.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Split Table Design File Size (Access 2003/XP)

    Yes, it only tracks the import and update stuff. It then sends me an email with an attached report based on the log table before shutting down on completion. (I have set compact on close to true). The log table is cleared before the next run.

    You're probably right about using XML instead of a table, but I haven't mastered that.

    I haven't used library databases before; thanks for the suggestion, I'll look into it. Any heads up info I should know about them?

    Also, your comment that Access is "not really DotNet compliant" raises a flag, because one of our production databases is in Access, and the vendor is planning to convert it to DotNet. Where can I find more information about any potential issues?

    Thank you so much for your time. I appreciate your help!

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

    Re: Split Table Design File Size (Access 2003/XP)

    If you are nervous about using a library database, then you might create an entirely separate front-end database that only has the import module and connections to the production database and your Access back-end. That way you or your designated user could run the import process when needed and it couldn't be run otherwise.

    Beyond that, have you tried importing all of the tables in the back-end into a new empty database. That often reduces the size of a database by 50% or more, depending on the version of Access you are using. A 260MB Access database would suggest to me that you have either lots of records (say 250,000 or more), or you are storing OLE or image files in your database. Is either the case?
    Wendell

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

    Re: Split Table Design File Size (Access 2003/XP)

    Access works as a back end to dotnet, but the Access UI/IDE itself doesn't handle managed code. In other words, you can't build code in Access that works in dotnet, but you can build code in dotnet that will run against an Access database. Does that make it clearer? I develop in VB.Net against both Access and SQL Server back ends. That part works just fine. But your vendor won't be able to migrate code or objects from Access to a dotnet solution, he'll have to recreate the equivalent objects and code in dotnet.
    Charlotte

Posting Permissions

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