Multiple BE -> 1 FE ... Plausible?

[nick404]

My place of business used an access database to manage audits for 4 different areas in the plant. This became quite large very fast and the first designer did not do too fantastic of a job designing it. I have been revamping it for some time now and mgmt wanted me to create a separate db for each area of auditing. (There are now have 5 areas they want to audit). Each area has a different number of 'tasks' that they perform when they audit and select either "good" or "not good" (around 19-22 tasks). Each task is not exactly the same from area to area.



Is it plausible (connectivity, speed, and long term reliability wise) to connect multiple back ends to one central front end? Essentially I want a single UI that has each area and the use selects where they are performing an audit. This then opens the corresponding form and inputs that data to the respective table in the corresponding back end.

[ItsMe]

If you can take your tables and place them into a single ERD then you should, probably, have a single BE. If you can have multiple ERD's that make sense when viewed individually, perhaps you could benefit from multiple BE's.

As for having a single FE with multiple BE's ... maybe. It is hard for me to imagine a scenario where a split desktop DB would have only one FE. For me, one FE equates to a standalone Desktop DB where tables , queries, forms, etc. reside in a single file.

Audit DB's are not easy to create and will, inevitably, break the rules of normalization.

[ranman256]

yes, but the tables cant be the same
You can link them all into the FE, but
db1.Tasks --> links as tTasks
db2.tasks --> links as tTasks2

OR
you could have them all linked as diffenent names
db1.Tasks --> links as tTasksManuf
db2.tasks --> links as tTasksStamping
then the user picks 'Tasks', then the FE would copy THAT dept task to tTAsks , so the code need only look at 1 table, not 4.
(follow?)

but if they have different table names, then you are good.

[nick404]

As for having a single FE with multiple BE's ... maybe. It is hard for me to imagine a scenario where a split desktop DB would have only one FE. For me, one FE equates to a standalone Desktop DB where tables , queries, forms, etc. reside in a single file.

Worded incorrectly, there will be multiple FEs for each local pc using the db. By 1 FE I meant the single one that would be on a users pc linking to multiple different BE's contingent on what audit was being conducted.

[nick404]

Currently I have the base tables, queries, forms, and reports done for each audit area's database. What would be the best way to merge these tables into one backend? There is currently no data in them. Would it be best to create another db consisting of all the tables I need and then link each front end to the respective tables?

[ItsMe]

...Would it be best to create another db consisting of all the tables I need and then link each front end to the respective tables?

I would try to do this. I would also try to consolidate/normalize the tables as best as I could.

[nick404]

Yes that is the plan. Will surely take some thought and time.
Using the db splitter can I specify certain frontend tables to link to different backends?

[ranman256]

or you could link the FE to 1 BE.
The user could choose in a combo ,which one to link to,
then the code would relink all to that 1 db.

[nick404]

Slightly confused as to what you mean by that?

[nick404]

What I did right now on some copies was split it, deleted the tables irrelevant to the area that was the original backend, then used linked table manager to redirect each of the tables to the correct db to serve as the other 'backends'.
I say 'backends' because they do not seem to be recognized by access as a true backend so to speak.

I attempted some mock data entry into each of the separate tables and when I did I got an error message, "0" -BUT the data still transferred to each of the 'backends'.
Is there a better way to go about this?

[ItsMe]

I use the link manager to update existing linked tables with the new path. Another approach is to use code to change the path for existing linked tables. Aside form that, there is a wizard for splitting a DB. Rather than using the wizard to split a DB, I will create a new file and call it the FE. I will open the new FE and use the Import Wizard to link new tables to my new FE file. Then, with the same new FE file, I will import the queries, reports, modules, and forms. When I import objects like forms, I am sure to use the "Advanced" options button to tick or un-tick various advanced options.

[nick404]

That is what I did. Exported all the tables to one db to serve as the BE, and deleted them from the original dbs. Then imported back the ones I wanted into each (now the FE's) as linked tables. Seems to work just fine. Thanks for the help guys