I decided to write this post because of a question from a reader about how to save Microsoft Access connection settings in user settings. User settings can be used to store any setting that needs to be stored.
While working with Ms Access database, most of us usually store the access file in the application path. But if you want it to be customizable by user, let's save it in user setting.
There are several types of connection strings for Ms. databases. Access, you can see more details at this link.
Here I only discuss 1 connection example. The highlighted text is the value to be set as dynamic value.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\latihan.accdb;Persist Security Info=False;
OLEDB 12.0 used by access 2007, 2010, 2013. I made a database bernama latihan.accdb and create 2 copies 2 that saved in 2 different path:
- D:\latihan.accdb
- D:\Blogging\latihan.accdb
This is the contents of the database that I'm using.
Let's move to the VB.NET project. I've already had a project named Latihan that contain a form named Form1. Set UI display as below. Dont forget to also add a OpenFileDialog, this is used to pick access file.
Then open project properties.
Choose Settings on the left panel. it will show grid/table to be filled with setting and its value.
I'll only make one named dbPath (that will be property name of setting while called by code). Type = string because will be concatenated with connection string, Scope = User, so ofter compiled user can make their own setting and save it. And i set D:\latihan.accdb for the initial value.
Here is code behind Form1.
Public Class Form1
Dim strConn As String
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
'menampilkan nilai dbPath saat ini
txtFile.Text = My.Settings.dbPath
'Kombinasi nilai dbPath dengan connection string
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & My.Settings.dbPath & ";" & _
"Persist Security Info=False;"
lblConnectionString.Text = strConn
End Sub
End Class
If we try to run, code above will result this display.
Next is code for choosing file with OpenFileDialog.
Private Sub btnFile_Click(sender As Object, e As EventArgs) Handles btnFile.Click
OpenFileDialog1.Filter = "Ms. Access|*.accdb"
OpenFileDialog1.FileName = ""
If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
txtFile.Text = OpenFileDialog1.FileName
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & txtFile.Text & ";" & _
"Persist Security Info=False;"
lblConnectionString.Text = strConn
End If
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
My.Settings.dbPath = txtFile.Text
My.Settings.Save()
End Sub
Let's run the application again. Click file button to choose access file. Choose an access file then click open.
The textbox of file will displat file path from selected file before. Click Save Connection Setting to save current setting.
You can try to close and re-run to see how user setting has already saved.
To see how this connection setting works I add code to display category data on grid.
Warning: You need to add import statement on the top of code!!!
Imports System.Data.OleDb
Then add this code under event btnCheck_Click.
Private Sub btnCheck_Click(sender As Object, e As EventArgs) Handles btnCheck.Click
Dim con As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand
Dim adapt As New OleDbDataAdapter
Dim dt As New DataTable
Try
con.Open()
cmd.Connection = con
cmd.CommandText = "SELECT kodekategori, namakategori " & _
"FROM kategori"
adapt.SelectCommand = cmd
adapt.Fill(dt)
grdData.DataSource = dt
con.Close()
Catch myerror As OleDbException
MessageBox.Show("Error: " & myerror.Message)
Finally
con.Dispose()
End Try
End Sub
Try to run and click Check Connection button.
Here is the complete code:
0 Comments