Description:
When Connection.Execute is called Either with adCmdTable or adCmdTableDirect command options no records are being returned. adCmdTable is returning 0, and adCmdTableDirect is returning -1.
According to Docs-
adCmdTableDirect: Evaluates CommandText as a table name whose columns are all
returned. Used with Recordset.Open or Requery only. To use the Seek method, the
Recordset must be opened with adCmdTableDirect. This value cannot be combined
with the ExecuteOptionEnum value adAsyncExecute.
adCmdTable: Evaluates CommandText as a table name whose columns are all returned
by an internally generated SQL query.
How to repeat:
mysql> CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR);
mysql> INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C');
VB code:
Sub OptionTest()
On Error GoTo EH
Dim strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim iRecCount As String
Set connection1 = New ADODB.Connection
strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseServer
connection1.Open
' open recordset with adCmdTable works and returns 0
Set recset1 = connection1.Execute("ado_test", iRecCount, adCmdTable)
If recset1.State And adStateOpen Then
MsgBox iRecCount & " rows returned"
End If
recset1.Close
' open recordset with adCmdTable Fails and returns -1
Set recset1 = connection1.Execute("ado_test", iRecCount, adCmdTableDirect)
If recset1.State And adStateOpen Then
MsgBox iRecCount & " rows returned"
End If
If recset1.State And adStateOpen Then
recset1.Close
End If
If connection1.State And adStateOpen Then
connection1.Close
End If
Set recset1 = Nothing
Set connection1 = Nothing
End Sub
Description: When Connection.Execute is called Either with adCmdTable or adCmdTableDirect command options no records are being returned. adCmdTable is returning 0, and adCmdTableDirect is returning -1. According to Docs- adCmdTableDirect: Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect. This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute. adCmdTable: Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query. How to repeat: mysql> CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR); mysql> INSERT INTO ado_test VALUES (1,'A'),(2,'B'),(3,'C'); VB code: Sub OptionTest() On Error GoTo EH Dim strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim iRecCount As String Set connection1 = New ADODB.Connection strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseServer connection1.Open ' open recordset with adCmdTable works and returns 0 Set recset1 = connection1.Execute("ado_test", iRecCount, adCmdTable) If recset1.State And adStateOpen Then MsgBox iRecCount & " rows returned" End If recset1.Close ' open recordset with adCmdTable Fails and returns -1 Set recset1 = connection1.Execute("ado_test", iRecCount, adCmdTableDirect) If recset1.State And adStateOpen Then MsgBox iRecCount & " rows returned" End If If recset1.State And adStateOpen Then recset1.Close End If If connection1.State And adStateOpen Then connection1.Close End If Set recset1 = Nothing Set connection1 = Nothing End Sub