1

I used the answer from this post to export my queries to a text file so I could do a find/replace exercise:

Using VBA to export all ms access sql queries to text files

I inherited a database that has object names, banker1, banker2 etc., hard coded and I had to create extras. I exported all of my queries and replaced banker1 with the new names. So far so good.

Is it possible to reverse this process from the single text file generated and load the queries back into Access?

My previous method involved exporting the queries to single text files using Application.SaveAsText, then looping through and doing my find/replace. The issue I encountered using this method is that the file is "formatted", possibly fixed width but not sure, such that some of the names were split across lines and therefore weren't detected by the find/replace. Loading them back in using Application.LoadFromText worked perfectly except I still had to search through queries to find the names that hadn't changed.

Edit: Sample of queries requested.

BNK30-AddChargebacks INSERT INTO BNK30EntryTable ( Entry ) SELECT BNK30SelectChargebacks.Entry FROM BNK30SelectChargebacks WHERE (((BNK30SelectChargebacks.Amount)<>0));

BNK30-AddCredit INSERT INTO BNK30EntryTable ( Entry ) SELECT BNK30EntryQuery.Credit FROM BNK30EntryQuery WHERE (((BNK30EntryQuery.Amt)<>0));

In the above I would be doing a find/replace of BNK30 with BNK31 etc.

Edit 2:

Operation =3
Name ="BNK01SavedReserves"
Option =0
Where ="(((BNK01Select.Reference) Is Null Or (BNK01Select.Reference)=[forms]![BNK01Nav]!"
    "[txtReference]) AND ((BNK01Select.Date) Is Null Or (BNK01Select.Date)=[forms]![B"
    "NK01Form]![StartedTime]))"
Begin InputTables
    Name ="BNK01Select"
End
Begin OutputColumns
    Name ="AssignedTo"

The above is from my original method which works except where the BNK01 is split; just above the Begin InputTables line. Hence trying to switch to exporting the SQL as one big file.

Community
  • 1
  • 1
nathanjw
  • 832
  • 2
  • 13
  • 23
  • @HansUp yes that's correct. I'm replacing banker1 with 11 and 12 to begin with. Possibly adding a few more later on. For example I have a query called Banker1SavedEntry which refers to controls on a form called Banker1Statement. I need that to become Banker12SavedEntry etc. There are roughly 60 objects associated with each banker, not including VBA. – nathanjw Mar 16 '16 at 13:54
  • @HansUp sorry, yes I also need to change the query name. Which wasn't an issue in my original method because as I looped through the querydefs looking for ones that matched banker1 I saved them as the new name each time. The find/replace loop I created in Word to open each file in the folder, carry out the find/replace, and then close the file. – nathanjw Mar 16 '16 at 15:01
  • @HansUp I added a sample of the queries from the file created by exporting the querydef.SQL. The only thing that would change is BNK30 would become BNK31. – nathanjw Mar 16 '16 at 16:06
  • @HansUp that's the bit I'm stuck on. When I do it using my original method, I'm importing a specific file and telling Access what to save it as. When it's one long text file I'm not sure if what I want to do is possible. I guess I need someway to tell Access that it is a new query. – nathanjw Mar 16 '16 at 16:15
  • @HansUp thanks for taking the time to have a look :) – nathanjw Mar 16 '16 at 16:52
  • 1
    @HansUp I didn't know I could do that on the querydef but I shall investigate that lead. Exporting to a text file is a bit of a pain! – nathanjw Mar 16 '16 at 18:12

1 Answers1

2

You can use a VBA procedure to modify both your query names and their SQL as needed. That approach should be much simpler than dumping the query definitions to a text file, doing search and replace in the text file, and then (somehow?) modifying your queries based on the text file changes.

For example, using the procedure below, you can do a "find/replace of BNK30 with BNK31" like this ...

ModifyQueries "BNK30", "BNK31"

However as written, the procedure does not change the queries. It only shows you the changes it would make if you enable the .Name = strNewName and .SQL = strNewSql lines. Please review the output in the Immediate window before enabling those lines. 

Public Sub ModifyQueries(ByVal pFind As String, ByVal pReplace As String)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strNewSql As String
    Dim varNewName As Variant

    Set db = CurrentDb
    For Each qdf In db.QueryDefs
        With qdf
            varNewName = Null
            strNewSql = vbNullString
            If .Name Like "*" & pFind & "*" Then
                varNewName = Replace(.Name, pFind, pReplace)
                Debug.Print "change " & .Name & " to " & varNewName
                '.Name = strNewName
            End If

            If .SQL Like "*" & pFind & "*" Then
                strNewSql = Replace(.SQL, pFind, pReplace)
                Debug.Print Nz(varNewName, .Name) & " SQL: "
                Debug.Print strNewSql
                '.SQL = strNewSql
            End If
        End With
    Next
End Sub

Beware that code has not been thoroughly tested. It is intended only as a starting point; you must test and refine it.

You should add error handling. The procedure will throw an error if/when it attempts to name a query with a name which matches an existing query or table.

Note, I wrote that procedure to rename queries. If you prefer to create new queries instead, revise the code to do this ...

db.CreateQueryDef varNewName, strNewSql

Finally make sure to backup your database before running the "enabled" version of that code. I doubt you need that warning, Nathan, but I cringe at the thought of anyone else inadvertently hosing their queries.

HansUp
  • 95,961
  • 11
  • 77
  • 135