Revise query's sql via code

[cpStar]

Is there a way to edit queries programmatically? Most all of the table names have changed (something we had no control over). So now we have to edit each query so it's using the new tables' name. Instead of going into each query & make the changes manually, can it be done via code, as there are a lot of queries? What we need to change is if the table name doesn't start with "tbl", then it needs "dbo" placed in front of the name. I've modified queries before using the ADOX.Catalog & ADODB.Command but that's actually writing the sql in code. What I need to do is loop thru each query in the db (which I have listed in a listbox) & for each one get the sql, make the changes to the table names & then save it. We have Access 2000. We did turn the AutoCorrect on, but that didn't make changes to anything in the Criteria section.

TIA for any suggestions.

[ADezii]

Is there a way to edit queries programmatically?

Yes

cpStar, you're gonna have to stay with me on this one!

1. You will need to create 2 For...Next Nested Loops.
2. The Outer Loop will generate all Table Names that are not prefaced with 'tbl', and are NOT System Tables, namely NOT ?Sys.
3. The Inner Loop will retrieve all Queries that are not Temporary Objects, namely NOT ~sq*.
4. Every qualifying Table is now cross referenced against every qualifying Query.
5. The code now checks to see if the Table Names appear in the Query via its SQL Property and the Instr() Function.
6. If the Table Name(s) does/do appear, its Name(s) will now be prefaced with dbo via the Replace() Function. What this does in effective is to dynamically modify the underlying SQL Statement for the Query/Queries via the SQL Property.
7. The Algorithm is posted below. It is near my bedtime and it has not been thoroughly tested. DO NOT TEST THIS ALGORITHM ON YOUR LIVE DATABASE! I will not accept responsibility for any drastic results that may occur! All that said, here is the code, any questions, feel free to ask.
8. Good Luck and Good Night!

Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
 
For Each tdf In CurrentDb.TableDefs
  If Left$(tdf.Name, 3) <> "tbl" And Mid$(tdf.Name, 2, 3) <> "Sys" Then
    For Each qdf In CurrentDb.QueryDefs
      If Left$(qdf.Name, 3) <> "~sq" Then
        If InStr(qdf.SQL, tdf.Name) > 0 Then
          qdf.SQL = Replace(qdf.SQL, tdf.Name, "dbo" & tdf.Name)
        End If
      End If
    Next
  End If
Next

P.S. - Are you sure that you do not want the qualifying Table Names in the Queries prefaced with 'dbo.'?

[cpStar]

Hi ADezii,

That makes total sense! I work a lot with ADO, that I forget that DAO can sometimes be more helpful ... & easier to use. Thank you so much for your quick response. I'll try that today & will let you know how it goes. If you were asking if we want the tables also renamed, the answer is no as we'll be relinking them.

Thanks again for your help!

[ADezii]

Glad to help, let us know how you make out.

[cpStar]

Hi ADezii,

Sadly to say it didn't work. Close, but the problem is unfortunately they have named each field with the name of the table, so it's not only changing the table name but also each field's name. For ex: let's say a table is named AA then each of the fields start with AA_FName, AA_LName, etc. Someone else had set these tables up, so I have no say in that.

I'm looking into the Find Method so I can be more specific, such as whole words maybe?? I know whole words would look at AA, as in the sql referring to the joins, but do you know if it will also find AA. (AA plus period which is in sql SELECT section indicating the table then the name of the field), but will leave the field AA_LName alone? If it will, then I'm trying to figure out how to use the replace once it finds it. Any thoughts there?

Thanks again for your help!

[ADezii]

Don't give up so easy, you could actually:

1. Parse each SQL Statement for each Query, namely qdf.SQL, placing each segment into an Array using the Split Function (varSQLArray = Split(qdf.SQL, " ")
2. Check each element of the Array:
   1. If an Underscore (_) exists within the Table Name segment, leave it alone since it is actually a Field.
   2. If an Underscore (_) does not exist in the segment with the Table Name, do the normal Replace() Function appending dbo to the Table Name.
3. Reiterate through the newly defined Array and rebuild the SQL String again.
4. Assign the newly rebuilt String to the SQL Property of the Query, namely qdf.SQL = Top Secret Newly Rebuilt SQL String! (LOL)
5. I'd be lying if I said that this would be simple, but if you like I'll attempt to develop the logic as long as you are in no rush.
6. Kindly let me know one way or the other, given the unique constraints, I think your solutions are quite limited.

[cpStar]

Thanks for your quick response. I was testing something like that now ... looping thru the sql ... finding each occurrence of the table name. But then got stuck on how to replace. I came across the Mid Statement & noticed that replaces, but I don't think that will allow me to add add'l characters. I'm not familiar with the Split Function either, but will look into that as well.

I understand your logic, just not sure how to translate that into code (#2 & #3). If you have some time, that would be helpful. In the meantime, I'll check out the Split function.

Thanks again for all your time & patience on this ADezii. I really appreciate it!!

[ADezii]

Bit by bit, I'll see what I can come up with.

[ADezii]

Thanks for your quick response. I was testing something like that now ... looping thru the sql ... finding each occurrence of the table name. But then got stuck on how to replace. I came across the Mid Statement & noticed that replaces, but I don't think that will allow me to add add'l characters. I'm not familiar with the Split Function either, but will look into that as well.

I understand your logic, just not sure how to translate that into code (#2 & #3). If you have some time, that would be helpful. In the meantime, I'll check out the Split function.

Thanks again for all your time & patience on this ADezii. I really appreciate it!!

See what the following code does/does not do for you (ON A TEST DATABASE):

1. Create the following Public Function, actually just Copy and Paste this Function into the General Declarations Section of a Standard Code Module:
   

   Public Function fReBuildSQLString(strSQL As String, strTable As String) As String
   On Error GoTo Err_fBuildSQLString
   Dim varSQLItems As Variant
   Dim intCounter As Integer
   Dim strBuildString As String
    
   'Parse the Components of the SQL Statement into a Variant Array using a
   'Space (" ") as the Delimiter'
   varSQLItems = Split(strSQL, " ")
    
   'Interrogate all the Elements of the Array
   For intCounter = LBound(varSQLItems) To UBound(varSQLItems)
     'Table Name not contained in the Array Element, can be SELECT, ORDER,
     'Field Name, etc., rebuild SQL String
     If InStr(varSQLItems(intCounter), strTable) = 0 Then
       strBuildString = strBuildString & varSQLItems(intCounter) & " "
     'Table Name contained in Element as well as an Underscore (-), as in
     'TableName.TableName_FieldName, extract TableName_
     ElseIf InStr(varSQLItems(intCounter), strTable) > 0 And InStr(varSQLItems(intCounter), "_") > 0 Then
       strBuildString = strBuildString & "dbo" & Left$(varSQLItems(intCounter), _
                        InStr(varSQLItems(intCounter), ".") - 1) & "." & _
                        Mid$(varSQLItems(intCounter), InStr(varSQLItems(intCounter), "_") + 1) & " "
       'strBuildString = strBuildString & varSQLItems(intCounter) & " "
     Else      'a Table Name in the Element with no Underscore (-), preface with dbo
       strBuildString = strBuildString & "dbo" & varSQLItems(intCounter) & " "
     End If
   Next
    
   fReBuildSQLString = Trim(strBuildString)
    
   Exit_Err_fBuildSQLString:
     Exit Function
    
   Err_fBuildSQLString:
     fReBuildSQLString = ""
     Resume Exit_Err_fBuildSQLString
   End Function

2. Change your Base Code to the following:
   

   Dim tdf As DAO.TableDef
   Dim qdf As DAO.QueryDef
    
   For Each tdf In CurrentDb.TableDefs
     If Left$(tdf.Name, 3) <> "tbl" And Mid$(tdf.Name, 2, 3) <> "Sys" Then
       For Each qdf In CurrentDb.QueryDefs
         If Left$(qdf.Name, 3) <> "~sq" Then
           If InStr(qdf.SQL, tdf.Name) > 0 Then
             'Pass the SQL Statement and Table Name to the Function fReBuildSQLString
             'Debug.Print fReBuildSQLString(qdf.SQL, tdf.Name)     'Test
             qdf.SQL = fReBuildSQLString(qdf.SQL, tdf.Name)
           End If
         End If
       Next
     End If
   Next

3. Any questions, feel free to ask.

[cpStar]

Wow, thanks so much for all your time on this!! That’s very impressive!! Unfortunately, after all of this, I just received word, that someone tried the AutoCorrect in 2003 & evidently it changed everything, so they’re going to use that version on that computer to work on this database.

However, that said, I had already started testing what you did & just as an FYI, I noticed a couple of things. When I used the debug.print it did change the table names & left the fields alone … great job!!! But one thing I noticed in the Where clause, for some reason it put the “dbo” outside the parentheses …
ex: “ And dbo(TableName.FieldName between start date & end date) And dbo(TableName.FieldName =20)"
Also when I commented the debug.print line out & used the next line - qdf.sql = ..... – I received an error msg "Invalid sql statement - expected "Delete, Insert, Procedure, Select or Update".

Someday I hope to resolve these 2 issues as I'm sure your code works & it would be great to have on hand as you just never know when you might need something like this.

I feel terrible ADezii, after all the time you spent on this, I won't be able to use it (at least not this time), however, I do want you to know I really appreciated all your help! I learned so much!!! Thank you!!

Have a great day!!

[ADezii]

Not a problem, it has been a learning experience for both of us.