Saving Changes To A Query In VBA

Question

I have a query that is a predecessor to a number of others.  The criteria this query uses ( WHERE clause) are based on values that can be changed via user input and stored in a table.  It's no problem for me to make the changes to the .SQL property as part of saving changes to the criteria.  What doesn't happen is that those changes get saved. 

The process I'm using is:

      Open querydef...

      querydef.sql = "select..."

      querydef.close

I've tried using DoCmd.Save or DoCmd.Save acQuery queryname the first doesn't seem to do anything while the second tells me that the query isn't open regardless if I specify the query by name or the querydef name.  While debugging, If I hit the Save icon after assigning a new value to .SQL it saves correctly. 

Any thoughts on how I can get the revised code to stick would be greatly appreciated.

TIA,

John

 

Answer 1

Hi John,

>> While debugging, If I hit the Save icon after assigning a new value to .SQL it saves correctly

How did you open querydef and what do you mean with “hit the Save icon”? In my option, if you want to change the sql statement, you could try the code below:

Sub changeQuery()
    Dim strSQL As String
    strSQL = "Select * from Table1 where ID=2000"
    CurrentDb.QueryDefs("Query1").SQL = strSQL
End Sub

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi Edward,

First the simple answer.  There is a save icon (picture of a disk) the third from the left on the icon bar in the IDE.  That's what I click, the same as File | Save, etc.

I am working with an existing query so I do the following:

Declare qd as QueryDef

Declare strSQL as String

Set qd = CurrentDB.QueryDefs("ExistingQueryName")

With qd

   strSQL = .SQL

    ......build the replacement SQL statement

   .SQL = strSQL

End With

qd.close

I would have expected that to save on closing but when I then open the query in design mode the change is not there.

Hope that clarifies it.

Thanks for any answers you might have

Best regards,

John

Answer 3

Hi John,

I tested at my side, and I could change sql statement without clicking save icon.

I suggest you try the code below to check the output in immediate window.

Sub changeQuery()
    Dim strSQL As String
    Dim qd As querydef
    Debug.Print CurrentDb.QueryDefs("Query1").SQL
    Set qd = CurrentDb.QueryDefs("Query1")
    'strSQL = "Select * from Table1 where ID=2002"
    'CurrentDb.QueryDefs("Query1").SQL = strSQL
    With qd
        Debug.Print qd.SQL
        .SQL = "Select * from Table1 where ID=2001"
    End With
    Debug.Print CurrentDb.QueryDefs("Query1").SQL
    qd.Close
End Sub

In addition, I suggest you create a new query, and try the code to change the sql statement to check whether this issue still exists.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

What environment are you using to develop your code (Access/Excel/VB...?)?

I'm asking because the Declare statements in your code do not follow Access VBA syntax.

---

Miriam Bizup Access MVP

Answer 5

The criteria this query uses ( WHERE clause) are based on values that can be changed via user input and stored in a table.

Then why not just include the table in the query and reference the columns in question in the WHERE clause?  If the values are in separate columns in one row you can return the Cartesian product of the table restricted to the row in question and the existing result set of the query by not explicitly joining the table.  If they are in the same column position in multiple rows you might need to join the table in some way and return a DISTINCT result set, but on the basis of the information which you have provided it's not possible to be more categorical.

Either way there should be no occasion to amend the query definition At runtime.

---

Ken Sheridan, Stafford, England

Answer 6

Hi Edward,

Thank you for the reply and suggestions. 

Unfortunately, I cannot use a new query as the query in question is an input to other queries.  The code that you show gives me the results that I'd expect. I have done the checking in the immediate window and seen what I expect.  It's when I go to reopen the query that I don't see the change unless I hit a save.

Happy holidays!

Thanks and best regards,

John

Answer 7

Hi Miriam,

The actual code uses Dim statements.  The downside of bouncing between Access and SQL Server.

Best regards,

John

Answer 8

Hi Ken,

Thanks for the suggestion.  The values are stored in a system parameters type table.  In this case, one column indicates units of measure (months or days) and the other the number of units.  This gives me the ability for users to easily go from say, three months in one case to thirty days in another, so there is no way I can generate a Cartesian product since there's nothing to join. The criteria (where clause) become something like:

{columName} < DateAdd({UnitType}, {-NumberOfUnits}, Date())

I hope that makes sense.

Thanks for your kind assistance.

Best regards,

John

 

Answer 9

Hi John,

>> I cannot use a new query as the query in question is an input to other queries.

I suggest you to create a new query is to narrow down this issue whether this issue is related with the specific query or all of your queries have this issues.

>> The downside of bouncing between Access and SQL Server.

Where the query stored, is it in Access or SQL Server? What is your Access version? I know your query did not save, but without your environment, I could not test, it would be helpful if you could share us result of below test.

1. Create a new query, and test with the simple code
2. Test your Access database in a new PC with Access

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 10

Hi Edward,

First the simple answer.  There is a save icon (picture of a disk) the third from the left on the icon bar in the IDE.  That's what I click, the same as File | Save, etc.

I am working with an existing query so I do the following:

Declare qd as QueryDef

Declare strSQL as String

Set qd = CurrentDB.QueryDefs("ExistingQueryName")

With qd

   strSQL = .SQL

    ......build the replacement SQL statement

   .SQL = strSQL

End With

qd.close

I would have expected that to save on closing but when I then open the query in design mode the change is not there.

Hope that clarifies it.

Thanks for any answers you might have

Best regards,

John

Can you verify that the SQL you are assigning to the querydef's .SQL property is syntactically valid?  If you copy the replacement SQL string that you built into the SQL View of a new query and run it, does it work?  IIRC, the querydef won't save if the SQL is not valid.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 11

Thank you everyone for your kind suggestions.  I found my issue and it turns out to be too stupid to even mention here!  The expected behavior is to edit the sql string of the query and have it become part of that query until changed.  It does work that way, when I use the right data and sources.

:{)