CODEWITHSUNDEEP
sql-serverdatabasems-access
Ronnie Overby
Ronnie Overby

Reputation: 46480

Need to change sql server database name in MS Access with Linked Tables

I used the SSMA to migrate data from an access database to sql server, while still using the user interface in the access database.

Now, I need to rename that sql server database, but I'm not sure how to update the Access file to use the renamed database.

Upvotes: 2

Views: 16628

Answers (2)

Fionnuala
Fionnuala

Reputation: 91376

You can use the Linked Table Manager.

View or refresh links

Use this procedure to view or to refresh links when the structure or location of a linked table has changed. The Linked Table Manager lists the paths to all currently linked tables.

  • Open the database that contains links to tables.
  • On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
  • Select the check box for the tables whose links you want to refresh.
  • Click OK to refresh the links.

Microsoft Access confirms a successful refresh or, if the table wasn't found, displays the Select New Location of dialog box in which you can specify the table's new location. If several selected tables have moved to the new location that you specify, the Linked Table Manager searches that location for all selected tables, and updates all links in one step.

To change the path for a set of linked tables

  • Open the database that contains links to tables.
  • On the Tools menu, point to Database Utilities, and then click Linked Table Manager.
  • Select the Always prompt for new location check box.
  • Select the check box for the tables whose links you want to change, and then click OK.
  • In the Select New Location of dialog box, specify the new location, click Open, and then click OK.

Upvotes: 5

HK1
HK1

Reputation: 12230

You can do this in VBA. This code looks for linked tables that begin with "dbo_" and it removes that part of the name. You'll need to modify it to suit your needs. I recommend your call this from your autoexec macro or an unbound form that starts up with your database.

If you're linking to multiple SQL Server databases then this solution might now work.

Public Sub subChangeLinkedTableNames()

    Dim dbCurr As DAO.Database
    Dim tdfCurr As DAO.TableDef

    Set dbCurr = CurrentDb()

    For Each tdfCurr In dbCurr.TableDefs
        If Len(tdfCurr.Connect) > 0 Then
            If Left(tdfCurr.Name, 4) = "dbo_" Then
                tdfCurr.Name = Replace(tdfCurr.Name, "dbo_", "")
            End If
        End If
    Next


    Set tdfCurr = Nothing
    Set dbCurr = Nothing

End Sub

Upvotes: 0

Related Questions