Examples Of Larger Systems Built On MDB Backends

I've been asked to build a system that is considerably larger than
anything that I've done before and I'm trying to determine if an mdb
backend is up to it. The system is not mission critical to the point
where the loss of a day's data would be a catastrophe.

It will probably be running on terminal server. If I understand
terminal server correctly this will be slightly more robust than a
frontend/backend setup where each user has the frontend on their
machine. I'm assuming that if the backend and each user's frontend
profile is on the server all the problems that can arise from faulty
network connections etc will be removed.

I'm interested to hear of examples of "large" (a very subjective term)
database systems that have been built by contributors to this forum
that work reliably on an mdb backend.

Any help is appreciated.

Re: Examples Of Larger Systems Built On MDB Backends

On Tue, 8 Jan 2008 16:32:24 -0800 (PST), Wayne
wrote:

Let me turn that around and let you give a synopsis of what your app
would do, and how many forms and reports you expect in the final
product. Also how many CONCURRENT users.

-Tom.



>I've been asked to build a system that is considerably larger than
>anything that I've done before and I'm trying to determine if an mdb
>backend is up to it. The system is not mission critical to the point
>where the loss of a day's data would be a catastrophe.
>
>It will probably be running on terminal server. If I understand
>terminal server correctly this will be slightly more robust than a
>frontend/backend setup where each user has the frontend on their
>machine. I'm assuming that if the backend and each user's frontend
>profile is on the server all the problems that can arise from faulty
>network connections etc will be removed.
>
>I'm interested to hear of examples of "large" (a very subjective term)
>database systems that have been built by contributors to this forum
>that work reliably on an mdb backend.
>
>Any help is appreciated.

Re: Examples Of Larger Systems Built On MDB Backends

Per Wayne:
>I've been asked to build a system that is considerably larger than
>anything that I've done before

Define "larger".
--
PeteCresswell

Re: Examples Of Larger Systems Built On MDB Backends

Thanks Tom and Pete for your replies.

The database would handle production data from pit to port including
tonnages mined, preparation plant data and shipping data.

In very rough terms the database would have about 20 tables, each
table would have no more than a dozen fields. Data entry would be
about 6 records per day into a few of the tables and less than that
for the rest. It would have a about 30 forms, 30 or 40 queries and
about 25 reports. Concurrent users would be less than 6.

I know the above is rough, but I was only approached about the job
today and I still don't have all of the information to get a more
concise handle on what exactly is required. I guess I initially was
concerned because I haven't done anything this big before. My concerns
probably aren't justified. You guys would be the best judge of that.
I'm hoping that an mdb backend will suffice because it's all I've done
before and setting it up is very simple.

I probably should add that I have about 20 databases in production
environments and they have rarely/never given any problems. The seem
to tick along year after year with no problems. They are all mdb
backends sitting on the server, with each user having their own mde.
The databases manage tasks like safety incident reporting, training
records, document management, environmental reporting etc.

Re: Examples Of Larger Systems Built On MDB Backends

Wayne wrote:
> Thanks Tom and Pete for your replies.
>
> The database would handle production data from pit to port including
> tonnages mined, preparation plant data and shipping data.
>
> In very rough terms the database would have about 20 tables, each
> table would have no more than a dozen fields. Data entry would be
> about 6 records per day into a few of the tables and less than that
> for the rest. It would have a about 30 forms, 30 or 40 queries and
> about 25 reports. Concurrent users would be less than 6.

That does not meet any reasonable definition of "larger". You should be fine.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Examples Of Larger Systems Built On MDB Backends

On Tue, 8 Jan 2008 19:08:25 -0800 (PST), Wayne
wrote:

Thanks, that helps frame it.
What you're describing is definitely within the design specs of
Access/Jet. With the experience you already have you should be able to
pull this one off. Pay enough attention to database design, proper
unique indexes, required fields, relationships. Pay enough attention
to defining the scope of work - what exactly this app should do and
not do, perhaps a prototype of some critical forms is in order.

Good luck. Come back for more specific questions down the road.

-Tom.


>Thanks Tom and Pete for your replies.
>
>The database would handle production data from pit to port including
>tonnages mined, preparation plant data and shipping data.
>
>In very rough terms the database would have about 20 tables, each
>table would have no more than a dozen fields. Data entry would be
>about 6 records per day into a few of the tables and less than that
>for the rest. It would have a about 30 forms, 30 or 40 queries and
>about 25 reports. Concurrent users would be less than 6.
>
>I know the above is rough, but I was only approached about the job
>today and I still don't have all of the information to get a more
>concise handle on what exactly is required. I guess I initially was
>concerned because I haven't done anything this big before. My concerns
>probably aren't justified. You guys would be the best judge of that.
>I'm hoping that an mdb backend will suffice because it's all I've done
>before and setting it up is very simple.
>
>I probably should add that I have about 20 databases in production
>environments and they have rarely/never given any problems. The seem
>to tick along year after year with no problems. They are all mdb
>backends sitting on the server, with each user having their own mde.
>The databases manage tasks like safety incident reporting, training
>records, document management, environmental reporting etc.

Re: Examples Of Larger Systems Built On MDB Backends

"Wayne" wrote in message
news:0a859866-9033-41e5-aae4-e45def519b3f@s19g2000prg.google groups.com...

> It will probably be running on terminal server. If I understand
> terminal server correctly this will be slightly more robust than a
> frontend/backend setup where each user has the frontend on their
> machine. I'm assuming that if the backend and each user's frontend
> profile is on the server all the problems that can arise from faulty
> network connections etc will be removed.
>

Each user will get a copy of the front-end in a terminal server enviroment.
Also each Access user needs be treated as a power user when sizing the
server.

Re: Examples Of Larger Systems Built On MDB Backends

Per Wayne:
>In very rough terms the database would have about 20 tables, each
>table would have no more than a dozen fields. Data entry would be
>about 6 records per day into a few of the tables and less than that
>for the rest. It would have a about 30 forms, 30 or 40 queries and
>about 25 reports. Concurrent users would be less than 6.

I'd expect no problems with less than 6 concurrent users.

As far as number of tables/fields/forms/queries/reports go, I'd
call that a relatively small application.

The only MDB-specific issues that I can see are:
------------------------------------------------------------ -----
1) Unexpected LAN problems. Flaky server, bad NIC, whatever....
But I'm guessing you're in a position to quickly move the back
end to another server, so that's not the potential problem it
would be in a large organization where they have everything
totally locked down and you have to go to committees and wait
for months for a move.

2) IT deploying a later version of MS Access. Minor nuisance,
but it can break an application until somebody makes the
needed adjustments. Again, size of organization...
------------------------------------------------------------ -----
--
PeteCresswell