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

Posted on 2009-12-30
Last Modified: 2013-11-27

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
Question by:Jimmy_inc
    LVL 4

    Accepted Solution

    There is, but even easier is an Access search & replace program. Why reinvent the wheel?

    I heavily use Rick Fisher's FInd And Replace.

    There are others out there also.

    LVL 84
    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.
    LVL 30

    Assisted Solution

    "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.
    LVL 44

    Assisted Solution

    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!!
    LVL 84
    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.
    LVL 30

    Expert Comment


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

    Using "From a " will not change From all....
    LVL 44

    Expert Comment

    hnsar:  Your stuff is soo cryptic.  What are you getting at?
    LVL 84
    <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

    LVL 84
    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.
    LVL 84
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now