Hi
I'm designing MS SQL Server database with MS Access FE. Database tables are linked to FE using a dsn-file (to avoid the need for managing dsn-connections for all end-users computers), which is a text file containing rows like
[ODBC]
DRIVER=SQL Server
DATABASE=MySqlDB
APP=Microsoft® Windows® Operating System
Trusted_Connection=Yes
SERVER=MySqlServer
The database will be used in different sites over world. Every table in database has a field for site code as part of compound PK. Current FE is meant to work with data for one specific site.
The FE contains a local SetUp table, with field for site code. An unbound text box on unbound main form reads site code from SetUp table. And the main form contains a score of subforms linked to this text box, with different bound forms as source.
This design is working, but linked tables contain data from all sites, and some tables are quite large (mostly data read from various ERP systems, and we have about 15 different sites currently). I'd like to have only data for site determined by SetUp table read into linked tables. Of-course this would be possible when I'd design separate views for every site for every table in DB, and then design separate FE's for every site, but I'm not very fond over this approach (really, I think unless I'll have another solution, I prefer having abundant data linked instead).
So my question is, can someone advice a way to make linked tables in FE to be filtered by site code saved in SetUp table in same FE? I didn't see such possibility in built-in linking wizard!