Results 1 to 8 of 8
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    How to Dynamically Link Tables for Security

    I have 3 payroll tables with 3 security requirements, say, TableHourly, TableAcct, TableExec. These table definitions are identical. However, only 5 people should be able to read TableAcct and only 2 should be able to read TableExec. Anyone can read TableHourly. The access is based on the operator's login ID. When the application opens, the system sets the access type. Depending on the category, the forms' RecordSource should be one of the three tables. Since there are a number of forms involved, I would prefer not changing the form's RecordSource in each form. I would like all the forms to refer to, say, PayTable. However, when the application opens, I want PayTable to be dynamically linked to TableHourly, TableAcct, or TableExec. I want to set the access limitations within SQL Server so that no one can directly link to an unauthorized table and read it. Any suggestions? Eddie

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Probably need to use TableDefs collection. Try

    Dim tdf As TableDef
    Set tdf = CurrentDb.CreateTableDef("PayTable")
    CurrentDb.TableDefs.Delete tdf
    tdf.SourceTableName = "whichever backend table you want"
    tdf.Connect = "; DATABASE=path\filename.accdb"
    CurrentDb.TableDefs.Append tdf
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    I think your solution assumes an Access Backend. How would I do this with a SQL Server Backend?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I've only manually linked to SQL tables so don't know how code would handle. Did you do web search?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    The key is to fetch the connection string easily. I created a dummy table on SQL Server named "aaaLinkedTable" and used ODBC to link to it. This gives me the necessary connection string

    strConnect = TableConnect("aaaLinkedTable")

    Then you can use the logic provided by June7 above to do the link dynamically. BTW, the .Delete tdf is how to remove the link.

    Eddie

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I tried to use TableConnect. It is not recognized as an intrinsic function. Could you explain it some more?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    June7, this is what I typed in the Immediate Window to confirm...

    ?TableConnect("aaaLinkedTable") note that aaaLinkedTable must be a linked table.

    Oh, also, I have a reference to ADO set as well. That may be necessary.

    Eddie

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Immediate window was the first place I went.

    I have libraries set (in addition to the standard 4):
    Microsoft ActiveX Data Objects 2.8 Library
    Microsoft ADO Ext. 2.8 for DDL and Security
    Microsoft ActiveX Data Objects Recordset 2.8 Library

    "Sub or Function not defined"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Changing a Table Link Dynamically
    By EddieN1 in forum Import/Export Data
    Replies: 3
    Last Post: 09-05-2013, 02:17 PM
  2. how to link 3 tables?
    By handsome1855 in forum Access
    Replies: 1
    Last Post: 03-10-2011, 09:25 AM
  3. security on linked tables
    By wil in forum Security
    Replies: 3
    Last Post: 08-07-2010, 04:54 PM
  4. Replies: 0
    Last Post: 03-26-2008, 08:46 PM
  5. Security and Linked Tables
    By bab133 in forum Security
    Replies: 0
    Last Post: 03-13-2008, 02:11 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