"To boldly go where no mdb has gone before"

[ChopinFan]

(I accidentally posted this in the forms forum when I should have posted it here. Oops!)
Hi,

I have a 92 meg back-end mdb file with a 31 meg front-end mdb, both shared over the network. There are really only three users for this database. I'd like to ask for opinions on the following issues:

1. How large, in theory, can the back end file grow before we start to see significant issues arising?
2. What specifically would those issues be (aside from the obvious pulling-an-elephant-over-the-network issue)?
3. Would migrating to Access 2003 help solve any of these issues? (I'm sure A2K3 is still not a true client-server db)
4. If we must stay in A2K (I want to go to SQL Server, but we may not be able to for 2-3 more years...yikes!) What would I need to do to allow for the back-end to reach, say, 175, or even 200 megs (blood-curdling scream)? Is replication the answer?

If anyone is aware of any Microsoft articles addressing the limitations of mdb files, I'd appreciate the link.

Thanks!

 

[M8KWR]

see if this link helps..

http://www.microsoft-accesssolutions.co.uk/access_specifications.htm

 

[hneal98]

All I can say, is with Access, getting to that size of file means you are probably already hurting on performance. I would seriously try to convince the powers that be to convert to SQL Server back end, either that or do some archiving.

 

[jrbarnett]

1. As far as I know, its about 2Gb.
2. None I am aware of, other than that you have identified, affecting both database and general network performance.
3. Access 2003 is not a true client/server DB as you request. I don't think it will solve your problem.
You can check to ensure that the network is running optimally with the equipment you have got, but that is off topic for an Access forum.
4. Nothing for that size, but remember that Access 2000, XP and 2003 include a product called MSDE, which is a cut down version of SQL Server. You may like to consider using that if you only have 3 concurrent users.

John

 

[MoLaker]

If feasible, you might consider placing the front-end on each client rather than sharing over a network. This would reduce network traffic to data grabs only and not load the network with general application traffic.

Another bit of help is to place tables which are not normally updated (such as reference tables) in the front-end rather than the back-end. This too can reduce network traffic.

 

[ChopinFan]

Thanks everyone for the good information. I find I'm running into some issues of theory vs. practice when it comes to these things. From reading Microsoft's own materials, it would seem an mdb can almost walk on water, but I know otherwise.

I am curious to hear more about the effects of sharing the front end over the network. I was under the impression that the front end simply loads up once for each user and, subsequently, the only network traffic would be data grabs. How much would network traffic be reduced by having the front-end local to the client rather than shared?

 

[hneal98]

From my experience, it is better to put the frontend on the users computer as mentioned by MoLaker above. It is a bit of a pain when you have to make any changes to it though.

 

[ChopinFan]

Thanks, hneal. Unfortunately, the database is constantly changing. They are always (and I mean always!) wanting new reports, changes to existing reports, new this, new that, ad infinitum. As you can imagine, it's easiest just to share it over the network, but if saving it locally and replacing it often will give the db a new lease on life, it looks as though I won't have a choice.

 

[hneal98]

Can you archive any of the data in the backend? Just create another backend that would hold the older data and then give the user an option to select old data or new.

That might be another option. And I know what you mean about the constant requests. When I worked for a different Health care company, I was in the process of creating an application for them to use. I say in the process because I never finished it due to constant change reqeusts.

Anyway, hope that helps.

 

[ChopinFan]

Archiving would be an option if I knew of a simple way to give them "trend" reports and query through all historical data at once. (Build a union query as a sub query? Never tried it.) Thoughts?

 

[hneal98]

That is one major drawback. Do they always go all the way to the beginning of the data? Sometimes companies just want to look at last three years or last year, etc.

I haven't done that kind of thing for a few years now. I would imagine that you could create temp tables to pull all the data into that you would need, however, that would defeat the purpose of archiving.

If they need all the data all at once, it would not do any good, but if you can get them to just pull a certain period at a time, that would make it easier. Then you could give them a selection and pull from one set or the other based on their selection.

Probably not much help, but with Access, it is always an issue.

 

[MichaelRed]

re the 'constant' updating of the front end, there are several to numerous threads discussing this, including a few variations on the mechanics. Some favor the bbrute force approach, just determining that there is actually a newer/update available and copying the whole thing. Others 'inspect' each object, and only relpace the parts required. Each approach has its advantages, but I would think that 30Meg would be worth the piecemeal approach. One of these includes the use of MSysObjects, so htat could be a keyword to use for the search.

re archivinig, I would 'discuss' the history / trend needs and uses with the mgmt. Form this discussion, one should be able to arrive at a reasonable division of current vs. historical requirements. Trending could be a seperate FE / activity, with the BE data for it updated periodically from the current production db (weekly, monthly, quarterly, etc). This couls pare down the size (therefore the troubles) with both FE & BE of BOTH the current and trend applications, so benefit all sides. A BIG issue is splitting the data is wheather (and for what period(s) "re-stating" (e.g. changing historical information) could / would / might occur. Obviously, if history can (is) be changed from the beginnning of time, and it is done often, splitting the data is a MUCH more serious issue.





MichaelRed

 

[ChopinFan]

All good information. They do indeed want to see trends from "the beginning of time", although I'm not concerned about old information needing to be corrected or modified (thank goodness). I think my best option is to do two things: First, make a separate BE, as Michael said, to provide trend information. Second, I'll seriously consider pulling all look-up tables into the FE and installing it locally to each machine, further reducing network traffic and BE file size. It isn't the best solution (like SQL Server would be but oh well.

Thanks again!

 

[hneal98]

Good info, MichaelRed. And wish you the best, ChopinFan.