Modify linked SQL table name in Access 2013

Question

I have a SQL table linked in Access. I'd like to know how to change the linked table object to reference a different table with the same design but another name. For example, I link Table1 and create forms with it, and now need to change it to Table2.

There doesn't seem to be an easy way to do this.

The table Description in Design View contains all the linked database and table information but it's not editable.
Using the Linked Table Manager, I can change the database the table comes from, but the tables in both databases need to have the same name.
I can create a query with Select * From Table1 and change it to Select * From Table2 to switch tables, but I don't want to use a workaround if I don't have to.

Andre · Accepted Answer · 2016-04-27 18:18:29Z

2

Remove the linked table, and use DoCmd.TransferDatabase to recreate the link with different names:

DoCmd.TransferDatabase acLink, "ODBC", your_ODBC_String, acTable, _
    "schema.source_table", "target_table"

You can look up your_ODBC_String from existing linked tables.

Add the StoreLogin:=True parameter if needed.

answered Apr 27, 2016 at 18:18

Andre

27.7k7 gold badges43 silver badges85 bronze badges

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

2 Comments

Andre Over a year ago

Well, in a standard module into a public sub, I suggest. How to use it depends on whether this is a one-time operation, or regularly executed.

jjjjjjjjjjj Over a year ago

It works perfectly, thank you! For anyone who reads this answer in the future, I noticed there's a hierarchy with what a form will take as its RecordSource. It will take a new table with an identical name before it will take a renamed table. For example, if I have an Access table Table1 and rename it to TableA, the form will switch to TableA. However, if I have TableA (originally Table1) and add a new table named Table1, the form will switch to the new Table1.

Collectives™ on Stack Overflow

Modify linked SQL table name in Access 2013

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related