Re: Query with Command object ADO

From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 16:42:45 +1000

Roberts,

Month is a reserved keyword, so enclose it in square brackets. Also, you
need to tell the Command object what type of command to execute.

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim sSQL As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.ConnectionString = strConString
cn.Open

sSQL = "INSERT INTO mat ([month]) VALUES (200412)"

With cmd
    .ActiveConnection = cn
    .CommandText = sSQL
    .CommandType = adCmdUnknown
    .Execute
End With

Set cmd = Nothing
cn.Close
Set cn = Nothing

*** But you can also execute an action query against the Connection object,
without needing a Command object:

Dim cn As ADODB.Connection
Dim sSQL As String

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.ConnectionString = strConString
cn.Open
cn.Execute sSQL

cn.Close
Set cn = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

"roberts" <rsamul@o2.pl> wrote in message
news:%23AdiMRmXEHA.3796@TK2MSFTNGP11.phx.gbl...
> Hello,
> I have a problem with making update query using ADO Command obj.
>
> ...
> text = "insert into mat(month) values(200412)"
> ...
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = con
> cmd.CommandText = text
> cmd.Execute
>
> If I make regular Access query using the same text it updates without
> problem. Also DELETE works well using this Command structure. But if I
want
> update it shows "INSERT INTO syntax error"
> Thanx for any help
> Robert S
>
>

Relevant Pages

  • RE: Vbs: How To Read Variables From An external File?
    ... Execute Command ... > PHP, Perl programer, i know its verey easy to learn VBS, but i dont ... > Dim Command ...
    (microsoft.public.scripting.vbscript)
  • Provider error 80020005 Type mismatch.
    ... Dim PIpk, varTitle, varCitation, varProdType, varYear, varReview, ... 'Set the properties of the command object ... Set cmd = Server.CreateObject ... .Parameters.Append .CreateParameter("varTitle", adVarChar, adParamInput, ...
    (microsoft.public.data.ado)
  • Re: recordset count CONTINUED -- Partial resolution
    ... with the command object. ... Dim cn As ADODB.Connection 'Create a Connection Object ... > Set cmd = New ADODB.Command ... > Set rstTags = Nothing ...
    (microsoft.public.vb.database.ado)
  • Unable to recognize stored procedure param
    ... execute from my application. ... Dim Connection As SqlConnection ... Dim Command As SqlCommand ...
    (microsoft.public.dotnet.languages.vb)
  • A VBScript of Dubious Value. Decide for yourself.
    ... The intent of this script is to execute programs written in scripted ... points to WScript and this program which builds and shells to a command that ... Dim WshShell ...
    (microsoft.public.scripting.vbscript)
Quantcast