none
Progressive development of ssma for Access migration RRS feed

  • Question

  • Hi MVPs, 

    A question please about databases originally in Access that need further development.

    The database is migrated with SSMA for Access.  But then further tables are needed, and potentially relationships between previously migrated tables, now already in SQL Server Database.

    I presume the logical answer is continue developing the database in SQL Server, then link up with Access via the DSN ODBC Driver.

    During the process of migration with SSMS for Access, the option of Link to tables is made via a checkbox before the migration starts.  This creates a DSN-less link, where the link details are in the design are of the linked tables.

    Eg. ODBC;DRIVER=SQL Server;SERVER=ABAPLUMBINGPC\SQLEXPRESS;Trusted_Connection=Yes;APP=SSMA;DATABASE=ABA PLUMBING Call Tracker V7;TABLE=dbo.Calls

    Therefore what is the most appropriate way for future development:

    1) Is there a DSN Less function module that can be used to create further DSN-less links to Access (similar as in the migration)

    I believe there are some on UtterAccess.  Would you recommend any particular one or any other official site.

    2) would it be better to make all future links to the SQL Server via a newly created File DSN within the ODBC area

    3) Or can we rename the SQL Server Database temporarily with _original for example.

    Then re-migrate the new tables in Access to the same name SQL Server Database.  In this manner, the SSMA for Access links will have the same server allocation.  And then copy the relevant links manually into the Access front end, along with all the other links.

    And then finally, remove the _original under the SQL Server Database.  And delete the temporarily newly created database for the updates.

    By having indicated the possibilities here, it helps me use the migration process effectively and correctly.

    Thank you kindly in Advance


    Thank you in Advance

    Tuesday, July 24, 2018 3:11 AM

Answers

  • Hi Forum

    It seems the issue was that I had not created the temporary dsn'less link.  I presumed the code I pasted from the UtterAccess forum created them from scratch.

    In  brief, I used SSMA to create a temporary database with the new links.

    I then just copied one of the new links (or in fact I could have used all the newly generated links)

    I did not need to do any of the above change of names of the database.

    I then ran the following function module:

    http://www.utteraccess.com/forum/index.php?showtopic=1979459&hl=sql+server

    with execution:

    test = LinkAllTables("ABAPLUMBINGPC\SQLEXPRESS", "ABA PLUMBING Call Tracker V7", True)

    or if I was to change the function module into a sub

    then simply 

    LinkAllTables("ABAPLUMBINGPC\SQLEXPRESS", "ABA PLUMBING Call Tracker V7", True)

    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

    In any case, the issue was that I had not a dsn'less link prior to running the 'fix' as you tip'ed.

    This can be marked as answered.


    Thank you in Advance


    Saturday, July 28, 2018 9:50 AM

All replies

  • Hi,

    This forum is for developers discussing the developing issues about Microsoft Access. For the issues about SSMA, you may consider discuss it in SQL Server Migration forum.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 27, 2018 7:38 AM
    Moderator
  • Hi Forum

    It seems the issue was that I had not created the temporary dsn'less link.  I presumed the code I pasted from the UtterAccess forum created them from scratch.

    In  brief, I used SSMA to create a temporary database with the new links.

    I then just copied one of the new links (or in fact I could have used all the newly generated links)

    I did not need to do any of the above change of names of the database.

    I then ran the following function module:

    http://www.utteraccess.com/forum/index.php?showtopic=1979459&hl=sql+server

    with execution:

    test = LinkAllTables("ABAPLUMBINGPC\SQLEXPRESS", "ABA PLUMBING Call Tracker V7", True)

    or if I was to change the function module into a sub

    then simply 

    LinkAllTables("ABAPLUMBINGPC\SQLEXPRESS", "ABA PLUMBING Call Tracker V7", True)

    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

    In any case, the issue was that I had not a dsn'less link prior to running the 'fix' as you tip'ed.

    This can be marked as answered.


    Thank you in Advance


    Saturday, July 28, 2018 9:50 AM