14-. Setting up a Database with Visual Basic Using a Local Access 2010 Database To

14. Setting up a Database with Visual Basic Using a Local Access 2010 Database To Use SQL and OLEDB and VB Studio.Net 2010 or 2008 from Scratch In 32 Bit Windows XP by Jim Krumm

1. Click here for a complete project with source in VB 2010 or click here for a complete project with source in VB 2008. This program runs and compiles in 32 Bit Windows XP. The executable should run in Windows 7, 64 bit, provided the database is present, and the path adjusted to the database. But development can an issue because of the 32 bit vs. 64 bit drivers. I have a student who reports that using a older mdb database may resolve the issue with working on 64 Windows 7 using a 32 bit database, and then using the older Jet driver instead of ACE. At any rate, this program uses a database set inside it's innermost debug directory (which may have to be adjusted in Windows 7). A 2010 accdb database and a 2002 mdb database (with a working but remarked connection string) is included in the above zip files. If you run the 2002 mdb database and need the Microsoft jet driver refer to http://support.microsoft.com/kb/239114. This program does not use Odbcad32. However to make this work you will need to at least install the Access Runtime available as a free download from Microsoft. To download the Access Runtime go to http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=10910    and/or the Microsoft Access Database Engine at: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255 To begin create a new VB 2008 or 2010 project called mysqldatabaseprogram and click OK as shown below: 2. Modify the code in your project to read as follows: Imports System.Data.SqlClient Imports System Imports System.Data Imports System.Collections Imports System.Windows.Forms Imports System.Resources Imports System.Data.OleDb Public Class Form1     Dim myConnection As System.Data.OleDb.OleDbConnection      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load      End Sub End Class 3.  Create the Database in Access 2007 Open Access 2007 named mysampledatabase.accdb into the inner most debug directory of your project as shown below: Set up the table as shown below, renaming the first column after ID firstName, and the one after lastName. Beware, if you choose a Keyword by Microsoft Jet, this will not work as a column name. For a list of Jet Keywords refer to http://support.microsoft.com/kb/248738. I found the column names of first and last were keywords and wouldn't work Fill in several first names, and last names as shown below.    Exit Access and when you are asked what you wish to call the table name it Table1 as shown below Confirm the file was saved to the innermost directory of your project as shown below: 4.  Design the form of your project to look as follows: 5. Add the following Code: Imports System Imports System.Data Imports System.Collections Imports System.Windows.Forms Imports System.Resources Imports System.Data.OleDb  Public Class Form1     Dim myConnection = New System.Data.OleDb.OleDbConnection()     Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load      End Sub      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click         myConnection = New System.Data.OleDb.OleDbConnection()         myConnection.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =mysampledatabase.accdb; Persist Security Info =False;"         ' try this for Windows 7 if the first connection string doesn't work             'myConnection.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0; Data Source =mysampledatabase.mdb; Persist Security Info =False;"         Try             myConnection.Open()             ListBox1.Items.Clear()             ListBox1.Items.Add("Connection made")         Catch ex As Exception             MsgBox("Error")             Console.WriteLine(ex.Message)         End Try     End Sub      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click         'count records         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As String = ""          If myConnection.State <> ConnectionState.Open Then             MsgBox("Error")             Exit Sub         Else             SqlCommand.Connection = myConnection             SqlCommand.CommandText = "select count(*) as RecordCount from Table1"             result = SqlCommand.ExecuteScalar().ToString()             ListBox1.Items.Clear()             ListBox1.Items.Add("Number of Records: " & result)         End If     End Sub     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click         'list records         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As OleDb.OleDbDataReader         Dim sqlText As String         If myConnection.State <> ConnectionState.Open Then             MsgBox("Connection is not open")             Exit Sub         End If         sqlText = "select * from Table1"         SqlCommand = New OleDbCommand(sqlText, myConnection)         result = SqlCommand.ExecuteReader()         ListBox1.Items.Clear()         Do While result.Read()             ListBox1.Items.Add(result.GetValue(1).ToString() & " " & result.GetValue(2).ToString())         Loop         result = Nothing         SqlCommand = Nothing     End Sub     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click         'Sort by first         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As OleDb.OleDbDataReader         Dim temp As String = ""         If myConnection.State <> ConnectionState.Open Then             MsgBox("Connection is not open")             Exit Sub         End If         SqlCommand.Connection = myConnection         SqlCommand.CommandText = "select * from Table1 order by firstName"         result = SqlCommand.ExecuteReader()         ListBox1.Items.Clear()         Do While result.Read()             ListBox1.Items.Add(result.GetValue(1).ToString() & " " & result.GetValue(2).ToString().Trim)         Loop         result = Nothing         SqlCommand = Nothing     End Sub     Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click         'find by lastName         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As OleDb.OleDbDataReader         Dim temp As String = ""         If myConnection.State <> ConnectionState.Open Then             MsgBox("Connection is not open")             Exit Sub         End If         SqlCommand.Connection = myConnection         SqlCommand.Connection = myConnection         SqlCommand.CommandText = "select * from Table1 order by lastName"         result = SqlCommand.ExecuteReader()         ListBox1.Items.Clear()         Do While result.Read()             ListBox1.Items.Add(result.GetValue(1).ToString() & " " & result.GetValue(2).ToString())         Loop         result = Nothing         SqlCommand = Nothing      End Sub     Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click         '*************************         'Close Connection button         ListBox1.Items.Clear()         If myConnection.Equals(Nothing) Then             ListBox1.Items.Add("No Connection to close")             Exit Sub         End If          If myConnection.State <> ConnectionState.Open Then             ListBox1.Items.Add("Connection is not open")             Exit Sub         End If         myConnection.Close()         If myConnection.State = ConnectionState.Closed Then             ListBox1.Items.Add("Connection is closed")             Exit Sub         End If         '**************************      End Sub     Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click         'Find Wildcard Match button         Dim firstName As String = ""         Dim lastName As String         Dim sqlFind As String = ""         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As OleDb.OleDbDataReader         Dim found As Integer = -1         Dim temp As String = ""         ListBox1.Items.Clear()         If TextBox1.Text.Trim = "" And TextBox2.Text.Trim = "" Then             MsgBox("Enter First Name or last name or first Name and last Name")             Exit Sub         End If         firstName = TextBox1.Text.Trim         lastName = TextBox2.Text.Trim         sqlFind = "select * from Table1 Where firstName like '%" + firstName + "%' and lastName like '%" + lastName + "%'"         SqlCommand.Connection = myConnection         SqlCommand.CommandText = sqlFind         result = SqlCommand.ExecuteReader()         found = -1         Do While result.Read()             ListBox1.Items.Add(result.GetValue(1).ToString() & " " & result.GetValue(2).ToString())             found = found + 1         Loop         If found = -1 Then ListBox1.Items.Add("No records found")         result = Nothing         SqlCommand = Nothing     End Sub     Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click         'Delete button         ListBox1.Items.Clear()         Dim sqlDelete As String = ""         Dim firstName As String = ""         Dim lastName As String = ""         Dim result As Integer = -1         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         firstName = TextBox1.Text         lastName = TextBox2.Text         sqlDelete = "delete from Table1 Where lastName = '" + lastName & "' and firstName = '" & firstName & "'"         SqlCommand.Connection = myConnection         SqlCommand.CommandText = sqlDelete         result = SqlCommand.ExecuteNonQuery()         If result = 0 Then             MsgBox("No records deleted!")         Else             ListBox1.Items.Add(CStr(result) & " record(s) deleted!")         End If         SqlCommand = Nothing     End Sub     Private Sub Button9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button9.Click         'Insert button         Dim firstName As String = ""         Dim lastName As String = ""         Dim sqlInsert As String = ""         Dim result As Integer = -1         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         ListBox1.Items.Clear()         If TextBox1.Text = "" Or TextBox2.Text = "" Then             MsgBox("Both first Name and last Names Required!")             Exit Sub         End If         firstName = TextBox1.Text.Trim         lastName = TextBox2.Text.Trim         sqlInsert = "INSERT INTO Table1(firstName, lastName) VALUES('" + firstName + "', '" + lastName + "')"         Try             SqlCommand.Connection = myConnection             SqlCommand.CommandText = sqlInsert             result = SqlCommand.ExecuteNonQuery()             If result = 0 Then                 ListBox1.Items.Add("No records inserted!")             Else                 ListBox1.Items.Add(CStr(result) & " record inserted!")             End If         Catch ex As Exception             MsgBox(ex.ToString)         End Try         result = -1         SqlCommand = Nothing     End Sub     Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click         'Find and Modify button         Dim firstName As String = ""         Dim lastName As String = ""         Dim newFirst As String         Dim newLast As String = ""         Dim sqlFind As String = ""         Dim sqlUpdate As String = ""         Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim sqlUpdateCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand         Dim result As OleDb.OleDbDataReader         Dim updatecount As Integer = -1         If TextBox3.Text = "" And TextBox4.Text = "" And TextBox5.Text = "" And TextBox6.Text = "" Then             ListBox1.Items.Add("Enter target first Name and last Name names, and modified first Name and last names!")             Exit Sub         End If         firstName = TextBox3.Text         lastName = TextBox4.Text         newFirst = TextBox5.Text         newLast = TextBox6.Text         sqlFind = "select * from Table1 Where lastName = '" + lastName + "' and firstName = '" + firstName + "'"         SqlCommand.Connection = myConnection         SqlCommand.CommandText = sqlFind         result = SqlCommand.ExecuteReader()         If (Not (result.HasRows)) Then             MsgBox("No records found to modify")         Else             result.Close()             result = Nothing             sqlUpdate = "update Table1 set firstName = '" + newFirst + "', lastName = '" + newLast + "' where firstName = '" + firstName + "' and lastName = '" + lastName + "'"             sqlUpdateCommand.Connection = myConnection             sqlUpdateCommand.CommandText = sqlUpdate             updatecount = sqlUpdateCommand.ExecuteNonQuery()             If (updatecount = 0) Then                 ListBox1.Items.Add("No records updated!")             Else                 ListBox1.Items.Add("Record updated!")             End If         End If         result = Nothing         sqlUpdateCommand = Nothing         SqlCommand = Nothing     End Sub  End Class

---