Programatically link SQL Server tables in a new Access 2007 database

Question

I manage an SQL Server 2005 Database and I would like to give read-only access to the necessary tables to a group of 20-30 networked users who are able to use the GUI in MS Access 2007 to write or modify their own queries to the database, with some help.

I would like to distribute an Access database with a single form that would create links to the necessary tables. All of these users are included in a group with read-only permissions to the SQL Server database. I can distribute a dsn file for the connection, but I haven't found a way to programatically create the links to the 50 or so tables they might need, with their network credentials from an otherwise empty Access database.

I found a line of VB code from answer to a similar question onstackoverflow (below), but I was wondering if there was any simpler way than running the modified command once for each of the 50 or so tables.

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=Microsoft ODBC for Oracle;SERVER=myserver;UID=myuser;PWD=mypassword", acTable, "SCHEMA.TABLE", "TABLE", False, True

Answer 1

I just wrote an article last week detailing a way to quickly link all tables in an SQL Database to Access. Here are some Access methods that will help. Read the article for more instructions on using it.

You will need to reference the Microsoft ActiveX Data Objects library.

Sub LinkAllTables(Server As String, database As String, OverwriteIfExists As Boolean)
    'Usage Example (link all tables in database "SQLDB" on SQL Server Instance SQO01, overwriting any existing linked tables.
    'linkalltables "SQL01","SQLDB", true

    'This will also update the link if the underlying table definition has been modified.

    Dim rsTableList As New ADODB.Recordset
    Dim sqlTableList As String

    sqlTableList = "SELECT [name] as tablename FROM sysObjects WHERE (type = 'U')"

    rsTableList.Open sqlTableList, BuildSQLConnectionString(Server, database)
    While Not rsTableList.EOF
        If LinkTable(rsTableList("tableName"), Server, database, rsTableList("tableName"), OverwriteIfExists) Then
            Debug.Print "Linked: " & rsTableList("tableName")
        End If
        rsTableList.MoveNext
    Wend

    rsTableList.Close
    Debug.Print "Done."

End Sub

Function LinkTable(LinkedTableAlias As String, Server As String, database As String, SourceTableName As String, OverwriteIfExists As Boolean)
    'This method will also update the link if the underlying table definition has been modified.

    'The overwrite parameter will cause it to re-map/refresh the link for LinktedTable Alias, but only if it was already a linked table.
    ' it will not overwrite an existing query or local table with the name specified in LinkedTableAlias.

    'Links to a SQL Server table without the need to set up a DSN in the ODBC Console.
    Dim dbsCurrent As database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be appended.
    Set dbsCurrent = CurrentDb()

    'Check for and deal with the scenario ofthe table alias already existing
    If TableNameInUse(LinkedTableAlias) Then

        If (Not OverwriteIfExists) Then
            Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite existing table."
            Exit Function
        End If

        'delete existing table, but only if it is a linked table
        If IsLinkedTable(LinkedTableAlias) Then
            dbsCurrent.TableDefs.Delete LinkedTableAlias
            dbsCurrent.TableDefs.Refresh
        Else
            Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite an existing query or local table."
            Exit Function
        End If
    End If

    'Create a linked table
    Set tdfLinked = dbsCurrent.CreateTableDef(LinkedTableAlias)
    tdfLinked.SourceTableName = SourceTableName
    tdfLinked.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Server & ";DATABASE=" & database & ";TRUSTED_CONNECTION=yes;"

    On Error Resume Next
    dbsCurrent.TableDefs.Append tdfLinked
    If (Err.Number = 3626) Then 'too many indexes on source table for Access
            Err.Clear
            On Error GoTo 0

            If LinkTable(LinkedTableAlias, Server, database, "vw" & SourceTableName, OverwriteIfExists) Then
                Debug.Print "Can't link directly to table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Linked to view '" & "vw" & SourceTableName & "' instead."
                LinkTable = True
            Else
                Debug.Print "Can't link table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Create a view named '" & "vw" & SourceTableName & "' that selects all rows/columns from '" & SourceTableName & "' and try again to circumvent this."
                LinkTable = False
            End If
            Exit Function
    End If
    On Error GoTo 0

    tdfLinked.RefreshLink
    LinkTable = True

End Function

Function BuildSQLConnectionString(Server As String, DBName As String) As String
    BuildSQLConnectionString = "Driver={SQL Server};Server=" & Server & ";Database=" & DBName & ";TRUSTED_CONNECTION=yes;"
End Function

Function TableNameInUse(TableName As String) As Boolean
    'check for local tables, linked tables and queries (they all share the same namespace)
    TableNameInUse = DCount("*", "MSYSObjects", "(Type = 4 or type=1 or type=5) AND [Name]='" & TableName & "'") > 0
End Function

Function IsLinkedTable(TableName As String) As Boolean
    IsLinkedTable = DCount("*", "MSYSObjects", "(Type = 4) AND [Name]='" & TableName & "'") > 0
End Function

Answer 2

If your SQL Server uses Windows security instead of SQL Server security, then you don't have to supply a username/password in your connect string.

Here's the standard way to do this:

1. on your development machine, create a DSN for your SQL Server database.

2. use FILE | GET EXTERNAL DATA | LINK TABLES to link to the tables via ODBC.

3. get Doug Steele's code to convert to DSN-less connect strings.

4. then just distribute the front end as is to your users.

The key to this scenario is using Windows security rather than SQL Server security -- Access silently passes the credentials when it requests the connection via ODBC. This is one reason I'd never use SQL Server security -- too much trouble!

Answer 3

Why not use an Active Data Project in Access?

Linked tables are really only useful if you also need local (unlinked) tables. If you can keep all the tables and views on SQL Server and leave the forms in Access, an ADP will work fine and won't require "linking" any tables manually or via scripting.

In response to Patrick below, if you don't want them mucking around creating queries in your real SQL Server store, create a second SQL Server database that they have rights to create and update queries in, and create VIEWs like the following:

CREATE VIEW mytable AS SELECT * FROM [real database].dbo.mytable

Thus, when you change your master data tables, you only have to make a change to the VIEW in their shared SQL Server database, not a change to each Access database.

Side advantage #1: the users can see each other's queries, thus giving a social aspect of easily sharing good queries.

Side advantage #2: since they are all in one place, you can use SQL Server to detect which user queries will break if you make a change to one of the read-only tables (by searching the view definitions created by Access).

Answer 4

Is there a special reason why you want to re-create the links every time? It would be much simpler to create once the mdb with the linked tables, and distribute that mdb to your users.
You might also want to link SQL Server Views (instead of tables) to Access tables, in order to make sure it's read only, maybe pre-join some tables, and eliminate some fields they do not require.

Answer 5

In addition to what David proposed, you could have a local (client side) table listing the list of tables available through the SQL connection. You could then write a piece of VBA code that will browse this table to establish all corresponding connections:

Dim rsTable as DAO.recordset

set rsTable = currentDb.openRecordset("Tbl_Tables")
if rsTable.EOF and rsTable.BOF then
else
    rsTable.moveFirst 
    Do while not rsTable.EOF
        DoCmd.openDatabase .... 'enumerate here all needed paarmeters with rsTable.fields("tableName") in the string' 
        rsTable.moveNext
    Loop
Endif
rsTable.close
set rsTable = Nothing

This piece of code was written on the fly, so I cannot garantee it will work 'as is'. This code could for example be launched at startup (through the autoexec macro) so that your users will have their links ready when they open their app.

The 'view-only' thing can be easily managed by listing corresponding users (or, if you have a Domain, the corresponding group of users) as 'data readers' on your SQL server.