(Re)Connecting to Multiple BE's from Single FE

[RightTurn]

I am trying to implement a split database exclusively in Access with a single FE capable of connecting/reconnecting to different BE mdb files depending on the project No. selected on a combo box in a startup form. All of the BE files will be stored on the same Server, separated by subfolders according to the project (eg GM0123, BOEING0456, etc.,) Also, the table structure for all the BE's shall be similar, but not necessarily identical (ie some projects may have some extra tables depending on their individual requirements).

Here are my main questions on this subject:

1 - Are both DAO and ADO EQUALLY suited for programming this procedure? For the record, I am somewhat competent with DAO, totally ignorant of ADO. So if I can program the code with the former, then it’s all the better.

2 - Where is the best place to save the procedure? I am thinking to just create a button on the Connection form called Reconnect and associating the sub with the button's on Click event. Are there any drawbacks with this approach?

3 - The users would rarely require reconnecting to a new project, thus I want to be able to somehow save the current project for that user upon starting up the FE. What is the most efficient way to save these settings?

FWIW, I have searched for this exact topic on the forums and while I have had luck in finding many posts on linking an access FE to a single BE, there is very little info on connecting to multiple mdb BE files using forms on in the FE.

If any of you DB gurus have covered this topic in some detail on a previous thread, then a link to that thread would be greatly appreciated.

Thanks in advance.

 

[TKnight]

This can be done of course but are you sure you really want to set up the system that way?

It seemed ok to me right up to the point where you stated that the BE structure would not be identical each time. If you switch the BE tables what happens to a form that references a field that no longer exists?

You might be better to create different FE's that link to their respective BE and create a similar dropdown list option that launches the correct FE rather than use a single FE with dynamicly linked tables.

If you think it's best to implement it your way then you could create a table which stores the table structure for each different BE and then when they select from the combo box you could clear all the linked tables in the FE and then create the links by using the append tabledef method (pointing it to the correct BE)

The code below will change any linked table path to the DB specified and you can modify it to append new tabledefs if the table is not currently linked into the FE.

Dim tdf as DAO.Tabledef
Dim dbs as DAO.Database
set dbs = application.currentDB
Set tdf = dbs.TableDefs([I]Table1[/I])
With tdf
    .Connect = ";Database=" & [I]PathToBE[/I]
    .RefreshLink
End With

If you need anything else post back.

HTH

Tom

 

[MarkK]

It seems like a bad idea to me to store data for different jobs in different files in different folders.
The value of a database is that you assert a uniform structure over all your data, and that you can query the whole dataset or a subset of your data with remarkable speed.
The result of breaking up your data by job, or by year, or any other arbitrary distinction you wish to make, is that you unneccessarily complicate the work involved in all storage and retrieval operations, and in my mind this defeats the purpose of creating a relational database system.

 

[grendell2099]

I have done almost this exact sort of thing on a project awhile back and it worked out fine. I say almost because all of my back-ends were identical. Given potentially different table structures, I agree with TKnight... and frankly that sounds like a lot of redundant work.
In my case, I left all of the tables linked to each front end and use a function to redefine the query source based on selection. My users do switch projects from time to time but it isn't something they do constantly.
I used a function to alter the visibility of a few fields/ buttons in forms and such based on a variable stored in the front-end identifying the project selected. This allowed me to do some small customizing with respect to what users see. But basically, I enforced my will that all the groups would have to live with the standardization.

Lagbolt's points are very valid, but let me explain a few points of why I structured the project this way:

1) I had 13 different groups that function almost exactly alike, however they should be segregated from each other for project security (both real and against botard users mucking up someone else's project).

2) With separate back ends, if one group/user manages to muck something up only their "system" goes down. The other groups are not affected. If it was all in one file, I would have to boot everybody in all 13 groups out for the recovery/ repair. (So far, after 3 years there has been no such event).

3) There were only 2-3 users for each group, but I had a total of 25 users. I am not an Access master as many are here, but that total made me uncomfortable- it seems like a lot to be working in one table at a time. Hence the different BE.

At first I was concerned about the time to redefine all the queries, but it only pauses momentarily when the network itself is really slow. I was concerned about all of the linked tables- it does make a bigger front end file, but it hasn't caused any real problems.
The only real negative I have come across with this implementation is that a table change has to be done for each BE- this can get tedious. I have only done this twice in the 3 years the system has been up.