Unanswered: One front end with multiple backend databases?
Is there any way to do this with Access 97?...
Problem: I'm working on an application which I want to use for same structure databases for more than 1 fiscal year. like I've 2 or more .mdb files (exactly same structure) and data for say year 2003-04 and 2004-05 in them. I want to make one front end .mde and want to call these .mdbs from the front end .mde depending on user's choice which year he/she wants to work with. I've created the application with 2 mdbs (backend) and 1 .mde with front end interface. In the front end mde I'm dynamically opening the database, creating its links and then removing links when the user finishes work or if he/she selects the different year. Its seems to work but what is happening is that during the session with one year (for which year the tables are linked) it some times doesn't bring data from these tables and/or mixes up the data from other years database whos links have been removed already.
What I'm doing wrong or what may be causing this? any one can help?
Perhaps the problem is, When you change the link from one DB to another, the record sets are not cleared. So try to open the record sets as fresh record sets so that they don't hold the data of the previous selection.
I've similar routine already coded for my appliation but refreshing links over the network with multiple users already logged in and working it is sometimes terribly slow and time cosuming. But if that is the cause and it is nesessary to do I'll try and check it, but my point is when at the end before closing the front end application I've removed all the links and I re-create them again for the year selected, this shouldn't be necessary or is it? specially when you've to switch back and forth many time from one year to other?
Originally posted by Funkster
I think this may be your problem. If possible and practical you will need to move the FE to the local pc as the multiple users are causing a data conflict.
May be If I restate my question as follows:
is there a way in access to dynamically link/unlink backend databases while running an application. and how?
is there a way in access to dynamically open/close backend databases while running an application. and how?
Actually what you are doing is fine its just that multiple users are accessing your Front End at the same time and this is causing a conflict.
For example if user 1 selects table 2002 the links are created and the user can access the data. User 2 comes along and also requires table 2002. This may work correctly but if user 2 then decides to select table 2003 are the links for table 2002 deleted whilst user 1 is still working with the table.
This is where I feel the conflict occurs. How does the database know which table which user is working on.
If you cannot put the front end on the user's local machine, I recommend that you put the front end and all the back ends in the same location on the server, the same directory and subdirectory. Depending on your server, but this will probably help performance.
Using multiple back ends should not be a problem. As far as dynamically linking to a different back end, I don't know.