Query with Command object ADO

[roberts]

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

 

[Graham R Seach]

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