Visual Basic 2010 with MS-Access Database

Understanding ADO .Net for Visual Basic 2010 and Microsoft Access 2003 / 2007 Database

ADO.NET connected model, disconnected model
Visual Basic .Net and Ado.Net for MS-Access Database course online

There are two methods to work with MS-Access 2003/2007/2010/2013/2016/2019 Database (*.Mdb / *.Accdb) from Visual Basic 2010 using ADO .Net technology .

With .NET, Microsoft has released a new mechanism for accessing data: ADO.NET. The name is a carryover from Microsoft's ADO (ActiveX Data Objects) technology, but it no longer stands for ActiveX Data Objects--it's just ADO.NET. To avoid confusion, I will refer to ADO.NET as ADO.NET and to ADO as classic ADO.

If you're familiar with classic ADO, be careful--ADO.NET is not a descendant, it's a new technology. In order to support the Internet evolution, ADO.NET is highly focused on disconnected data and on the ability for anything to be a source of data. While you will find many concepts in ADO.NET to be similar to concepts in classic ADO, it is not the same.

Method (1) 

Online mode or Connected mode or connected model
ADO.Net Connected model, Ado.net connected mode
Ado.Net Online-mode (Connected Mode) is just like the same old method we used to work with Databases from Visual Basic 6.0 and ADO2.8 version, all you need to do is :
    • Create Access Database and a Table with your fields.
    • Define the Database path
    • Include ADO2.8 Reference into the Visual Basic 6.0 project
    • Connect the Database
    • Open the Database
    • Operate your events (Save, Edit, Delete and find)
    • Exit the VB6 Project and you will find the data in the Table were affected if events were processed correctly in the VB6 Project.
 And the this is the same logic with ADO.Net in Visual Basic 2010 and MS-Access 2003/2007. The difference is in coding interface, but also the logic is the same some how, more than VB6 Coding, but more understandable.

An example to the Online-mode with MS-Access 2003, is the Friends_Info. Basic code to connect to a MS-Access Database  2003/2007 from Visual Basic 2010 using the ADO.Net Online-Mode is :
Visual Basic Online Courses
ADO.Net connected model example source code vb.net

♦ Connect to and Open Database :
'http://www.evry1.net/
'http://adonetaccess2003.blogspot.com
'ADO.Net, MS-Access 2003, Visual Basic 2010, Online-mode, friends Info
'Form Class
Imports System
Imports System.Data
Imports System.Data.OleDb
Dim CN As New OleDb.OleDbConnection
Dim CMD As New OleDb.OleDbCommand
Dim DataR As OleDb.OleDbDataReader
Dim SqlStr, DBPath As String
'Form_Load event will connect you to the Database if you specify
'the correct name and path to it.
If CN.State = ConnectionState.Open Then CN.Close()
'DBPath must carry the correct Path+File name of your Database (I.E c:\Project\DB.mdb)
SqlStr = ("Provider = Microsoft.ACE.OLEDB.12.0;Data Source =" & DBPath) 'MS-Access 2007
SqlStr = ("Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" & DBPath) 'MS-Access 2003
CN.ConnectionString = SqlStr
CN.Open()
view raw gistfile1.vb hosted with ❤ by GitHub

♦ Save Data into Table :
'Ado.Net : How to Save into Table using Online Mode
'Visual Basic .Net, MS-Access 2003/2007, Ado.Net
'Save Method
SqlStr = Nothing
SqlStr = "Insert Into Table_Name (Field1,Field2,Field3)"
SqlStr = SqlStr + " Values ('" & TextBox1.Text & "', '" & TextBox2.Text & "', '" & TextBox3.Text & "')"
With CMD
.Connection = CN
.CommandType = CommandType.Text
.CommandText = SqlStr
End With
Dim SqlH As Integer
SqlH = CMD.ExecuteNonQuery
MsgBox ("( ") & SqlH & (" ) Friend Saved")
ClearAll()
view raw gistfile1.vb hosted with ❤ by GitHub

♦ Search for Data :
'Visual Basic.Net, Ado.Net, MS-Access2003/2007
'evry1.net/
'Search using First Name (Fname)
Dim SearchBox As String
SearchBox = InputBox("Please, Specify your friend's first name")
If SearchBox = vbNullString Then
MsgBox ("Type Something in the search")
Exit Sub
End If
SqlStr = Nothing
SqlStr = ("Select * From Table_Name Where Fname = '" & SearchBox & "'")
With CMD
.Connection = CN
.CommandType = CommandType.Text
.CommandText = SqlStr
End With
DataR = CMD.ExecuteReader
DataR.Read()
If DataR.HasRows = False Then
MsgBox ("Friend's First Name doesn't exist")
DataR.Close()
ClearAll()
Exit Sub
Else
TettBox1.Text = DataR!Field1
TettBox2.Text = DataR!Field2
TettBox3.Text = DataR!Field3
DataR.Close()
End If
DataR.Close()
End If
view raw gistfile1.vb hosted with ❤ by GitHub

♦ Delete Data from Table :
'Visual Basic .Net, ADO.Net, MS-Access2003/2007
'http://adonetaccess2003.blogspot.com
If Textbox1.Text = Trim("") Then
MsgBox ("Please Choose a friend to EDIT")
Exit Sub
End If
SqlStr = Nothing
SqlStr = ("Delete * From Table_Name Where Fname = '" & TextBox1.Text & "'")
With CMD
.Connection = CN
.CommandType = CommandType.Text
.CommandText = SqlStr
End With
SqlH = CMD.ExecuteNonQuery
MsgBox (SqlH & (" Friend's Info Deleted"))
ClearAll()
view raw gistfile1.vb hosted with ❤ by GitHub



Method (2) 
Offline mode or Disconnected mode or disconnected model
Ado.Net disconnected model
Ado.Net disconnected model

You will see the difference now of using ADO technology only if you are familiar with classic ADO that we used to use in Visual Basic 6.0, but if you did not use it before that you won't.
ADO.Net Offline mode or the Disconnected method of working with Databases has a nice and easy logic :
♦ ADO.Net disconnected model works great with Client/Server applications
ADO.Net method is the preferred way to code client/server applications. The DataSet object is what the ADO.Net offline/Disconnected mode is all about, it gives you the ability to work on the Database components separately (it stores a Database copy in the Memory - RAM of the client machine while working on the Database components), so it gives you the ability to create multiple tables, fill them with data coming from different sources, enforce relationships between pairs of tables, and more.
 ♦ ADO.Net Offline mode is not recommended for ASP.Net
Though the Dataset is a great choice to work with Client/Server applications, but when working for example with ASP.Net live pages, it is a bad choice because the ASP.Net pages don't live long before they finish their lifetime cycle.
The above Friends Info VB.Net project using ADO.Net offline mode basic codes as follow :
Connect to and Open Database :
'https://adonetaccess2003.blogspot.com
'Visual Basic Online courses, ADO.Net, Offline mode
'Connect Database using Offline mode
'☺ ☺ ☺ ☺ ☺ ☺ ☺ ☻ ☺ ☺ ☺ ☺ ☺ ☺ ☺ ☻ ☺ ☺ ☺ ☺ ☺ ☺ ☺ ☻ ☺
'Module1 codes
Imports System
Imports System.Data
Imports System.Data.OleDb
Public DBPath As String
Public CN As New OleDb.OleDbConnection
Public CMD As New OleDb.OleDbCommand
Public DBAd As New OleDbDataAdapter
Public CMDB As New OleDbCommandBuilder
Public Dtbl As New DataTable
Public DBS As New DataSet
Public Rec As DataRow
Public Frec() As DataRow
Public RR As Integer
'End Module1 codes
'Database connects on Form_Load event like this.
DBPath = ("C:\Folder\My_Database.Mdb") 'MS-Access 2003
'DBPath = ("C:\Folder\My_Database.Accdb") 'MS-Access 2007
If CN.State = 1 Then CN.Close()
CN.ConnectionString = ("Provider = Microsoft.Jet.Oledb.4.0 ; Data Source = " & DBPath) 'MS-Access2003
CN.ConnectionString = ("Provider = Microsoft.Ace.Oledb.12.0 ; Data Source = " & DBPath) 'MS-Access2007
CN.Open()
MsgBox ("CN.State")
view raw gistfile1.vb hosted with ❤ by GitHub

Save Data into Table :
'Visual Basic Online courses via
'adonetaccess2003.blogspot.com
'[Save] button in Offline mode
Rec = Dtbl.NewRow
Rec("Field1") = TextBox1.Text
Rec("Field2") = TextBox2.Text
Rec("Field3") = TextBox3.Text
Dtbl.Rows.Add(Rec)
'Sub [UpdateMe] to finally update the Table before exit application.
Public Sub UpdateMe(ByVal TabNm As String, ByVal ADtBL As OleDbDataAdapter, ByVal Msgbx1 As String)
If DBS.HasChanges = True Then
CMDB.DataAdapter = ADtBL
ADtBL.Update(DBS, TabNm)
MsgBox(Msgbx1)
End If
End Sub
UpdateMe("Table_Name", DBAd, "Updated Successfully")
view raw gistfile1.vb hosted with ❤ by GitHub

Search for Data :

'ADO.Net offline mode, VB.Net
'Programming the [Search] button
Dim XX As String
XX = InputBox("Enter search word")
If XX = Trim("") Then
MsgBox("Enter dearch word please")
Exit Sub
End If
Frec = Dtbl.Select("Field1 = '" & XX & "'")
If Frec.Length = 0 Then
MsgBox("Make sure the search word correctly spelled")
Exit Sub
End If
TextBox1.Text = Frec(0).Item("Field1")
TextBox2.Text = Frec(0).Item("Field2")
TextBox3.Text = Frec(0).Item("Field3")
Rec = Frec(0)
view raw gistfile1.vb hosted with ❤ by GitHub
Delete Data :

'ADO.Net offline mode
'ADO.Net, VB.Net, Visual Basic Online Courses
'http://adonetaccess2003.blogspot.com
'Programming the [Delete] Button
If Dtbl.Rows.Count = 0 Then
MsgBox("No Data Stored")
Exit Sub
End If
Rec.Delete()
UpdateMe("Table_Name", DBAd, "Deleted Successfully")
view raw gistfile1.vb hosted with ❤ by GitHub
I will discuss working with Crystal Reports for Visual Basic .Net in details in another post, starting Downloading SAP Crystal Reports until Packing SAP Crystal Reports and install it on the client's machine. Keep up !
Another topic about ADO.Net generally, is the ability to Create/Delete/Back-up Database, Add/Modify/Delete/Back-Up  Tables and Queries using the OpenSchema method. Let's see how to do this in Visual Basic .Net 2010.

VB2010 : To Create new Database using ADO.Net, you will need to add new reference to your project to  AdoX (Microsoft ADO Ext. 2.x for Dll and Security) ...
VS2010, ADOX, ADO.Net, MS-Access
Create new Database in Visual Basic 2010
VB2010 : To list all Database files from a Dir. into a TreeView control
'Visual Basic .Net Online Course
'List all Database files from Dir.
'into a TreeView Control in VB2010
Private Sub Button2_Click(sender As System.Object, _
e As System.EventArgs) _
Handles Button2.Click
'Refresh TreeView contents
'Get list of all Databases in the project main folder
Dim myRootPath As String = Application.StartupPath & "\"
Dim Dir As New IO.DirectoryInfo(myRootPath)
Dim aryFil As IO.FileInfo() = Dir.GetFiles("*.*")
Dim Fil As IO.FileInfo
Dim DB_S As TreeNode
DB_S.Nodes.Clear()
For Each Me.Fil In aryFil
If LCase(Microsoft.VisualBasic.Right(Fil.Name, 3)) = _
LCase("mdb") Then
DB_S.Nodes.Add("", Fil.Name, 1)
Dim N As TreeNode
For Each N In DB_S.Nodes
N.Name = Fil.Name
Exit For
Next
End If
Next
End Sub
view raw gistfile1.vb hosted with ❤ by GitHub

VB2010 : To manage Tables using OpenSchema method

 VB2010 : To manage Fields using OpenSchema method

 

Some Visual Basic Exceptions and Solutions Working with MS-Access 2003/2007

Exception: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key Information"
Solution  :
'Visual Basic 2010 Online Course
'Exception and Solution (1)
'Exception :
"Dynamic SQL generation for the UpdateCommand is not supported against
a SelectCommand that does not return any key Information"
'Cause :
"Error while attempting to Update [Change] the Table's field.
The Table you are working on doesn't have a Primary-Key"
'Solution :
"Open your MS-Access Database Table and Create a Primary Key"
view raw gistfile1.txt hosted with ❤ by GitHub

Exception: "Syntax error (missing operator) in query expression ''He's')'.", while trying to Execute INSERT INTO Statement which carries String Values that contain Apostrophe like (He's)"
Solution :
'Visual Basic Online Course
'VB 2010 Insert InTo Values with Apostrophe
Function SqlStrAps(ByVal ToStr As String) As String
SqlStrAps = "'" & Replace(ToStr, "'", "''") & "'"
End Function
'_OR, you can do this .
Dim SqlStr As String = _
"Insert Into Table_Name (Field1,Field2,Field3_With_Apostrophe)"
SqlStr = SqlStr + " Values ('" & TextBox1.Text & "', _
'" & TextBox2.Text & "', '" & TextBox3.Text.Replace("'", "''") & "')"
With CMD
.Connection = CN
.CommandType = CommandType.Text
.CommandText = SqlStr
End With
Dim SqlH As Integer
SqlH = CMD.ExecuteNonQuery
view raw gistfile1.vb hosted with ❤ by GitHub

Exception: Application was Unable to Start correctly (0xC0000142) in Office 2016

Solution : Re-install Microsoft Access Database Engine 2016 Redistributable [AccessDatabaseEngine16.exe] from here [https://www.microsoft.com/en-us/download/details.aspx?id=54920]

Visual Studio 2015 and Microsoft Access 2016

Connection String : The OleDB Connection String used to connect Microsoft Access Database 2016 (accdb) with Visual Basic 2015 (.Net FrameWork 4.5) Win10 32bit [WinForm] Project.
Imports System.Data.OleDb
Public Class ThisClass
Private ConnectionString As String
Private CN As OleDbConnection = New OleDbConnection
'This function Returns the Oledb Provider for your Project
'I.e: if you have Office 2016 installed, it will return :
'Microsoft.ACE.OLEDB.16.0
Public Function FindProvider() As String
Dim Provider As String = String.Empty
Dim reader = OleDbEnumerator.GetRootEnumerator()
Dim list = New List(Of String)
While reader.Read()
For i = 0 To reader.FieldCount - 1
If reader.GetName(i) = "SOURCES_NAME" Then
list.Add(reader.GetValue(i).ToString())
End If
Next
End While
Return Nothing
reader.Close()
For Each provider In list
If Provider.StartsWith("Microsoft.ACE.OLEDB") Then
Provider = Provider.ToString()
Return Provider
Else
Return Nothing
Exit Function
End If
Next
End Function
'This function is to validate connection to the database *.accdb
Public Function DBConnected(ByVal DBLocation As String, ByVal DBPass As String) As Boolean
ConnectionString =
("Provider=" & FindProvider() & ";Data Source=" & DBLocation & ";" _
& "Jet OLEDB:Database Password = '" & DBPass & "'; " _
& "Persist Security Info=False;")
CN.ConnectionString = ConnectionString
If CN.State = ConnectionState.Open Then CN.Close()
Try
CN.Open()
Catch ex As OleDbException
MsgBox("Error Database connection : " & ex.Message, MsgBoxStyle.Critical)
Result = False
Return Result
Exit Function
End Try
Result = True
Return Result
End Function
'Ofcourse this is not secure connection String, but just an example.
'You should always use Configuration Files to manage Database Connections.
End Class

How to properly Close Connection to OleDB

Code to close all Connection to Microsoft Access Database after exit windows form application in VB.NET
'Close all OleDB Connections to MS Access Database when exit application
'adonetaccess2003.blogspot.com
Public Sub CloseCN()
If Not IsNothing(CN) Then
For Each p As Process In Process.GetProcesses()
If p.ProcessName = "MSACCESS" Then
Try
p.CloseMainWindow()
p.Dispose()
p.Kill()
Catch ex As Exception
Exit Sub
End Try
End If
Next
CN.Dispose()
CN = Nothing
End If
End Sub
view raw gistfile1.txt hosted with ❤ by GitHub




Here are some online Visual Basic lessons and courses :

Popular posts from this blog

VB .NET DropBox Api Source Code Example

VB .NET Google Drive Api Source Code Example

VB.NET Access 2007 Hierarchical TreeView

VB.NET How to properly close a windows application Form

DAO in VB .NET MS ACCESS Database

Solution - There is already an open DataReader associated with this Command which must be closed first.