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

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

in front the specified tablename.

Steps to Reproduce Behavior

This sample uses the Nwind database that comes with Visual Basic.

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.