Changing a query Dynamically (1 Viewer)

grenee · Sep 21, 2018

Good Day All,

This is my existing query created manually and stored in the query pane:

Code:

SELECT Names.Fname, Names.Lname
FROM [Names];

I want this query to change to this, using VBA code, when I press a button:

Code:

SELECT Names.Fname, Names.Lname
FROM [Names]
WHERE (((Names.Fname)="Manilla"));

This is the VBA code which I tried:

Code:

Private Sub TestQry_Click()
Dim curDatabase As DAO.Database
    Dim rst  As DAO.Recordset
   
    Dim strFilter As String
   
    Set curDatabase = CurrentDb
                
            strFilter = "SELECT  Fname,  Lname From [Names] WHERE  Fname =" & "'" & "Manilla" & "'" & " ;"
               
              Debug.Print strFilter
              Set rst = curDatabase.OpenRecordset(strFilter)
        
     Set rst = Nothing
    
End Sub

But Unfortunately, there is no change.
Can anyone show me the code to effect the change that I am expecting

Minty · Sep 21, 2018

Your objective isn't very clear. Are you trying to simply run that query directly within VBA and do something with the recordset?

This line can be simplified to

Code:

   strFilter = "SELECT  Fname,  Lname From [Names] WHERE  Fname ='Manilla' ;"

If Manilla is pulled from the current form, then you would need to concatenate it;

Code:

   strFilter = "SELECT  Fname,  Lname From [Names] WHERE  Fname = '" & Me.YourFormControName & "' ;"

Note that currently you're not doing anything with the recordset, so this VBA won't appear to do anything.

arnelgp · Sep 21, 2018

also

strFilter = "SELECT Fname, Lname From [Names] WHERE Fname =" & "'" & "Manilla" & "'" & " ;"
Me.Recordsource= strFilter

grenee · Sep 21, 2018

Thanks for the simplification. I suspected mine was too long.

My objective at this time is just to have the SQL code in the SQL view changed to the modified query from the VBA code.

arnelgp · Sep 21, 2018

it will chane, add the code to the click event of the button.

to reinstate your query by another button;

me.recordsource="QueryName"

grenee · Sep 21, 2018

Please explain a little further.

The code is already being activated by a button. However when the button is clicked I want to see the code in the query window in the SQL Code changed to the following code

Code:

SELECT Names.Fname, Names.Lname
FROM [Names]
WHERE (((Names.Fname)="Manilla"));

The objective is to learn how to change the query definition by VBA rather than having to open the query window and do so manually

Galaxiom · Sep 21, 2018

This is a job for parameters, not clumsy and inefficient modification of the SQL.

Minty · Sep 21, 2018

You can only do this by opening the query and then changing the querydef() property.
But it won't save the change as far as I know.

You would either have to delete the query then recreate it using the CreateQueryDef or apparently you can use the replace syntax;

Code:

Set qd = db.QueryDefs("YourSavedQry")
qd.SQL = Replace(qd.SQL, "='Manilla';", "='Croydon';")

I've never used the above, but apparently it works... ?

Oh and +1 for Glaxiom's suggestion. Definitely the correct solution.

grenee · Sep 21, 2018

ok.
It would be good to see an example of how the parameter method suggested by Vlaxiom's works.

I would try the delete and replace method as I think I can manage that. Would let you know how it works.

But note that the original query does not have a WHERE condition; so therefore "='Manilla';", "='Croydon';") would not be applicable. The where condition is created by VBA.

Minty · Sep 21, 2018

Okay - but to elaborate an example based on opening a recordset from a saved parameter query, based on controls on your form ;

Code:

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
etc, etc

Set rst = qdf.OpenRecordset

The_Doc_Man · Sep 21, 2018

If you are opening this as a recordset for VBA navigation, you are doing this at the wrong time. Once the recordset (the plain vanilla version) is open, instead of doing a .MoveFirst or .MoveNext, you use a .FindFirst or .FindNext and use "[FName]='Manilla'" as the criterion. Then you change nothing about the query.

grenee · Sep 23, 2018

I think that I did not state my problem properly; so it might have led you off track.

Essentially I wanted to use VBA code to delete an existing query and recreate it. I found the code exactly as I need it on, online. :

Code:

Dim strSql As String 'as already in example
Dim qdf As QueryDef 'as already in example

strSql = "SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID 'as already in example

On Error Resume Next
'Delete the query if it already exists
DoCmd.DeleteObject acQuery, "NewQuery"

Set qdf = CurrentDb.CreateQueryDef("NewQuery", strSql) 'as already in example
DoCmd.OpenQuery qdf.Name 'as already in example

'release memory
qdf.Close 'i changed qdef to qdf here and below
Set qdf = Nothing

It woks well.
Thanks all for your persistence in offer offering help

arnelgp · Sep 23, 2018

you can just simply overwite its content;

with currentdb.querydefs("QueryName")
.Sql= "SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID 'as already in example
end with

June7 · Sep 23, 2018

If you are doing this as only a learning exercise, fine. However, advise not to implement code that routinely changes db design except as a last resort solution. I have done this only once.

Agree with Galaxiom. Apply filter criteria to form or report, no need to modify with QueryDefs.

Or use dynamic parameterized query.

The_Doc_Man · Sep 23, 2018

Be it noted that self-modifying code MIGHT work like this in Access but there are some databases that will not allow this kind of "diddling" with queries.

Therefore, I concur with the others. As a learning experience, OK. As something that might end up in production, avoid this practice like a vampire avoids sunlight.

grenee · Sep 25, 2018

arnelgp said:
you can just simply overwite its content;

with currentdb.querydefs("QueryName")
.Sql= "SELECT * FROM tblT WHERE ID =" & Forms!Form1!txtID 'as already in example
end with

This piece of code is very handy. I shortened my code significantly

grenee · Sep 25, 2018

Your advise is noted and will be applied. Thank you all.

Pat Hartman · Sep 25, 2018

As the others have said - if this is a learning exercize - OK but it is NOT the way you would code an actual application. You would always use a variable in the saved querydef or if you want to use embedded SQL, then you would use the concatenation technique suggested.

You would NEVER, EVER actually modify a saved querydef for this reason.

Also, FYI, when you distribute an application that others will run, you want the option of being able to use the Access Runtime engine so that people do not have to purchase a copy of Access or a version of O365 that includes Access.

Modifying objects is not supported by the Runtime engine. All you can do is to modify data. In a properly defined application, the users can never change anything you built. Think about it. Would MS allow us to change the Excel executable if we didn't like the way they coded a particular function?

Galaxiom · Sep 25, 2018

Pat Hartman said:
Modifying objects is not supported by the Runtime engine. All you can do is to modify data.

While AFAIK, changes to the Project component cannot be edited by the runtime engine, there isn't a limitation on tables and queries.

I have Access queries built on top of Pass Through queries to SQL Server that return data from a table valued function. The only way I know to pass the parameters is by editing the SQL of the query. This works fine on the mde file.

BTW The digital signature on the mde file covers the queries. Discovering this surprised me because I had previously thought that signatures only applied to the code.

It actually gave me even more respect for the digital signature on the mde file security model, functionality that was deprecated when accd* was introduced.

I have not tested what else is covered by the signature but it might well include the local tables and table links too. Maybe I should test it and report to ridders security thread.

isladogs · Sep 25, 2018

Galaxiom
It would indeed be helpful if you could report back exactly what protection adding a digital signature provides for the MDB/MDE format.

It is worth mentioning that, though otherwise more secure, adding a digital signature to an ACCDB/ACCDE file has absolutely no effect. To repeat, it does nothing.
See this article by AWF member sonic8: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=signing-vba-code-access-accdb

Changing a query Dynamically (1 Viewer)

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Super Moderator

AWF VIP

Registered User.

AWF VIP

Immoderate Moderator

Registered User.

..forever waiting... waiting for jellybean!

AWF VIP

Immoderate Moderator

Registered User.

Registered User.

Super Moderator

Super Moderator

MVP / VIP

Similar threads

Users who are viewing this thread