[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

Microsft Access queries need to update all queries with new table name


I need to change many,many queries in my access database, I've had to rename certain tables.. I could manually copy the sql to notepad and replace but surely there is an easier way using DAO or something like that
  • 5
  • 2
  • 2
  • +1
3 Solutions
There is, but even easier is an Access search & replace program. Why reinvent the wheel?

I heavily use Rick Fisher's FInd And Replace. http://www.rickworld.com/products.html

There are others out there also.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No points wanted, but HartCraft has provided you the best suggestion you can get on this one: F&R is the tool to do this with. I own it, along with Total Access Detective, and for things like this F&R is the better choice.
"surely there is an easier way using DAO or something like that"
Try this to do it yourself.
table a (aid)
Select aid  from a;
Want to change it to: Select aid  from b;

Private Sub cmdReplaceSql_Click()
    Dim s As String
    Dim q As QueryDef
    Set q = CurrentDb.QueryDefs("h")
    s = q.sql
    s = Replace(s, "from a", "from b")
    q.sql = s
End Sub

You may loop through all querydefs to achieve a glbal change.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Lets we assume the two tables exist in the same mdb at the time you want to update the SQL of each related query.  On a form you have two list boxes lbxOld, and lbxNew, each indicating all the tables in the mdb.  You make the proper selections of old and new tables, and click a command button cmdUpDateSQL which has this code:

Private Sub cmdUpDateSQL_Click()
Dim strSQL as String, qd as QueryDef, db as Database
For each qd in db.QueryDefs
  strSQL = qdSQL
  If InStr(strSQL,Me!lbxOld)>0 Then
  End If
Next qd
Set qd = Nothing
Set db = Nothing

End Sub

Make sure you backup your mdb before running any code that can make this kind of change.  Double check your selections before clicking that button!!
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Note, however, that hnasr's code would also change this query:

SELECT * FROM allStudents


SELECT * FROM bllStudents

Of course, assuming that you have uniquely named tables, and modified the code to indicate as such, you could certainly use code like this. This does NOT however, take into account inline queries in Forms or Modules.

One should be careful in using find and replace sterategies in all programs.

Using "From a " will not change From all....
hnsar:  Your stuff is soo cryptic.  What are you getting at?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Using "From a " will not change From all....>

Yes it will. I tested it before posting ... I built a query named "Students", with SQL like this:

SELECT * FROM Students

I then altered your code:

 s = Replace(s, "from S", "from b")

The result was this:

SELECT * FROM btudents

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
To take it a step further:

If I have similarly named tables, the Replace syntax in your code would cause additional issues. For example, if I have tables like this:


and I want to replace "tblNames" with "tblMonikers" in my queries, then when I run your code, ANY query that uses any of the 3 tables above would be altered ... queries using tblNamesOfStudents would now point to tblMonikersOfStudents, and queries using tblNamesOfCities would now point to tblMonikersOfCities ... which, of course, would not exist and would break the applicaiton.

This is why I suggest using a product like Find and Replace, which is long established, and makes ONLY the changes you request. Unless the developer has a very good understanding of what the code does, and what the final outcome will be, using code as you suggest could have very negative results. That's not to say it won't work, or that you couldn't tweak the code to be much more specific in what's replaced. But as is, the code suggested could result in some serious consequences.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... got mouse issues today, and the Submit button keeps getting clicked somehow ;)

To resolve this, you could try including spaces around your names;

Replace(s, "FROM A ", "FROM B ")

I'm not sure how replace works in that regard, or whether it would be reliable, but that might get you around the limitation I explain above.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now