How to open local Access 2000 table into ADODB.RecordSet?

[kutlesh]

I am using this helper function to open my local helper table in ADODB.RecordSet with VB.NET.

Public Sub OpenRecordSetDynamic(ByRef recordSet As ADODB.Recordset, ByVal sql As String, ByRef dbConn As ADODB.Connection)
        Try
            recordSet.Open(sql, dbConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)
        Catch ex As Exception
            Dim funcName As String = System.Reflection.MethodInfo.GetCurrentMethod().Name
            Console.WriteLine(funcName & ": " & ex.Message & ", Source: " & ex.Source & ", StackTrace: " & ex.StackTrace)
        End Try
    End Sub

where sql eguals:

DELETE FROM MyTable;

I get an exception that the object is closed, i.e in this case, it doesn't open the table.

Note: by local table i mean a table created in Access 2000.

What should I do in VB.NET?
And what do i do wrong?

[jmcilhinney]

What should I do in VB.NET?
And what do i do wrong?

You should use ADO.NET.
And you're using ADODB.

As the name suggests, ADO.NET is a data access technology that was developed specifically for .NET and that is what you should use. If you're using an Access database then you should use members of the System.Data.OleDb namespace, e.g. OleDbConnection. You can call ExecuteReader on an OleDbCommand if you want to access data in a forward-only, read-only manner, e.g. to process data as you read it. If you want random access to the result set of a query, call Fill on an OleDbDataAdapter to populate a DataTable. You edit that data if you wish, then save the changes back to the database by calling Update on the same adapter.

I would suggest that you follow the Database FAQ link in my signature below and check out some of the .NET resources there, including my own ADO.NET code examples.