Referential Integrity/Back-End File

[Sprinks]

We've created several "mini-applications" in Access. All use a local
front-end with a server back-end file. Several tables, such as Projects and
Staff, are needed for all of the applications, while others are specific to
each application.

To avoid duplicating the same data in two different places, I moved these
shared tables to their own backend file--Common.mdb. I then realized I
couldn't enforce referential integrity between a Common table and an
application-specific table. I don't see any way around the problem other
than placing all of the tables in the same back-end file.

Currently, the sum total of all backend .mdb files is still small--about 2-3
MB, but wonder if, as the database grows, we'll run into a performance
problem. We will have about 15 concurrent users on a file-server network.

Can anyone advise?

Thank you.
Sprinks

 

[Jeff Boyce]

Sprinks

Not at all uncommon to put both "common" and "specific" tables into a single
back-end. Each front-end can use what it needs to.

If you are using a Microsoft Access/JET back-end, be aware of the
theoretical limit (more recent version) of 2 Gb.

If your total back-end tables take up 10-20 Mb, don't worry.

If you are using (or plan to migrate to) a more robust back-end (e.g.,
SQL-Server, MySQL, Oracle, ...), you will gain capacity and features.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jeff Boyce]

Sprinks

No first-hand experience with MySQL. I had to teach myself enough
SQL-Server to manage the migration of approximately 20 agency databases from
JET back-end to SQL-Server. I believe there is a "free" (?lite) version of
MS SQL-Server available.

Consider posting in a SQL-specific newsgroup for other folk's take on this
topic.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tony Toews]

Currently, the sum total of all backend .mdb files is still small--about 2-3
MB, but wonder if, as the database grows, we'll run into a performance
problem. We will have about 15 concurrent users on a file-server network.

To add to Jeff's response. That will be fine. I had a client with 25
users on a 300 Mb backend. Had because I fired them.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[David W. Fenton]

It was interesting that you mentioned MySQL; someone else
recommended it to me and described it as "free", although I
presume that is for a standalone trial copy rather than a network
version. Nevertheless, judging from their website, it is quite
widely used and growing, and is at an attractive value point.

MySQL is open-source software, so it is, indeed, completely free.
You can download it from MySQL.com, but I see that they are pushing
a supported version of it for $$$. One of the major developers of
MySQL, the one that provides the InnoDB table drivers, was bought by
Oracle recently, and I don't know how long the really good versions
of MySQL will remain free. It may be that there will be a fork in
the project, with a pay version from Oracle and a free version from
the larger developer community, sort of the way it is with
OpenOffice.

We, however, do not have a full-time IS staff member; we bring in
a consultant once a month to do periodic and preventative
maintenance on the network and other troubleshooting. I have
created all of our DB solutions to-date, and learned enough
through experimentation and the experts on this forum to deploy
several multi-user applications with security which have worked
great the past 6 months.

I don't see any reason from what you've said for adding the
difficulties of a server back end.

Now that we have something working, I'm wondering how big a
learning curve and for what benefit migrating to MySQL might be.
Can you provide a broad-brush overview--so much of what is on
their website and published on the Internet is so specific that
I'm having trouble finding the forest through the trees.

I wouldn't say there are great benefits to migrating to MySQL or any
other database server (such as SQL Server) unless you've got
requirements for 24/7 availability, hot backup, bullet-proof
security and the highest level of concurrency. MySQL itself is still
shedding its toy database history -- for instance, it's only with
the InnoDB table format that you get referential integrity (!!!).

Another alternative is PostgreSQL, which is vastly superior in
design and implementation, having all the industrial-strength
features but still nearly matching the blazing performance of MySQL.
But it's substantially harder to use because of that.

I would advise you to ignore this issue for now, but to read up on
it and perhaps download the free programs and experiment with
installing them and using them.

 

[Sprinks]

David,

Thank you for your thoughtful response. Of the benefits you mention, only
better security strikes a chord, although staff phone numbers and billing
rates, while preferred private, aren't state secrets, and we aren't a rich
target for hackers, so I think we're just fine with Access for now.

However, I had already downloaded MySQL for the same reason you suggest--to
keep my ear to the ground, understand its capabilities and limitations, and
do it all *offline* without deadline pressure. Since I'm a full-time
consultant and part-time developer, I think this strategy makes sense.

Thanks again.
Sprinks

 

[Tony Toews]

Thank you for your thoughtful response. Of the benefits you mention, only
better security strikes a chord, although staff phone numbers and billing
rates, while preferred private, aren't state secrets, and we aren't a rich
target for hackers, so I think we're just fine with Access for now.

However, I had already downloaded MySQL for the same reason you suggest--to
keep my ear to the ground, understand its capabilities and limitations, and
do it all *offline* without deadline pressure. Since I'm a full-time
consultant and part-time developer, I think this strategy makes sense.

For better comparability with Access I'd sure look at SQL Server
Express or MSDE/SQL Server 2000. It'd be easier to get problems
solved with those products. Or so I would think.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm