I can use this statement cmd.ExecuteNonQuery in a CMD to exceute a SQL?
Or i can integrate in CMD
this CMD.Execute SQL, , adCmdText + adExecuteNoRecords
????
cmd.ExecuteNonQuery IN VBA FOR EXCEL
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
ExecuteNonQuery is valid in VB.NET. It won't work in VBA for Excel.
If you create an ADO Command object CMD, you can use
CMD.Execute SQL, , adCmdText + adExecuteNoRecords
If you create an ADO Command object CMD, you can use
CMD.Execute SQL, , adCmdText + adExecuteNoRecords
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
HansV wrote:ExecuteNonQuery is valid in VB.NET. It won't work in VBA for Excel.
If you create an ADO Command object CMD, you can use
CMD.Execute SQL, , adCmdText + adExecuteNoRecords
OK TKS!
but if i put into , , my long variable LNG after the execution have LNG=0!!!!!
i'm sure the query update a records, sure!
is the parameter , , the count of record affect? or not?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
Oh wait - it doesn't work this way with a Command object, but with a Connection object.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
HansV wrote:Oh wait - it doesn't work this way with a Command object, but with a Connection object.
yes, only with Command object...(?)
instead with the tipical:
SQL = "INSERT INTO ....."
CONN.Execute SQL, LNG, 129
work perfect and return the correct number of recordset found!
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
With a command object, you could use
CMD.CommandText = SQL
CMD.Execute LNG, , adCmdText + adExecuteNoRecords
CMD.CommandText = SQL
CMD.Execute LNG, , adCmdText + adExecuteNoRecords
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
HansV wrote:With a command object, you could use
CMD.CommandText = SQL
CMD.Execute LNG, , adCmdText + adExecuteNoRecords
Ahhhh ....
But Not between , , but near execute stattement
Taths si news for me...
Tks.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: cmd.ExecuteNonQuery IN VBA FOR EXCEL
The syntax of the Execute method of the Connection object is:
connection.Execute CommandText, RecordsAffected, Options
And the syntax of the Execute method of the Command object is:
command.Execute RecordsAffected, Parameters, Options
Notice that RecordsAffected is the second argument of connection.Execute, but the first argument of command.Execute.
See:
Execute Method (ADO Connection)
Execute Method (ADO Command)
connection.Execute CommandText, RecordsAffected, Options
And the syntax of the Execute method of the Command object is:
command.Execute RecordsAffected, Parameters, Options
Notice that RecordsAffected is the second argument of connection.Execute, but the first argument of command.Execute.
See:
Execute Method (ADO Connection)
Execute Method (ADO Command)
Best wishes,
Hans
Hans