Redesign of an old Access Database with Linked Tables

I am redesigning an old database that collected client data by linking
to tables from any one of 50 different outer databases. The databases
(MDB) files are mostly small, less that 2 MB each. Client information
is viewed by selecting a client from a dropdown menu in the front-end
database and linking to 4 key data tables in the client database.

My question is since the front-end and all client databases are
currently less that 100 MB and all databases reside on a local PC (not
networked) wouldn't it be better to incorporate all the client
databases into the front-end? This would make one good size file but
far below the 2 GB limit in Access 2003. It just makes more sense to
me to put everything in one database file. Also there are no plans to
network this database but I can always split it later if needed.
Estimated growth is about 10% a year.

Any help on this would be appreciated. Thanks in advance.

On Fri, 15 Jun 2007 14:40:19 -0700, Ronster wrote:

I am redesigning an old database that collected client data by linking
to tables from any one of 50 different outer databases. The databases
(MDB) files are mostly small, less that 2 MB each. Client information
is viewed by selecting a client from a dropdown menu in the front-end
database and linking to 4 key data tables in the client database.

My question is since the front-end and all client databases are
currently less that 100 MB and all databases reside on a local PC (not
networked) wouldn't it be better to incorporate all the client
databases into the front-end? This would make one good size file but
far below the 2 GB limit in Access 2003. It just makes more sense to
me to put everything in one database file. Also there are no plans to
network this database but I can always split it later if needed.
Estimated growth is about 10% a year.

I'd go even further. If the tables in the client databases are all of the same
structure, I'd import the data into just four tables in the master database.
You may need an additional Clients table, and a ClientID field in one or more
of the four tables, to identify which client's data is in the record.

Storing data (the identity of a client) in a tablename is not good design;
storing that same data in the name of a .mdb file is even worse!

In short - go for it!

John W. Vinson [MVP]

Split it now!
There is never any good justification for not using a split database.
--
Dave Hargis, Microsoft Access MVP


"Ronster" wrote:

I am redesigning an old database that collected client data by linking
to tables from any one of 50 different outer databases. The databases
(MDB) files are mostly small, less that 2 MB each. Client information
is viewed by selecting a client from a dropdown menu in the front-end
database and linking to 4 key data tables in the client database.

My question is since the front-end and all client databases are
currently less that 100 MB and all databases reside on a local PC (not
networked) wouldn't it be better to incorporate all the client
databases into the front-end? This would make one good size file but
far below the 2 GB limit in Access 2003. It just makes more sense to
me to put everything in one database file. Also there are no plans to
network this database but I can always split it later if needed.
Estimated growth is about 10% a year.

Any help on this would be appreciated. Thanks in advance.

John W. Vinson wrote in
:

On Fri, 15 Jun 2007 14:40:19 -0700, Ronster
wrote:

I am redesigning an old database that collected client data by
linking to tables from any one of 50 different outer databases.
The databases (MDB) files are mostly small, less that 2 MB each.
Client information is viewed by selecting a client from a dropdown
menu in the front-end database and linking to 4 key data tables in
the client database.

My question is since the front-end and all client databases are
currently less that 100 MB and all databases reside on a local PC
(not networked) wouldn't it be better to incorporate all the
client databases into the front-end? This would make one good
size file but far below the 2 GB limit in Access 2003. It just
makes more sense to me to put everything in one database file.
Also there are no plans to network this database but I can always
split it later if needed. Estimated growth is about 10% a year.

I'd go even further. If the tables in the client databases are all
of the same structure, I'd import the data into just four tables
in the master database. You may need an additional Clients table,
and a ClientID field in one or more of the four tables, to
identify which client's data is in the record.

Yes, but still maintain a front-end/back-end structure (which is not
what was described).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Hi Dave,

This is so far back in the archives that you may never see it.

Philosophically, I'm on your side of the issue of splitting
applications. For developers of tools for others to use, splitting is
an absolute requirement.

However, not everyone who posts here really aspires to become a
professional developer. For unsophisticated power users of Access,
people who are trying to get a few things done for their own sole use,
there are a couple of justifications for a monolithic implementation:

1. avoiding the need to open the back end to make design changes to
the tables.

2. The automatic backup tools provided by Access don't protect the
BackEnd. MS assumes a monolithic configuration and that's what their
tools support. A lot of things that MS puts into Access are there to
help (lure?) unsophisticated users. MS sells hundreds of copies of
Access to regular office user for each copy of the Developer Tools.
As we insist to newbies that they split we don't always make it a
point to tell them that by so doing they lose the automatic backup of
their data.

We're not really good developers until we're well beyond the levels of
power users and willing to go the extra miles to provide better
solutions. Splitting is then an absolute requirement and it's
incumbent on us to provide tools and practices to assure that the
user's data is protected.

As to OP, I agree: "Split it now"

HTH
--
-Larry-
--

"Klatuu" wrote in message
...
Split it now!
There is never any good justification for not using a split
database.
--
Dave Hargis, Microsoft Access MVP


"Ronster" wrote:

I am redesigning an old database that collected client data by
linking
to tables from any one of 50 different outer databases. The
databases
(MDB) files are mostly small, less that 2 MB each. Client
information
is viewed by selecting a client from a dropdown menu in the
front-end
database and linking to 4 key data tables in the client database.

My question is since the front-end and all client databases are
currently less that 100 MB and all databases reside on a local PC
(not
networked) wouldn't it be better to incorporate all the client
databases into the front-end? This would make one good size file
but
far below the 2 GB limit in Access 2003. It just makes more sense
to
me to put everything in one database file. Also there are no
plans to
network this database but I can always split it later if needed.
Estimated growth is about 10% a year.

Any help on this would be appreciated. Thanks in advance.