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()
    TEST(TextBox1.Value)
End Sub

Private Sub CommandButton2_Click()
    If Len(TextBox1.Value) > 0 Then
        TEST(TextBox1.Value)
    Else
        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


    oRecordset.MoveFirst()
    index1 = 0
    Do
        aString = ""

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

        MsgBox(aString)

        oRecordset.MoveNext()
    Loop Until oRecordset.EOF = True

ErrorHandler:
    If Err.Number <> 0 Then
        MsgBox(Err.Description)
    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

        Try
            index1 = 0
            'ConnectionString
            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()
                'oOleDbDataReader.FieldCount
                Do While oOleDbDataReader.Read()
                    'oOleDbDataReader.FieldCount
                    Dim str(oOleDbDataReader.FieldCount - 1) As String
                    For index1 = 0 To oOleDbDataReader.FieldCount - 1 Step 1
                        str(index1) = oOleDbDataReader.GetValue(index1)
                    Next index1

                    Me.DataGridView1.Rows.Add(str)
                    'ComboBox1.Items.Insert(index1, oOleDbDataReader.GetValue(0))
                    index1 = index1 + 1
                Loop

            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  : OpenMDBSample.zip

References:



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

No comments:

Post a Comment