Merging Multiple Databases

[Tezcatlipoca]

Ok, this is only a very general question, but I'm hoping somebody here can give me some general advice.

Basically, I have three separate database which I've written for my company. They were all written at different times, hence the fact there are three and not one that does three things. The first is used to raise, edit, delete, and track RMA records for returned merchandise. The second is used to raise, edit, and track Training data for people who have undergone my training course. The third is my main database that contains all of my clients' details, plus a log of all calls taken from them. This is the largest and most complex of the databases and has some fairly advanced relationships setup to enabled hundreds of call logs to be linked to a single record of necessary.

Now all three databases have been written using the same kind of structure. They all use popup modal forms to prevent malicious/accidental damage to the database by the staff who use them. They are all locked down and Access cannot be broken into whilst they are running. they all also have their own primary keys.

All these databases work just fine, but I'm now a little tired of each staff member having three shortcuts on their desktops, depending on which database they need to use. what I'd really like to do is somehow merge all three of these databases into one single database, and alter the switchboard accordingly to allow users to access any of the three parts of it.

The problems - as I see them - are: (a) that my main database uses a lot of relationship links to maintain certain records, whilst the other two use none, and, (b) that each database, having been written separately, has records with their own ID keys and autonumbers.

I did toy with the idea of having some kind of function on my main switchboard that would allow me to close the current database being used and open another, all without closing Access, but I haven't been too successful in my tests...

...so, my question is, does anybody have any bright ideas as to how I can go about merging these three, or, at very least, centralising access to all three?

 

[edtab]

One possible approach is to link all the tables from the two minor databases into your main one. Then import all your forms, subforms, queries and reports from the two smaller db's into your main database. Now, you have all the elements you need in one database. You'll need to redesign your switchboard to accomodate these new elements you have combined together in the main database.

 

[Tezcatlipoca]

Hmmm...would that not cause problems given that each of the three tables has records which have unique numbers at their primary keys? So the record with primary key number 1 on database A, for example, is not the same as record 1 with primary key number 1 on database B...

...or are you suggesting a FE/BE split?

 

[edtab]

I don't see why you would have issues relating to the primary keys of the various tables you are linking to, unless they have the same table names as the ones existing in your main db -- which I presume is not the case.

FE/BE? - Yes, in so far as the two minor db's are concerned, you would in effect have the main db as your front end while the other two smaller dbs would hold your other tables. However, in your main db, you'd have all your db elements present (ie. forms,subform,queries, reports,modules). So, you couldn't call this a pure FE/BE split.

You could make it that if you create a 4th db, to act as your front end for all the 3 existing dbs which would host tables only. However, I see no reason to do that as this would mean more work.