Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Linked Tables - Name Changes

Posted on 2016-08-03
4
Medium Priority
?
53 Views
Last Modified: 2016-08-03
Hi,

I have two access databases. The first (database A), is a reference database that simply stores a bunch of data in various tables. The second database (database B), is more of a "computational" database, that uses Linked Tables from database A and runs several different queries/calculations.

My question is this:
Is there anyway to change a table name in database A and automatically have database B recognize this name change in the linked tables and auto update all the queries in database B appropriately? It seems like the answer is no based on what I'm reading online but I figured I would ask the experts...

Thanks.

Brett
0
Comment
Question by:Bcn78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41741080
You are correct. The answer is no.

But all you need is to link the new table name, then rename the table link to the previous name, thus all queries and code can remain unchanged.

/gustav
0
 

Author Comment

by:Bcn78
ID: 41741098
Got it. That makes sense.

Thanks.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41741108
Not really.  Here is the way I would approach it (you don't happen to have Find & Replace do you?)

1.  Rename the table in the backend (database A)
2.  Open database B in design view
3.  Refresh the link to the backend table with the following in the immediate window.  After each of these lines, hit return to execute each statement.  

set db = currentdb
set tdf = db.tabledefs(localTableName)
tdf.SourceTableName = NewName
tdf.refreshlink
set tdf = nothing
set db = nothing

When this is complete, you should be able to double click on the old table name and open the table.

4.  Then change the name of the table in the front end (Database B)
5.  Then, to replace references to that table in all of your saved queries, you can try the following.  But before you do this, MAKE A BACKUP.  Keep in mind, if your tables OldName is embedded in another table name, (like tbl_Clients and tbl_Clients_Family) this is not going to work.
Public Sub ReplaceTableName(OldName, NewName)

Dim db as dao.database
Dim qdf as dao.querydef
Dim strMsg as string

Set db = currentdb
For each qdf in in db.querydefs

    if instr(qdf.sql, OldName) > 0 then
        strmsg = "Warning: using using replace to change the " & vbcrlf _
               & "text of a query could cause the query to stop" & vbcrlf _
               & "functioning properly!" & vbcrlf & vbcrlf _
               & "Replace '" & OldName & "' with '" & NewName & "'" & vbcrlf _
               & "in query: " & qdf.name & vbcrlf & vbcrlf _
               & qdf.SQL
        if msgbox(strmsg, vbcritical + vbYesNo, "Replace string") = vbNo then
            debug.print qdf.name
        else
            qdf.sql = Replace(qdf.SQL, OldName, NewName)
            qdf.close
        end if
    end if

Next

End Sub

Open in new window

This also will not work for queries which are used as the RecordSource for forms or reports or as the RowSource for combo and list boxes if those queries are not saved queries.

HTH
Dale
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41741126
"But all you need is to link the new table name, then rename the table link to the previous name, thus all queries and code can remain unchanged."

Yes, but if you do it that way, it could be confusing down the road when you go looking for the old table name in Database A, because that will no longer be available.  And people rarely consider that the LocalTableName and the ForeignTableName are not the same.

1.  You could download Rick Fisher's Find & Replace, I believe it has a 30 day trial, that is the best method.  Unfortunately, Rick has not been replying to request for license codes lately, so if you become enamored with his tool, you may not be able to use it for more than 30 days.
2.  Gustav's method is obviously the easiest way to accomplish this, but... (see comments above)
3.  The method I described in my previous post will work for saved queries, with the caveats mentioned in that post.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question