Modifying an Access Pass through Query using Querydefs

[NJC59]

Hi there!

I have looked high and low in order to try and resolve this, but am getting nowhere.

I have a pass through query which I want to add a parameter to the end.

The pass through query is very long and has to crate temporary tables on SQL Server in order to generate the required output.

I do not want to retype or try and paste all of this code into a vba module.

Can I use querydefs or something in the querydefs collection to extract the code into a new query and append my parameter along the way?

A simple way of looking at this could be

querytemplate:-

"set nocount off

Select UWREF
from policy
where UWREF like '"

parameter:- (picked up from a text box on a form)
"ABC123"

additional text to follow:-
"%' set nocount on"

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

This is driving me nuts as I can only seem to find examples where the code is manually entered!

Any help pointers would be greatly appreciated as part of the problem here maybe information overload and I can't believe this that difficult to achieve!

Cheers,

Niven

 

[tehNellie]

I use a simple replace() function in VBA to update passthroughqueries in this manner

ie

DECLARE @uwref varchar(50)

Set @uwref = {placeholdertobereplaced}

etc etc etc

I put this SQL in a table in the Access DB along with the query name. and use a piece of code to take the text from the table, replace the placeholder with the value I want and stick that modified string in the passthrough query:

'take the SQL from the table and replace the value
strSQL = Replace(rs![querySQL], "{placeholdertobereplaced}", "somevalue")
  
'set the querydef object from the table
Set qdf = db.QueryDefs(rs![QueryName])
    
'replace the existing SQL with the new
qdf.SQL = strSQL

it's a bit scrappy compared to doing it properly but it works

 

[mdlueck]

In a nutshell that's all I want to do, but as stated beforehand the real world query has a shed load of other code.

This is driving me nuts as I can only seem to find examples where the code is manually entered!

When working with DAO.QueryDef objects, you can both programmatically create QueryDef instances, or modify existing ones. If you were to update an existing one, simply attach to the correct QueryDef object and provide an updated SQL query string.

Here is a post of mine showing how to use DAO.QueryDef objects to issue a Pass-Through query and download records to a FE temp table.

"Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them"
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

So this example actually creates the DAO.QueryDef objects on the fly. Modify the code to not "delete the DAO.QueryDef if found" but rather open the object and supply an updated SQL query to be executed on the BE DB.

References to opening previously saved DAQ.QueryDef objects:

http://www.logicwurks.com/CodeExamplePages/AQueryDefsFromVBA.html

http://msdn.microsoft.com/en-us/lib...spx#_core_opening_a_previously_saved_querydef

http://stackoverflow.com/questions/2251345/microsoft-access-vba-to-edit-sql-queries#2252689

 

[vbaInet]

TehNellie's method is what the OP is after.

@mdlueck: The OP mentions he doesn't want to type the SQL statement in VBA, but rather modify on-the-fly.

I would use TehNellie's suggestion but rather than saving the SQL statement in a table, simply search for WHERE or LIKE (i.e. case-sensitive search) in the SQL of the querydef, if found replace it with the appropriate part. If not found, concatenate the "WHERE" string to the original querydef. If you have more than one search criteria, then TehNellie's method is the way to go.

 

[mdlueck]

@mdlueck: The OP mentions he doesn't want to type the SQL statement in VBA, but rather modify on-the-fly.

Understood. Thus the links I sited at the end of my post related to modifying existing DAO.QueryDef objects rather than creating them on the fly.

So, was proposing to modify saved QueryDef's updating the SQL query based on the new requirements.

Better?

 

[vbaInet]

Better?

I will need to think about it.