Results 1 to 3 of 3
  1. #1
    zgadson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2021
    Posts
    8

    Front End Access to Linked ODBC Table in Back End

    Hello! Could really use some clarification in my understanding of how Access links to data in the context of accessing linked data from a front-end connection.

    Here's what I do understand:
    (1) I have a database with a single objects - one table directly linked to an ODBC data source.
    (2) Because this table is a "linked table", it will always reflect live/current data in the ODBC source.
    (3) Using the "Split Database" function requires a local table in the database to be split.
    (4) If I convert said linked table to a local table it loses its connection to the ODBC source.

    Here's what I want:
    (1) Front End, read-only access to my linked ODBC table in the back end.

    It's such a simple concept. Live data protected in the back end with access to view the data in the front end. But for the life of me I cannot wrap my head around how to link to a linked table. I could pass for a developer or programmer so my complete ineptitude here is infuriating.


    I went through the motions and converted my linked Data Source table to a local table to use the Split Database function and it worked, but as I suspected the new database it created is linked to the local table.

    Source Data File:


    Click image for larger version. 

Name:	Untitled.png 
Views:	12 
Size:	6.8 KB 
ID:	43986

    Front End File:
    Click image for larger version. 

Name:	Untitled2.png 
Views:	12 
Size:	5.9 KB 
ID:	43987

    What am I missing? Why is this so complicated? Please help!

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your original linked table is all you need.

    In order to make it read only, the best route would be to use a query that pulls in all the fields but add the Distinct keyword to the query.
    That will make it read only.

    The other route would be to use a pass through query that did the same, assuming you can run a query on the server that contains the data?
    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 ↓↓

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Safest is to give only read permissions at the source. Second best is to lock down the db so that no one can see the linked table view, otherwise anything you do with locking down via forms can be bypassed by editing the table directly. However, that is as safe as you make it but it will never be 100%.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 8
    Last Post: 08-25-2016, 08:16 PM
  2. Replies: 1
    Last Post: 12-22-2015, 11:04 AM
  3. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  4. Replies: 3
    Last Post: 08-06-2012, 10:38 AM
  5. Replies: 0
    Last Post: 03-08-2012, 03:12 AM

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