Converting Query into SQL (DAO) code

[netsmurph]

I have paramater based querys that i need to able to use through vba as a DAO SQL recordset and am searching for a way to do this without having to re-code each time.

I have looked at the querydef object, and it does not seem suitable.

Has anyone developed any code to do this conversion (although thinking it through, if there are multiple paramaters, and or string or integers, it makes things kinda complex?)

Is it any easier with ADO?

Any help would be appreciated.

Andrew

 

[ZmrAbdulla]

Format QBE SQL at

http://datafast.cjb.net/?GoTo=Downloads.asp

They have a db with some more utilities. (shareware)

________________________________________________________
Zameer Abdulla
Help to find Missing people
Education's purpose is to replace an empty mind with an open one.
~Malcolm S. Forbes~

 

[alvechurchdata]

I've just answered your question in

http://www.tek-tips.com/threadminder.cfm?pid=705

Microsoft: Access Modules (VBA Coding)

Please don't post twice - you'll end up with two contradictory sets of advice.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[netsmurph]

First of all sorry about posting in two forums - was hedging my bets there.

Thanks for your reply ZmrAbdulla, but it is not quite what i am looking for.

Im not sure if it is possible, but i need to be able to convert partameterized access queries (where the partameter is based on a field on a form) into sql code that can be used in an openrecordset procedure.

Working backwards, if i can retrieve the sql string from a query, and add the form based parameters that way, it could be a work around, but i am having trouble getting this from the recordset object:

From the watchwindow, it seems that I need something along the lines of:

rs.parent.QueryDefs.[item1].SQL

Any help on this would be much appreciated.

Andrew

 

[PHV]

Have a look at the Parameters collection of the QueryDef object and the Eval function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[netsmurph]

Many thanks for your post PHV, have taken a look at it, but cannot find what i am looking for.

What I want is to be able to convert a query into a string, evaluate the where clauses on the end of it and concatenate another form based parameter (string) onto the end of it.

Could anyone point me in the right direction.

Many thanks

Andrew