Just wanted to see if anyone could give me any suggestions as to what could be going wrong with the data in a split frontend and backend db.
I have recently split the database into an access 2007 (.acdb) front end and an (.mdb) backend. The reason for doing this was to enable multiple users to have access to the data (which is sotred on a network drive) at the same time.
I currently have 4 different mdb file which contain the exactly the same amount of tables and are structured in the same way, which are stored on the network drive. I know this is not the best way to do things but each of the different mdb files are related to a particular branch and the data needs to be kept seperate for each branch.
Each user is using a localised frontend that enables the user to select which backend they would like to connect the frontend to. It then links all of the tables in the selected backend to the frontend to enable the user to edit the data.
The problem that i am getting occurs when the user opens another instance of the frontend and connects to a different backend at the same time. So the user would have two instances of the frontend each connected to a different mdb backend. Once the second instance if the frontend is linked to the backend that it is going to be using the data in the first frontend changes to the same data as newly opened second instance of frontend has connected to. So to clarrify;
First of all Frontend instance 1 connects to Backend 1 then,
Frontend instance 2 connects to Backend 2.
The data that was then connected to Frontend instance 1 (backend 1) changes to the same data as Frontend instace 2 is using (Backend 2).
Has anybody got any suggestions as to what i need to look into the sort this out? I have tried different ways of linking the tables in the backend to the frontend but been having the same problems. I hope this makes sense to someone and you can give me any advice.
well it is a crazy design. The BE is nothing but tables. Merge the data into a single BE while adding a branch specific ID to each record. Modify your FE to include the selection of Branch - and insert the Branch ID into each new record made. Also modify the FE so they can move between branches within the UI, and so they have no need to be opening a new instance of Access.
the ordinary layperson's demand that data for each branch 'be separate' shouldn't be the rationale for such a bad design. A branch specific ID on each record accomplishes / fulfills that requirement fully....they won't know the difference.
actually I'm not sure; but tend to think that 2 instances of the same app on the same PC is not the recommended install. Analogous to the many posts one sees about multiple people sharing the same FE, which is located on a shared drive. when you are in uncharted territory one can expect a wide variety of behavior problems - some inexplicable - and the only assist you will get is to do a recommended install. You can stop your problem by telling users to only open 1 instance at a time. That's the fix. But of course one can't easily toggle between branch data - so that is why the redesign is needed.