Split Table Design File Size (Access 2003/XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote