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
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" on the web.
- To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
- Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
