Change Linked Table in VBA

[JoeM]

I have a split Access database (front-end/back-end).



I changed the naming convention of the tables on the back-end, as they had spaces in them.
So, if the table was named "Table One" before, I am changing the name to "Table_One".

Now, if I go into the front-end db, I go to the Linked Table Manager so I can re-point the linked table to the new table name, it gives me an error (I am checking the "Always Prompt for New Location" check box). It says "The Microsoft Access database engine could not find the object 'Table One'...". It doesn't seem to let me browse for the new table name.

Since it appears that I cannot do this manually, I was thinking that maybe I could update this link via VBA. I have been searching, but I have yet to find any code which would update the name of the table I am trying to link to. Does anyone have any code which would work?

I know I can manually add in the linked table as a new linked table, but I prefer to update the existing one, so that all Queries, Reports, and Forms which use this table should hopefully reflect that without having to re-link it all in those objects too.

Thanks

[June7]

Have to delete then recreate the link.

Unless you had the link named Table_One and that was the name used in other objects, will have to change references in Queries, Reports, Forms. I use Rick Fisher's Find & Replace for Access. Saved my sanity more than once. Cost about $50. A freebie called VTools has good reviews.

Also, might have to first remove relationship in Relationships builder.

[John_G]

I know I can manually add in the linked table as a new linked table, but I prefer to update the existing one, so that all Queries, Reports, and Forms which use this table should hopefully reflect that without having to re-link it all in those objects too.

You can do this without having to change the queries, forms etc.

First, delete all the linked tables.
Next, re-link to the tables with the new names.
Then, in the FE, just rename them back to what they were before. That does not change anything in the BE, it only changes how the FE refers to the linked tables.

[JoeM]

I tried using the Rick Fisher's Find and Replace tool, but I cannot seem to get it to work for me. It keeps giving me an error message that says "Windows UAC was enabled (which causes problems). Find and Replace has been installed as an add-in but the Find and Replace Name builder was not installed. Please restart Access."

Not sure what that means or what I can do to correct that...

[June7]

John_G suggests workable alternative, which means your links will still have spaces in their names.

I don't know why you have difficulty installing Find & Replace. I have used it with 2007 and 2010.

[JoeM]

You can do this without having to change the queries, forms etc.

First, delete all the linked tables.
Next, re-link to the tables with the new names.
Then, in the FE, just rename them back to what they were before. That does not change anything in the BE, it only changes how the FE refers to the linked tables.

That didn't work at all. I tried that, and now when I try to open a query, it is still looking for the old name, so I get errors. I expected as much, because if you delete and rebuild, it does not understand that "Table_One" replaced "Table One", so the queries do not know what to look for (what was replaced with what).

Even worse, I tried to see if I can open the query in SQL View, thinking I could just do a Search and Replace on the table name to fix it all quickly. But it doesn't appear to let you open directly in SQL View without going to Design View. And once it does that, it puts aliases on all the unfound fields.

What a mess!

[June7]

Did you rename the new links with the old table name?

[JoeM]

I don't know why you have difficulty installing Find & Replace. I have used it with 2007 and 2010.

The odd thing too is that I am an admin on my own computer, so I am not sure why that would be an issue.

[JoeM]

Did you rename the new links with the old table name?

Aha. That was the issue. I had changed them to the new name I wanted before I changed the names on the back-end and updated the links.
I thought if you usually change the name of your tables, it filters through to the other objects. But I guess that since these are linked tables, these are really just "aliases" and that might not work that way. Is that correct?

The tricky part now is I need to update all the fields names that also have spaces and special characters in them. I have a feeling that is going to be a nightmare. Not sure what the best way of doing that is, especially if I cannot get Rick Fisher's program to work.

BTW, this is all in preparation of moving the back-end tables from Access to SQL.

[June7]

Changing a table or link name should filter through to references in query joins; however, expressions that reference the table name will likely not change.

Need to delete and relink first, then change names. But this still leaves names with spaces, which is what you seem to be trying to eliminate.

See if you have better luck with VTools. http://www.utteraccess.com/wiki/inde..._VTools_AddIns

[JoeM]

Changing a table or link name should filter through to references in query joins; however, expressions that reference the table name will likely not change.

That doesn't appear to be happening like I expected it would.

I tried a simple test. I have a linked table named something like "Table One". I then created a simple query based on this table that simply shows a few fields from this table (no calculated expressions).

Then I went back and changed the name/alias of this link table to "Table_One", and then tried re-opening that test query. I got errors saying it could not find "Table One". Out of curiosity, I tried it out with a native table (not a linked one). And I experienced the same behavior.

I find this behavior baffling, because I was pretty sure that I have had other databases in the past where if I changed the table name, those changes filtered through to queries automatically.

[June7]

There is a db option setting:

Office button > Options > Current Database > Name AutoCorrect Options > Perform name AutoCorrect

[JoeM]

Ah, that makes sense why it works in some cases and not in others.
I am on my way out the door, but I will try that tomorrow when I am back in the office.

Thanks!

[accedeholdings]

Thank you June7, I haven't thought of this yet.

There is a db option setting:

Office button > Options > Current Database > Name AutoCorrect Options > Perform name AutoCorrect