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) nimporte 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

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.

The 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:

my web site: Check Technologies official website

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

Blog Information