Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Create Dsn-less Links To Each User Table On Sql Server

Posted by: SparkByte Dec 21 2011, 07:04 PM

After some searching I found an interesting blog about linking SQL Server tables in a DB.
Here is the original. http://stackoverflow.com/questions/1569476/programatically-link-sql-server-tables-in-a-new-access-2007-database
I have modified the scripts to be more selective and adaptive. Create a new module and paste the code into the new module.
Most of the changes were to the LinkAllTables function.
1. The SQL script to get the table recordset from SQL Server has been modified to ONLY get USER tables.
a. The old script would get system tables as well as views and link them as well.
2. Changed the While Loop to link all dbo schema tables without the "dbo_" prefix usually added by Access and to
use the schema name as the prefix of all other tables. (ie. hr_Employees)

CODE

Option Compare Database
Function LinkAllTables(Server As Variant, database As Variant, OverwriteIfExists As Boolean)
On Error GoTo Function_End
    '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 [TABLE_SCHEMA] + '.' + [TABLE_NAME] as tableName"
    sqlTableList = sqlTableList + " FROM [INFORMATION_SCHEMA].[TABLES]"
    sqlTableList = sqlTableList + " INNER JOIN [sys].[all_objects]"
    sqlTableList = sqlTableList + " ON [INFORMATION_SCHEMA].[TABLES].TABLE_NAME = [sys].[all_objects].[name]"
    sqlTableList = sqlTableList + " WHERE [sys].[all_objects].[type]=N'U' AND [sys].[all_objects].[is_ms_shipped]<>1"
    
    rsTableList.Open sqlTableList, BuildSQLConnectionString(Server, database)
    Dim arrSchema As Variant
    While Not rsTableList.EOF
        arrSchema = Split(rsTableList("tableName"), ".", , vbTextCompare)
        If LCase(arrSchema(0)) = "dbo" Then
            If LinkTable(arrSchema(1), Server, database, rsTableList("tableName"), OverwriteIfExists) Then
            End If
        Else
            If LinkTable(arrSchema(0) & "_" & arrSchema(1), Server, database, rsTableList("tableName"), OverwriteIfExists) Then
            End If
        End If
       rsTableList.MoveNext
    Wend
Function_End:
    rsTableList.Close
End Function
Function LinkTable(LinkedTableAlias As Variant, Server As Variant, database As Variant, SourceTableName As Variant, 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 of the 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 Variant, DBName As Variant) As String
    BuildSQLConnectionString = "Driver={SQL Server};Server=" & Server & ";Database=" & DBName & ";TRUSTED_CONNECTION=yes;"
End Function
Function TableNameInUse(TableName As Variant) 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 Variant) As Boolean
    IsLinkedTable = DCount("*", "MSYSObjects", "(Type = 4) AND [Name]='" & TableName & "'") > 0
End Function

Posted by: DanielPineault Mar 31 2016, 12:28 PM

For anyone trying to implement the code above, do not forget:

QUOTE
You will need to reference the Microsoft ActiveX Data Objects library
- taken from the reference stackoverflow discussion.