That gave an error 3265 (Item cannot be found in the collection corresponding to the requested name or ordinal.) It makes sense to have tried that, since it's the way to do it with SQL Server. Having gotten that error previously, and being stuck on the adCmdStoredProc/adCmdText area, is where I headed out to ADOX.
Bryant --- In AccessDevelopers@yahoogroups.com, "Tom Oakes" <[EMAIL PROTECTED]> wrote: > Try changing the CommandType to adCmdStoredProc. I usually use that with > SQL Server stored procedures - that may be what you need for a parametized > query in Access. I've never used it outside of SQL Server, but it's worth a > shot. > > Tom Oakes > Personal PC Consultants, Inc. > [EMAIL PROTECTED] > 503.230.0911 (O) > 402.968.6946 (C) > 713.583.7091 (F) > > > > _____ > > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > Sent: Monday, July 18, 2005 7:17 AM > To: AccessDevelopers@yahoogroups.com > Subject: [AccessDevelopers] Re: ADO parameters for action query > > > Hi Tom, > > Thanks for taking time to reply! My problem comes in using a saved > query that has parameters. > > Here's my code ... > > -- Begin -- > > Dim cmd As New ADODB.Command > Dim cnn As New ADODB.Connection > > Set cnn = CurrentProject.Connection > > With cmd > .ActiveConnection = cnn > .CommandType = adCmdText > .CommandText = "qappTestQuery" > .Parameters.Refresh > .Parameters("MyTextParam") = "Test" > .Parameters("MyTextParam2") = "tseT" > .Execute > End With > > -- End -- > > Error received: > -2147217900 > Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', > 'SELECT', or 'UPDATE'. > > I'm guessing this refers to the SQL for the saved query, which has its > first line beginning with "PARAMETERS." > > If it requires a lot of rewrite (appending parameters etc) I may be > ahead to leave it in DAO for the time being, rather than moving it to > ADO. > > I've done some trying with ADOX, and it seems to work ok most of the > time, but in some instances an append query fails silently, with no > ability to troubleshoot (although the same query works when executed > as DAO QDF or by double-clicking the saved query). > > Thanks, > Bryant > > --- In AccessDevelopers@yahoogroups.com, "Tom Oakes" <[EMAIL PROTECTED]> > wrote: > > Bryant, > > > > The ADO command object would be used in code, something like this: > > > > Dim cmd As ADODB.Command > > Dim conn As ADODB.Connection > > Dim strSQL As String > > > > Set conn = Application.CurrentProject.Connection > > Set cmd = New ADODB.Command > > > > strSQL = "UPDATE tblSomeTable SET SomeField='SomeValue' WHERE > > SomeOtherField=12099" > > > > With cmd > > .ActiveConnection = conn > > .CommandType = adCmdText > > .CommandText = strSQL > > .Execute > > End With > > > > > > You can completely bypass your saved query if you want. If you need > to pass > > a parameter, you can create a routine that takes the parameter, > builds your > > SQL string, and fires it via the command object. > > > > Tom Oakes > > Personal PC Consultants, Inc. > > [EMAIL PROTECTED] > > 503.230.0911 (O) > > 402.968.6946 (C) > > 713.583.7091 (F) > > > > > > > > > > _____ > > > > From: AccessDevelopers@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of Bryant > > Sent: Wednesday, July 13, 2005 10:11 PM > > To: AccessDevelopers@yahoogroups.com > > Subject: [AccessDevelopers] ADO parameters for action query > > > > > > I'm having problems working with parameters for action queries, using > > an ADO command object. > > > > All the examples seem to display the ways of creating a recordset -- > > but I don't wish to return rows, only to insert or update records, > > based on parameters being passed to a saved Access query. > > > > Could you please point me towards some examples? > > > > Thanks in advance, > > Bryant > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > > > _____ > > > > YAHOO! GROUPS LINKS > > > > > > > > * Visit your group "AccessDevelopers > > <http://groups.yahoo.com/group/AccessDevelopers> " on the web. > > > > > > * To unsubscribe from this group, send an email to: > > [EMAIL PROTECTED] > > > <mailto:[EMAIL PROTECTED]> > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service > > <http://docs.yahoo.com/info/terms/> . > > > > > > _____ > > > > > > Please zip all files prior to uploading to Files section. > > > > _____ > > YAHOO! GROUPS LINKS > > > > * Visit your group "AccessDevelopers > <http://groups.yahoo.com/group/AccessDevelopers> " on the web. > > > * To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]> > > > * Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service > <http://docs.yahoo.com/info/terms/> . > > > _____ Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
