I am trying to determine the best strategy for expanding one of our applications to be used by different project sites. Currently, we have an Access 2000 application with a SQL2000 backend, which is hosted on Citrix and accessed through the internet by several remote users. However, these remote offices do not need to see each others data, and as each office is added, it makes any lookup lists and drop down boxes and reports filled with information irrelevant to their project. However, the information should remain in the same database for summary reporting purposes.
I would like to scale this solution with as little impact on the front end application as possible, and try to handle the solution from the SQL Server end. What I envision is at the login screen, the user selects a "Project Location" from a drop down list, and when the application starts up, it only connects to the data in the tables related to that project location. How should this be done? Should I create a ProjectLocation field in every table, then create views on every table that only display data for each office, then link the access front-end to those views? The only way I've every linked Access to SQL is through ODBC linked tables. Or is there is simpler way to accomplish this? Any help would be much appreciated.
I would like to scale this solution with as little impact on the front end application as possible, and try to handle the solution from the SQL Server end. What I envision is at the login screen, the user selects a "Project Location" from a drop down list, and when the application starts up, it only connects to the data in the tables related to that project location. How should this be done? Should I create a ProjectLocation field in every table, then create views on every table that only display data for each office, then link the access front-end to those views? The only way I've every linked Access to SQL is through ODBC linked tables. Or is there is simpler way to accomplish this? Any help would be much appreciated.
