CODEWITHSUNDEEP
sql-servervbams-accessms-access-2013
Marco
Marco

Reputation: 1652

How to change database for linked tables in Access MDB

I have many Access database files that contain linked tables to a SQLServer database. Now the database server has changed and I have to edit the links, possibly without recreating them.

it's possible to do it? I use Access 2013.

Upvotes: 0

Views: 1333

Answers (1)

Thomas G
Thomas G

Reputation: 10206

Yes it is possible to do with VBA but the way you'll do it really depends on how you linked the tables.

Here are 2 example of connection strings I use for SQL server tables

Direct connection:

Driver=SQL Server Native Client 10.0;Server=server_name;Address=server_address,1433;Network=network_name;Database=database_name;Trusted_Connection=Yes

DSN connection (with an entry in the ODBC control panel)

ODBC;Provider=SQLNCLI10;Server=server_name;DSN=name_of_DSN_entry_in_ODBC_control_panel;Database=database_name;Trusted_Connection=Yes

So the first thing to do is to determine how your tables are linked. You can use this code (pay attention to the comments):

Public Sub Check_ODBC_tables()
    Dim tdef As TableDef

    ' First loop on all tables to determine the connection strings

    For Each tdef In CurrentDb.TableDefs

        ' only print the constring if the database name is your old database (adapt accordingly)
        If InStr(tdef.Connect, "Database=old_database_name") Then
            Debug.Print tdef.Connect
        End If
    Next

End Sub

Run this code (F5) and check the output in the immediate window. You'll find how the table are linked and what are the connection strings.

You should prepare a connection string based on that, and adapt in them the database name to use your new DB.

Once you are ready, you can adapt and run the following code that will delete the old table and recreate them with the new database names (some tweaks might be necessary), so go thought it in debug mode !

Public Sub Change_ODBC_tables()

    Dim tDef As TableDef
    Dim tDefNew As TableDef

    Dim strTable As String
    Dim strCOnString As String

    ' examples of constrings - ADAPT!!!!
    strCOnString = "Driver=SQL Server Native Client 10.0;Server=server_name;Address=server_address,1433;Network=network_name;Database=database_name;Trusted_Connection=Yes"
    strCOnString = "ODBC;Provider=SQLNCLI10;Server=server_name;DSN=name_of_DSN_entry_in_ODBC_control_panel;Database=database_name;Trusted_Connection=Yes"

    For Each tDef In CurrentDb.TableDefs

        If InStr(tDef.Connect, "Database=old_database_name") Then

            ' We find a match, store the table name
            strTable = tDef.Name

            ' delete the linked table
            DoCmd.DeleteObject acTable, strTable

            ' recreate the linked table with new DB name
            Set tDef2 = CurrentDB.CreateTableDef(strTable)
            tDef2.Connect = strCOnString
            tDef2.SourceTableName = strTable
            tDef2.Name = strTable
            CurrentDb.TableDefs.Append tDef2

        End If
    Next


End Sub

If you don't fully understand the second piece of code I posted, I urge you to backup your mdb prior to run it because it will delete objects which can cause serious issues.

Upvotes: 2

Related Questions