PRB: "Run-time Error 3001" with ADO Command Object and adCmdTableDirect

• ActiveX Data Objects (ADO), versions 2.0, 2.01, 2.1, 2.1 SP1, 2.1 SP2, 2.5

SYMPTOMS

If you attempt to open an ADO Recordset using the Execute method of an ADO Command object, while specifying a Recordset option of adCmdTableDirect, the following error appears:

"Run-time error '3001': The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another."

CAUSE

adCmdTableDirect is not compatible with the ADO Command object. adCmdTableDirect specifies that ADO uses an interface called IOpenRowset instead of an ADO Command object.

RESOLUTION

There are two possible workarounds:

• Specify adCmdTableDirect in the Open method of an ADO Recordset.



• Specify a CommandType of adCmdText or adCmdTable.
  
  However, this would result in reduced functionality in situations where using adCmdTableDirect is an option.

STATUS

This behavior is by design.

MORE INFORMATION

adCmdTableDirect is designed specifically for OLE DB providers which support opening tables directly by name, using the IOpenRowset method of the provider. adCmdTableDirect forces ADO to use IOpenRowset rather than attempt to open the rowset by building and executing a Command object. IOpenRowset enables consumers to open and work directly with individual tables or indexes in a data source, resulting in increased performance and functionality.

By contrast, adCmdTable indicates that the provider should generate a SQL query to return all rows from a table. adCmdTable simply prepends

SELECT * FROM 

Steps to Reproduce Behavior

1. In Visual Basic, create a new Standard EXE Project.
   Form1 is created by default.



2. Set a Project and Reference to the Microsoft ActiveX Data Objects Library.



3. Place three Command buttons onto Form1.
   
   Command1, Command2, and Command3 are created by default.



4. Paste the following code into the "General Declarations" section of Form1's Code Window:




   Private Sub Command1_Click()
       
       Dim cmd As ADODB.Command
       
       Set cmd = New ADODB.Command
       cmd.CommandType = adCmdTableDirect
 
   End Sub


   Private Sub Command2_Click()
       
       Dim cmd As ADODB.Command
       Dim rs As ADODB.Recordset
       
       Set cmd = New ADODB.Command
       cmd.CommandText = "Employees"
       cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb"
       
       Set rs = cmd.Execute(, , adCmdTableDirect)
      
   End Sub

   Private Sub Command3_Click()

       Dim cmd As ADODB.Command
       Dim rs As ADODB.Recordset
       
       Set cmd = New ADODB.Command
       cmd.CommandText = "Employees"
       cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=c:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb"
      
       Set rs = New ADODB.Recordset
       rs.Open cmd, , adOpenKeyset, adLockOptimistic, adCmdTableDirect
       
       MsgBox rs("Firstname").Value
       
   End Sub
 

5. Test the project.
   
   Clicking either Command1 or Command2 generates error 3001.
   Clicking Command3 does not generate error 3001.

REFERENCES

For complete documentation on the Microsoft ActiveX Data Objects Library, please visit the following site on the World Wide Web:

Microsoft ActiveX Data Objects Library

Additional query words:

Keywords : kbADO kbADO200 kbADO201 kbDatabase kbMDAC kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbADO250
Version : WINDOWS:2.0,2.01,2.1,2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbprb