Okay, here's a question about a database design I figured out.

To me it sounds like Columbus's egg, but I don't know if it has been used before and more important, I would like to know if more experienced Access users have explicit reasons not to use this design.

I wanted to design a database that was easy to extend with new tables. To allow users to continue working with the existing database, I created a frontend database, however the backend database consisted of separated files. Every file contains a separate table. All files are linked together in the frontend. As, obviously, referential integrity is lacking, I added a custom table to the frontend containing all filenames of the "modular" backend database. With a Visual Basic/SQL instruction, all related records can be deleted in a form. This works flawlessly.

For example:
- frontend database: PATIENT.mdb
- backend databases: HISTORY.mdb, MEDICATION.mdb, EXAMINATION.mdb, etc. etc.

This modular system allows me to easily add new tables (e.g. a table containing labresults - LAB.mdb). I only have to adjust the frontend en instruct my users to exchange the frontend file and to add the new table files.

You may notice that I am pretty enthousiastic about this approach, but I would like to know if this design is feasible and wise to use. If not, could you propose other approaches to create a database project that is easily to extend?

First, let me say that from your post, I cannot know anything about the structure of your database and therefore I an sure that I do not have a clear understanding of what the data requirements are.

With that said, my first thought is: If you going to have a need to add tables, your database is most likely not normalized.

I suppose there could some very unusual circumstances where there could be a legitimate reason to need to add tables, but in my experience I can say that I have had not need to do this. I have had temporary tables that were used only for holding data for a short time and then the data in those table would simple be deleted. I have had situations where I did not get the complete data requirement defined correctly and had to redesign the schema of the database.

Just my thoughts.

how is this any better than just adding a new table to an existing database?

how is this any better than just adding a new table to an existing database?

The advantage in my opinion is that as a developer you can add new tables and update the frontend, while in the meantime the (remote) user can keep on using the database. That is actually the main reason for doing so.

I concur with Mr B on this one. If you need to frequently add tables then the data structure may be flawed.

Moreover it is possible to add tables to the backend while it is in use anyway. I just create the table, export it to the backend then link it.