Redesign of an old Access Database with Linked Tables

[Ronster]

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]

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]

 

[Klatuu]

Split it now!
There is never any good justification for not using a split database.

 

[David W. Fenton]

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

 

[Larry Daugherty]

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