Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: DNS Table Access

    What is the correct way to link a SQL Server table to an Access table, so that the workstation using the Access App has only the rights on the table that are assigned in SQL Server (SELECT, UPDATE, DELETE...)

    I have 2 user groups in Sql Server - ProdPlan and ProdRec.
    ProdPlan has all rights, ProdRec has Select only. I am a member of ProdPlan.

    When I attach tables in Access using DSN, and then ditribute my app to my users, they end up with my rights on the tables. Since they are all members of ProdRec, I really don't want them to open the attached table and do a Select All --> Delete.

    I think I've read every article on the internet related to this topic over the past few days, and haven't found an answer yet.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you connect your application to SQL Server Using Windows Authentication mode, every user is member of a Windows security group (most certainly in an Active Directory if you work with a SQL Server).

    Whatever the way the application is connected to the SQL Server (linked tables or pass-through queries with DAO or their functional corresponding objects in ADO), you need to specify a connection string. In this connection string, you specify the credentials that will be used for the connection. You can then specify that the connection will use the Windows account of the current user (i.e. the one that opened the Windows session on the client machine).

    Example for DAO (pass-through query or linked table):
    Code:
    ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;
    To create a linked table, use:
    Code:
    Sub CreateLinkedTable()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strLocalName As String
        Dim strRemoteName As String
        Dim strConnect As String
        
        strLocalName = "Customers"
        strRemoteName = "Tbl_Customers"
        strConnect = "ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef
        With tdf
            .Name = strLocalName
            .SourceTableName = strRemoteName
            .Connect = strConnect
        End With
        dbs.TableDefs.Append tdf
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    To retrieve data using a pass-through query, use:
    Code:
    Sub CreatePassThroughQuery()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strQueryName As String
        Dim strConnect As String
        Dim strSQL As String
    
        strQueryName = "qry_Customers"
        strConnect = "ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"
        strSQL = "SELECT * FROM Tbl_Customers;"
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef
        With qdf
            .Name = strQueryName
            .Connect = strConnect
            .SQL = strSQL
        End With
        dbs.QueryDefs.Append qdf
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Sub
    Example for ADO:
    Code:
    Driver={SQL Server};Server=Soliman;Database=Sales;Trusted_Connection=yes
    Depending on the data provider you use, there are other possibilities, such as here using SQL Native Client 9.0 OLE DB provider):
    Code:
    Provider=SQLNCLI;Server=Soliman;Database=Sales;Integrated Security=SSPI;
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    5
    Thanks for a great reply Sindho!

    I used your DAO linked table example, and when I open the design view on the linked table, and click on properties (it stores the connection string in the "Description" property), I see this connection string:

    ODBC;DRIVER=SQL Server;SERVER=DW1;APP=Microsoft OfficeXP;WSID=BEERSNOB6400;DATABASE=PROD_PLAN;Trus ted_Connection=Yes;TABLE=dbo.FACILITY_LINES

    So for some reason, Access takes it upon itself to add the WSID to the connection string I generate. So, when I copy this out to my users, it's still going to have my WSID in there instead of theirs. Wouldn't they, therefore, have MY permissions on that table instead of the ones assigned to their workstation?

    I suppose I could have something that runs when the database opens that automatically builds the table links then, but that seems rather messy.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It is true that Access stores extra elements in the connection string of a linked table.

    After creating a linked table the way I explaind in my former post (exactly "as is"), I used the following procedure to check the contents of the connection string of the linked table ([Customers]):
    Code:
    Sub PrintTableConnection()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Customers")
        Debug.Print tdf.Connect
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    This yields (Access runing on the same machine as SQL Server):
    ODBC;DRIVER=SQL Server;SERVER=Soliman;APP=Microsoft Office 2003;WSID=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes
    WSID stands for WorkStation ID, not for User ID.

    If I now copy the database on another machine and open a session with another user name, the connection remains unchanged but the credentials used to connect to the server are those of the current user of this second machine (because of "Trusted_Connection=Yes").

    However, if this is a problem, you don't need to recreate the linked tables when the database is open, you simply can refresh the connection with the RefreshLink property. Here's an example that refreshes every linked table in a database:
    Code:
    Sub RefreshLinkedTables()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim strConnect As String
        
        strConnect = "ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = strConnect
                tdf.RefreshLink
            End If
        Next tdf
        Set dbs = Nothing
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    5
    Excellent! Sounds like that should do the trick.

    A method I was using last week was throwing UID instead of WSID in - that's why I referenced that. When I saw that, I assumed I would still have the same issue.

    One of those things I'd really like to learn more about, but can't find any documentation about this particular topic.

    Thanks for the help!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Beer_Snob View Post
    One of those things I'd really like to learn more about, but can't find any documentation about this particular topic.
    Almost everything on this topic is available in Access help (in the DAO section).
    Quote Originally Posted by Beer_Snob View Post
    Thanks for the help!
    You're welcome!

    Note:

    If you don't want to hard-code the connection string into the code, you can use a seldom used feature of a DAO database (= .mdb file). On such a database, you can create additional properties. Here's what I use to do:
    Code:
    Sub CreateConnectString()
    
    ' This will create a user-defined property on the Database object.
    ' This property (SQLServerConnect) is persistent, unless you delete it.
    '
    ' This sub can be removed from the database once the property is created
    ' (it's a one shot).
    '
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        
        Set dbs = CurrentDb
        Set pty = dbs.CreateProperty
        With pty
            .Name = "SQLServerConnect"
            .Type = dbText
            .Value = "ODBC;DRIVER={SQL Server};SERVER=Soliman;DATABASE=Sales;Trusted_Connection=Yes;"
        End With
        dbs.Properties.Append pty
        Set pty = Nothing
        Set dbs = Nothing
        
    End Sub
    Now the procedure to refresh the connection string of the linked tables becomes:
    Code:
    Sub RefreshLinkedTables()
    
    ' This sub refreshes the connection string of every linked table.
    ' The connection string is fetched from a user-defined database
    ' property named "SQLServerConnect"
    '
        Dim dbs As DAO.Database
        Dim pty As DAO.Property
        Dim tdf As DAO.TableDef
        Dim strConnect As String
        
        Set dbs = CurrentDb
        strConnect = dbs.Properties("SQLServerConnect").Value
        For Each tdf In dbs.TableDefs
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = strConnect
                tdf.RefreshLink
            End If
        Next tdf
    '
    '   Note: Set tdf = Nothing is useless because tdf is already
    '                           set to Nothing when the loop ends.
    '
        Set pty = Nothing
        Set dbs = Nothing
        
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •