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

Question

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.

Bump. :) Just kidding, but it would be nice to figure this out. — Ronnie Overby
– Ronnie Overby, Commented Feb 8, 2011 at 16:04

Fionnuala · Accepted Answer · 2013-01-02 22:05:54Z

5

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.

edited Jan 2, 2013 at 22:05

answered Jan 26, 2011 at 19:14

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David-W-Fenton Over a year ago

I wouldn't, because ODBC linked tables don't update well for SQL Server. I would delete all the table links and recreate them from scratch.

HK1 · Accepted Answer · 2011-01-27 04:27:58Z

0

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

answered Jan 27, 2011 at 4:27

HK1

12.3k15 gold badges69 silver badges99 bronze badges

2 Comments

David-W-Fenton Over a year ago

I don't understand how this addresses the question, which I understand as being about the name of the DATABASE, not the names of the tables. That requires a different connect string, not just changing the name of the TableDef.

HK1 Over a year ago

David, if the OP means exactly what he posted then you are correct. I think I misunderstood the question.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related