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.
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)
Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com
atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :
