Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Wed Jun 7 17:47:07 CDT 2006
Jim: I think you're right. Delete and re-create was where I was heading if I want to keep this query in the string of queries the user wants to execute through a macro that they set up. "ahtDoesObjExist " whuzzat? Regards, Rocky Hale, Jim wrote: > I may have missed this suggestion from earlier in the thread, but why not > delete the old query and recreate it with the new criteria? I do it a lot > with passthrough queries but it can be done with regular Access queries as > well. Something like these code snippets (this ex creates a passthrough > query) > > Dim qdfPassthrough As QueryDef, rstPassthrough As Recordset > 'delete old version of query > if ahtDoesObjExist("qryDownload", acQuery) Then DoCmd.DeleteObject > acQuery, "qryDownload" > strPassthrough = "SELECT * from GLTLIB.GLMSL" > ' Create pass-through query > Set qdfPassthrough = db.CreateQueryDef("qryDownload") > qdfPassthrough.Connect = "ODBC;DSN=AS400Prod;" > qdfPassthrough.SQL = strPassthrough > ' Tell Access to wait as long as the query takes > qdfPassthrough.ODBCTimeout = 0 > Set rstPassthrough = qdfPassthrough.OpenRecordset() > qdfPassthrough.ReturnsRecords = True > > query qryDownload will show up in the Database container > > Jim Hale > > -----Original Message----- > From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com] > Sent: Wednesday, June 07, 2006 2:43 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Modifying a query > > > Thought about it? I didn't even know about it! That's the kind of > thing I would have suspected existed. But are you actually modifying > and saving the query or just modifying it for purposes of opening the > recordset? > > Rocky > > > Hale, Jim wrote: > >> Have you thought about using the parameters collection? >> Jim Hale >> >> Set dbs = CurrentDb >> Set qdfTemp = dbs.QueryDefs(strQryname) >> If Not strParameter = "" Then qdfTemp.Parameters(strParameter) = >> strCriteria >> Set rsRecset = qdfTemp.OpenRecordset() >> >> -----Original Message----- >> From: Rocky Smolin - Beach Access Software [mailto:bchacc at san.rr.com] >> Sent: Wednesday, June 07, 2006 1:06 PM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] Modifying a query >> >> >> P.S. However, I'm kind of surprised that there's no object model with >> properties and methods like there is for tables so you could just write >> something like qry.Field(n).Criteria = ">= xxx". Why should that be? >> >> Rocky >> >> >> > > *********************************************************************** > The information transmitted is intended solely for the individual or > entity to which it is addressed and may contain confidential and/or > privileged material. Any review, retransmission, dissemination or > other use of or taking action in reliance upon this information by > persons or entities other than the intended recipient is prohibited. > If you have received this email in error please contact the sender and > delete the material from any computer. As a recipient of this email, > you are responsible for screening its contents and the contents of any > attachments for the presence of viruses. No liability is accepted for > any damages caused by any virus transmitted by this email. > -- Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com