Results 1 to 5 of 5
  1. #1
    Anthonyinnz is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2

    How to get current data from a SQL Database into Access when no updates to SQL are allowed.

    Apologies in advance - I do not know much about Access.

    I have a SQL Database that must NOT be able to be updated as it is controlled by another system.
    I want to be able to access the data from three tables ... preferably via a sql Query or View

    In Access I have found out how to create a Table as a copy of my SQL database table (NOT Linked as I have to prevent Updates). However the data in SQL changes daily, and to refresh my Access Tables I have to Delete and recreate them. Is there some way to Replacing the data in my Access Table without Deleting it?



    Also, it would be better for me to Query the SQL tables, or connect to a SQL Database View.

    Is there a way to create a Read Only Access Table linked to a SQL Table or Query or View?

    Thanks for your help

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,654
    have you tried using a passthrough query?

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    705
    Very simple: create in SQL server a view and give the user only read rights on this view and then simply link this view as a new table in access. SQL server has all the tools you need and more.

  4. #4
    Anthonyinnz is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2

    How to link a SQL View from within Access

    Quote Originally Posted by NoellaG View Post
    Very simple: create in SQL server a view and give the user only read rights on this view and then simply link this view as a new table in access. SQL server has all the tools you need and more.

    Sounds good ... I did wonder if linking a View would be ok (instead of linking a table) but I didnt try it because my screen only offered "Tables". But looking closer, I see that my new View is selectable under that Heading.

    So next issue ... what is the best way to make my SQL View Readonly for all Users. Googling suggests that permissions are not adhered to by sysadmin users ... and including a Union is more fail safe, so that any update fails. Any comments?

  5. #5
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    705
    You never ever give your users an admin account!
    Just use the SQL security in a correct way: create a login on server level. This can be a SQL account or ause an already existing windows account or group.
    Click image for larger version. 

Name:	CreateSQL_login.JPG 
Views:	8 
Size:	79.5 KB 
ID:	42998

    After that: create in the database you use, a database user for that login:
    Click image for larger version. 

Name:	CreateSQL_database_user.JPG 
Views:	8 
Size:	131.7 KB 
ID:	42999
    Then you can set the rights, either by making the user a member of an existing group:
    Click image for larger version. 

Name:	CreateSQL_membership.JPG 
Views:	8 
Size:	44.1 KB 
ID:	43000
    Or setting the rights for specific objects:
    Click image for larger version. 

Name:	CreateSQL_rights.JPG 
Views:	8 
Size:	74.7 KB 
ID:	43001

    If you use the security settings as they should be used, they will work as intended.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  2. Replies: 6
    Last Post: 12-08-2014, 08:05 PM
  3. Replies: 7
    Last Post: 11-01-2013, 03:17 PM
  4. data entry not allowed in subform?
    By Lyndy in forum Forms
    Replies: 4
    Last Post: 08-22-2012, 10:28 PM
  5. Inquiry form updates the current record
    By mazzanrol in forum Forms
    Replies: 4
    Last Post: 04-08-2011, 08:35 AM

Tags for this Thread

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