Thursday, April 18, 2013

Using the ADO and Recordset in VB.NET

Using the ADO and Recordset in VB.NET

Everything you need to know on Recordset with Visual Basic 2010

For VB6 or VBA programmers, this article might be a good summary on how to connect and retrieve data from a MS-Access File. You might notice that objects you used in the past such as RecordSet, ADODC, ADODB, Microsoft.Jet.OLEDB, CursorLocation, adUserServer, adUseClient… might not work as well in Visual Studio .NET (Visual Basic 2005, 2008, 2010, 2012…)

There are many reasons why calling ADODB might not work correctly. The simplest and the shortest way to say it is that ADODB relies on older technologies. It uses the old COM (Component Object Model) and old OLE DB and ODBC stuff created many years ago. COM library are still used but it might be less cost efficient these modern days. Also, all ADO and ODBC stuff where removed from every default Windows 7 installation.

Here is a sample made in VBA-VB6 in Excel 2010. It is a simple UserForm1 that read a MS-Access File. Make sure you added a reference to Microsoft ActiveX Data Object Library.

'ajouter en référence Microsoft ActiveX Data Object (ADO) n'importe quelle numéro de version

Private Sub CommandButton1_Click()
End Sub

Private Sub CommandButton2_Click()
    If Len(TextBox1.Value) > 0 Then
        MsgBox("you must put a path for the MS-Access File")
    End If

End Sub

Private Sub Frame1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

' Check-Kay Wong
'Pour utiliser les vieilles technologies
'et  importer des base de données style MDB avec Excel.
Public Sub TEST(Optional sPath As String)

    Dim index1 As Integer
    Dim aString As String
    Dim oConnection As ADODB.Connection
    Dim oCommand As ADODB.Command
    Dim oRecordset As ADODB.Recordset

    On Error GoTo ErrorHandler

    oConnection = New ADODB.Connection
    With oConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0;"
        .ConnectionTimeout = 30
        .IsolationLevel = adXactIsolated
        .Mode = adModeReadWrite
        .Open("Data Source=" & sPath & " ;User Id=Admin; Password=")
    End With

    oRecordset = New ADODB.Recordset
    With oRecordset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .CursorType = adOpenStatic
        .CacheSize = 30
        .Source = "SELECT * From dictionary"
        .ActiveConnection = oConnection
        '    Set .ActiveCommand = oCommand
        .Open, , , , adCmdText
    End With

    index1 = 0
        aString = ""

        For index1 = 0 To oRecordset.Fields.Count - 1 Step 1
            aString = aString & oRecordset.Fields(index1).Value & vbCrLf
        Next index1


    Loop Until oRecordset.EOF = True

    If Err.Number <> 0 Then
    End If

End Sub

Has you can’t see, nothing very special because this is a sample. It starts with some basic Error Handling, make a Connection, get a RecordSet from a String and navigate thought the RecordSet line by line. It is pretty basic.

Now here is the something similar in VB.NET hoping the transition from VB6 to VB.NET is done nice and sweet.

    Private Sub Load_SQL_MDB(ByVal sPath As String, sTable As String)
        Dim oOleDbConnection As System.Data.OleDb.OleDbConnection
        Dim oOleDbCommand As OleDb.OleDbCommand
        Dim oOleDbDataReader As OleDb.OleDbDataReader
        Dim sqlText As String
        Dim index1 As Integer

            index1 = 0
            oOleDbConnection = New System.Data.OleDb.OleDbConnection()
            oOleDbConnection.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =" & sPath & "; Persist Security Info =False;"
            oOleDbConnection.Open() 'ATTENTION, FONTIONNE QUE POUR X86 ; ATTENTION ONLY FOR x86

            If oOleDbConnection.State = ConnectionState.Open Then
                sqlText = "select * from " & sTable
                oOleDbCommand = New System.Data.OleDb.OleDbCommand(sqlText, oOleDbConnection)
                oOleDbDataReader = oOleDbCommand.ExecuteReader()
                Do While oOleDbDataReader.Read()
                    Dim str(oOleDbDataReader.FieldCount - 1) As String
                    For index1 = 0 To oOleDbDataReader.FieldCount - 1 Step 1
                        str(index1) = oOleDbDataReader.GetValue(index1)
                    Next index1

                    'ComboBox1.Items.Insert(index1, oOleDbDataReader.GetValue(0))
                    index1 = index1 + 1

            End If

            oOleDbDataReader = Nothing
            oOleDbCommand = Nothing
        Catch ex As Exception

        End Try
    End Sub

The VB6-VBA code looks pretty the same with the VB.NET code. Is not the most popular way to get information from a MDB file, but it will work. If you run the code, you will be able to load a MDN file and to display some stuff in the little table DataGridView1.
Now, why this way is not popular? Lets put it this way. The old fashion way, you need to build a SQL sentence to get all the data. If you make a syntax error, well, you are screwed. The new fashion way use methods and object to get your data. You could take a look the MSDN article 315974  for more information. Please note that the last update on that article was in November 2007.

Personally, I still half way between the old VB6 and VB.NET. I simply prefer to use old command string to open a DataRecord. Shame on me!

The Excel File and the VB.NET project are available to download or to test.

x86 in Visual StudioThe 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
If you have this message, that means you VB.NET project is un “any CPU” or running on x64. You have to run your project in x86. Sorry, old technology.

Download the project  :


The program I love to use, buy it: Visual Studio 2010 Professional (Old Version)

1 comment: