Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2017
    Posts
    1,243

    Filtering linked tables!

    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!

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    780
    Possible solution if you don't won't to work with views: create procedures on the SQL server that accept the site as parameter and output the desired dataset filtered by site.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,243
    How I'll do this? The link is initiated from FE, and I can only point a table/view in BE I want to link. Or is there some way to edit created links by some VBA procedure with more finesse?

    And I don't see how passing side code to SQL DB will work with links! I see how this may work when I call a procedure/function on SQL Server which returns a recordset as result, but this will be one-time operation, not a live link!

  4. #4
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    780
    We stopped using Access years ago, but in our user front end (web based) we call on the SQL procedures to populate the forms and do update/insert/deletes. I remember that in the past I did a similar thing for a warehouse management system I wrote.
    Another possibility is that on SQL you create a schema per plant containing all views and objects necessary for the application and at the startup of access you refresh the links so they point at the correct schema.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,924
    Hmm - interesting problem.
    As you mentioned you could create editable views that were filtered to the desired sites. The trick would be a naming convention that allowed you to just link the required views in such a way that the FE was still universal.
    So in your FE opening code you would have a local table that listed all the SQL tables Views with their corresponding LocalName something like

    RemoteTableName LocalTableName SiteRef
    dbo.vw_Table1Site1 Table1 1
    dbo.vw_Table2Site1 Table2 1
    dbo.vw_Table3Site1 Table3 1
    dbo.vw_Table1Site2 Table1 2
    dbo.vw_Table2Site2 Table2 2
    dbo.vw_Table3Site2 Table3 2

    You would then only link the views you needed and the rest of the FE would work regardless of which site ID you used.
    Does that work?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    780
    On SQL side it would make more sense using different schema names and keeping the view names the same instead of renaming the views itself . The more you can set the security per schema.
    so you would just have to remove the schema name from the linked table name:
    RemoteTableName LocalTableName SiteRef
    Site1.vw_Table1 Table1 1
    Site1.vw_Table2 Table2 1
    Site1.vw_Table3 Table3 1
    Site2.vw_Table1 Table1 2
    Site2.vw_Table2 Table2 2
    Site2.vw_Table3 Table3 2
    Last edited by NoellaG; 06-10-2021 at 04:11 AM. Reason: typo

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,924
    @NoellaG - That makes a lot of sense, good idea.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  4. Replies: 1
    Last Post: 09-04-2013, 03:24 PM
  5. Replies: 5
    Last Post: 02-02-2012, 06:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums