Edit QUERY from VB?!

[briglass]

Hello all!

I have an mdb named data.mdb.

In it, there is a query named MDBox.

If you open this query with MS ACCESS and go into SQL mode, it shows some code.

If I can change this code from VB then my life will be complete.

Here is what it says now:

TRANSFORM Max(Stack.Response) AS MaxOfResponse
SELECT Stack.ID
FROM Stack
WHERE ((Stack.Probe="PRBA0&quot)
GROUP BY Stack.ID
PIVOT [Question] & "MD";


~~

I just need to change that first line to read:

TRANSFORM Max(CInt([Response])) AS MaxofResponse

~~

Can I do this from within VB? If so, that would be very sweet... for me.


Thanks,
Brian G.

 

[LonnieJohnson]

Look at the QueryDef object. prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[B827]

I found it was quite well hidden when I was looking to do this, so in case you can't find what you want try this

qrystring = "TRANSFORM Max(CInt([Response])) AS ..."
Set MyQuery = MyDb.QueryDefs("MDBox&quot
MyQuery.SQL = qrystring
Sandy

 

[Quehay]

It seems like you'd be able to do this before it arrives in the Xtab query, but this should do it:

Dim strSQL as string
Dim Qdf as DAO.QueryDef (requires overt reference in 2000)


strSQL = CurrentDB.QueryDefs("YourQueryName&quot.SQL

(I'm winging the syntax in last line--it will be close to this if not exactly this)


strSQL = Replace(strSQL,"Max(Stack.Response)","Max(CInt(Stack.Response))&quot

DoCmd.RunSQL strSQL

But this won't be compiled, since you're running ad hoc SQL. There's probably a better workaround out there if you describe the entire context at the process level.

 

[briglass]

hey THANKS for all the help!

i'll try these out!