Linked Table Manager Use

[SKV]

I have linked couple of tables (say a,b,c,d- All have same file formats but different data set) from another database to my database and named them as
a = x
b = y

Now I want to change the links ie.
c = x
d = y

how should I do it? I tried deleting the existing links (ie x, y) and reconnect back to c and d and then named them as x, y but this is failing my queries.

Please suggest the best method to do this without impacting the queries

 

[jackd]

I have linked couple of tables (say a,b,c,d- All have same file formats but different data set) from another database to my database and named them as
a = x
b = y

Now I want to change the links ie.
c = x
d = y

how should I do it? I tried deleting the existing links (ie x, y) and reconnect back to c and d and then named them as x, y but this is failing my queries.

Please suggest the best method to do this without impacting the queries

What is actually failing?
Why do you want to rename the linked tables now?
You can rename the link to whatever you like. Seems you created a query and the underlying processes have included the name of the linked tables as you had it when you first linked it.

You can rename the tables to match your query SQL detail.
You could rebuild the query with the current link names.

 

[SKV]

No I am not renaming the linked tables and I only want to change the l link to the tables, how should I do it?

 

[jackd]

No I am not renaming the linked tables and I only want to change the l link to the tables, how should I do it?

I am not following your terminiology.
Can you show the sql of the query that is failing? Also, the name of the linked table?

What is failing? Are you getting an error message?

 

[Joe4]

So are you saying you just want to link a different data source to a particular linked table (for example Excel File A is linked to Access Table A, and you now want to replace Excel File A with Excel File B, so that now Excel File B is linked to Access Table A)?

If so, go into the Linked Table Manager, select your table, then select the "Always prompt for new location" box at the bottom, then click OK. It will then prompt you to browse to the new data source you want that table linked to.

Obviously, for your queries to continue to work properly, the structure of the new linked table has to be the same as the old one (namely, same Field names).

 

[Will_B]

Hi, SKV. I think I understand what you mean: you currently have a query that uses table "x" (which is really table "A" linked from a different database, but which you renamed with the alias "x" in your database).

Now that you have renamed some of these tables aliases, you (understandably) don't want to have to recreate all your queries --- you simply want your queries to refer to the same tables, but to refer to them using the new names you have given them.

Is this correct? You simply want to change your queries to use the new table names?

 

[SKV]

Joe,
you got the point, but the problem is that my new table to be linked and old are in the same database. So when I go "Linked Table Manager" and select "Always prompt for new location" it validates the old connection without giving me the chance to change the table name.

 

[jackd]

Joe,
you got the point, but the problem is that my new table to be linked and old are in the same database. So when I go "Linked Table Manager" and select "Always prompt for new location" it validates the old connection without giving me the chance to change the table name.

Delete the linked table, then create a new link on the table you want.

 

[SKV]

Also, How can I get to know to which of the tables is my linked table attached too. In Linked manager, it just shows the database name but not the Table or query name.

So are you saying you just want to link a different data source to a particular linked table (for example Excel File A is linked to Access Table A, and you now want to replace Excel File A with Excel File B, so that now Excel File B is linked to Access Table A)?

If so, go into the Linked Table Manager, select your table, then select the "Always prompt for new location" box at the bottom, then click OK. It will then prompt you to browse to the new data source you want that table linked to.

Obviously, for your queries to continue to work properly, the structure of the new linked table has to be the same as the old one (namely, same Field names).

Delete the linked table, then create a new link on the table you want.

 

[jackd]

Also, How can I get to know to which of the tables is my linked table attached too. In Linked manager, it just shows the database name but not the Table or query name.

Go to the linked table, click on design, when design opens, right click and select properties

Database and table will show.