VB.NET: MS-Access Interaction Tutorial (Part I)

[miCRoSCoPiC^eaRthLinG 0]

VB.NET - MS-Access Interaction Tutorial

 

????[/tab]I got down to writing this tutorial because of this certain question that Dhanesh posted on our forums here. This tutorial will attempt to show you how to:

Create a MS-Access Database

Create/Edit/Delete Tables in it

Access it and Add/Edit/Update data in those tables

all with VB.NET.

 

 

Note:????This is not a beginner's tutorial and I'll assume you know all the basics of creating WinForm based applications using VS.NET. The screen-shots provided are from VS.NET 2005 - but you shouldn't face any difficult even if you're using VS.NET 2003. The functionality is essentially the same.

 

????We will start by creating a blank Windows Application project called MS-AccessTest.

 

Creating a MS-Access Database

????Microsoft didn't provide us with any easy modern method of creating an Access Database. No such classes are provided in the .NET Framework and hence we'll have to fall back onto an old library routine that came along with the older genre of Visual Studio. As for accessing a database and modifying data in it, we can conveniently use the OleDB Wrapper.

 

????For now, right-click on the project name in the Solution Explorer and select Add Reference. This will bring up the Add Reference dialog box. Next select the COM tab and scroll down till you find a library named Microsoft ADO Ext. 2.8 for DLL and Security as shown in the screen-shot.

 

Click OK to add the reference to this dll. The library should show up as ADOX in your References in the Solution Explorer, if you're using VS.NET 2003. Else you can spot it under the References tab under Project Properties in VS.NET 2005.

 

????Next, we'll create our own class to encapsulate all database related routines. In the Solution Explorer, right-click on the solution name and select Add > Class. Name this class Database.We'll create a function called CreateDatabase( FileName ) that'll take the FileName as a parameter and create a blank database at the given location.

 

Here's the code for the function - it returns True or False depending on the success/failure in creating the database. We use the Create method which can be found under the ADOX.Catalog class to create the database. This method takes the standard OleDB connection string (as shown in oConnect) and creates a blank database with the file name passed to it as Data Source.

Public Function CreateDatabase(ByVal FileName As String) As Boolean

 

'Instantiate the ADOX Object

Dim ADOXCatalog As ADOX.Catalog = New ADOX.Catalog

Dim oConnect As String

 

'Setup the connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName

 

' Try creating the database

Try

 

'Create the database

ADOXCatalog.Create(oConnect)

 

Catch ex As System.Runtime.InteropServices.COMException

Catch ex As Exception

 

'Show error message and return failure

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

Return False

 

Finally

 

'Dispose the object

ADOXCatalog = Nothing

 

End Try

 

'Return success

Return True

 

End Function

linenums:0'>'Method to create a blank database Public Function CreateDatabase(ByVal FileName As String) As Boolean 'Instantiate the ADOX Object Dim ADOXCatalog As ADOX.Catalog = New ADOX.Catalog Dim oConnect As String 'Setup the connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName ' Try creating the database Try 'Create the database ADOXCatalog.Create(oConnect) Catch ex As System.Runtime.InteropServices.COMException Catch ex As Exception 'Show error message and return failure MsgBox(ex.Message & vbCrLf & ex.StackTrace) Return False Finally 'Dispose the object ADOXCatalog = Nothing End Try 'Return success Return True End Function

Create/Edit/Delete Tables

????Our next job is to define some tables in this database. For this we use the standard OleDB Data Provider.You need to have a little prior knowledge of SQL to understand this - although syntactically this is very simple. We start by defining a process called CreateTable(). We'll create a simple table named accessTest for demonstration purposes. This will have just two columns:

ID - which is of the Data Type COUNTER, which basically means an Auto Incrementing Integer field

Name - A TEXT field of length 50, which will store the name of a person.

Moreover, we'll set the ID field as the Primary Key.The code for this function follows:

Dim oConn As OleDbConnection

Dim oComm As OleDbCommand

Dim oConnect, oQuery As String

 

'Define connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password="

 

'Define the query string the creates the table

oQuery = "CREATE TABLE accessTest ( ID Counter," &

"Name TEXT(50) NOT NULL," &

"PRIMARY KEY(ID) )"

 

' Instantiate the connectors

oConn = New OleDbConnection(oConnect)

oComm = New OleDbCommand(oQuery, oConn)

 

'Try connecting and crate the table

Try

 

'Open the connection

oConn.Open()

 

'Perform the Non-Query

oComm.ExecuteNonQuery()

 

'Close the connection

oConn.Close()

 

Catch ex As OleDb.OleDbException

Catch ex As Exception

 

'Show error message and return failure

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

Return False

 

Finally

 

'Dispose the connector objects

If Not (oConn Is Nothing) Then

oConn.Dispose()

oConn = Nothing

End If

If Not (oComm Is Nothing) Then

oComm.Dispose()

oComm = Nothing

End If

 

End Try

 

'Return success

Return True

 

End Function

linenums:0'>Public Function CreateTable(ByVal FileName As String) As Boolean 'Define the connectors Dim oConn As OleDbConnection Dim oComm As OleDbCommand Dim oConnect, oQuery As String 'Define connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password=" 'Define the query string the creates the table oQuery = "CREATE TABLE accessTest ( ID Counter," & _ "Name TEXT(50) NOT NULL," & _ "PRIMARY KEY(ID) )" ' Instantiate the connectors oConn = New OleDbConnection(oConnect) oComm = New OleDbCommand(oQuery, oConn) 'Try connecting and crate the table Try 'Open the connection oConn.Open() 'Perform the Non-Query oComm.ExecuteNonQuery() 'Close the connection oConn.Close() Catch ex As OleDb.OleDbException Catch ex As Exception 'Show error message and return failure MsgBox(ex.Message & vbCrLf & ex.StackTrace) Return False Finally 'Dispose the connector objects If Not (oConn Is Nothing) Then oConn.Dispose() oConn = Nothing End If If Not (oComm Is Nothing) Then oComm.Dispose() oComm = Nothing End If End Try 'Return success Return True End Function

[tab]Next we've to define some methods to READ data from this table and return to us as a DataSet - which can be easily bound to a DataGrid to display the data directly on screen. Once again - we define a method called FetchData() to facilitate this. We use the standard connectors that we'd used in the above procedure, but along with that we introduce a new object called a DataAdapter - which is used to read the whole table and place the data into the DataSet.

Dim oConn As OleDbConnection

Dim oComm As OleDbCommand

Dim oData As OleDbDataAdapter

Dim resultSet As New DataSet

Dim oConnect, oQuery As String

 

'Define connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password="

 

'Query String

oQuery = "SELECT * FROM accessTest"

 

'Instantiate the connectors

oConn = New OleDbConnection(oConnect)

oComm = New OleDbCommand(oQuery, oConn)

oData = New OleDbDataAdapter(oQuery, oConn)

 

Try

 

'Open connection

oConn.Open()

 

'Fill dataset

oData.Fill(resultSet, "accessTest")

 

'Close connection

oConn.Close()

 

Catch ex As OleDb.OleDbException

Catch ex As Exception

 

'Show error message and exit

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

 

Finally

 

'Dispose the connector objects

If Not (oConn Is Nothing) Then oConn.Dispose()

oConn = Nothing

If Not (oComm Is Nothing) Then oComm.Dispose()

oComm = Nothing

If Not (oData Is Nothing) Then oData.Dispose()

oData = Nothing

 

End Try

 

'Return results

Return resultSet

 

End Function

linenums:0'>Public Function FetchData(ByVal FileName As String) As DataSet 'Define the connectors Dim oConn As OleDbConnection Dim oComm As OleDbCommand Dim oData As OleDbDataAdapter Dim resultSet As New DataSet Dim oConnect, oQuery As String 'Define connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password=" 'Query String oQuery = "SELECT * FROM accessTest" 'Instantiate the connectors oConn = New OleDbConnection(oConnect) oComm = New OleDbCommand(oQuery, oConn) oData = New OleDbDataAdapter(oQuery, oConn) Try 'Open connection oConn.Open() 'Fill dataset oData.Fill(resultSet, "accessTest") 'Close connection oConn.Close() Catch ex As OleDb.OleDbException Catch ex As Exception 'Show error message and exit MsgBox(ex.Message & vbCrLf & ex.StackTrace) Finally 'Dispose the connector objects If Not (oConn Is Nothing) Then oConn.Dispose() oConn = Nothing If Not (oComm Is Nothing) Then oComm.Dispose() oComm = Nothing If Not (oData Is Nothing) Then oData.Dispose() oData = Nothing End Try 'Return results Return resultSet End Function

So far we've created some basic routines to create database/table and fetch data. Now it's time to concentrate on the main interface and come up with a way to display the fetched data on the screen.For this we'll utilize a DataGrid control, as it can be easily bound to the fetched data. On the main form, drop a DataGrid. Also add a DataSet to the main form. We're going to link these two controls and whenever we update the dataset with any data from the database, the datagrid will reflect the changes on screen. Likewise, when data is modified on screen, the changes get reflected in the actual database.

 

 

Continued in Part II ...

[apurba 0]

VB.NET - MS-Access Interaction Tutorial

 

????[/tab]I got down to writing this tutorial because of this certain question that Dhanesh posted on our forums here. This tutorial will attempt to show you how to:

Create a MS-Access Database

Create/Edit/Delete Tables in it

Access it and Add/Edit/Update data in those tables

all with VB.NET.

Note:????This is not a beginner's tutorial and I'll assume you know all the basics of creating WinForm based applications using VS.NET. The screen-shots provided are from VS.NET 2005 - but you shouldn't face any difficult even if you're using VS.NET 2003. The functionality is essentially the same.

 

????We will start by creating a blank Windows Application project called MS-AccessTest.

 

Creating a MS-Access Database

????Microsoft didn't provide us with any easy modern method of creating an Access Database. No such classes are provided in the .NET Framework and hence we'll have to fall back onto an old library routine that came along with the older genre of Visual Studio. As for accessing a database and modifying data in it, we can conveniently use the OleDB Wrapper.

 

????For now, right-click on the project name in the Solution Explorer and select Add Reference. This will bring up the Add Reference dialog box. Next select the COM tab and scroll down till you find a library named Microsoft ADO Ext. 2.8 for DLL and Security as shown in the screen-shot.

 

Click OK to add the reference to this dll. The library should show up as ADOX in your References in the Solution Explorer, if you're using VS.NET 2003. Else you can spot it under the References tab under Project Properties in VS.NET 2005.

 

????Next, we'll create our own class to encapsulate all database related routines. In the Solution Explorer, right-click on the solution name and select Add > Class. Name this class Database.We'll create a function called CreateDatabase( FileName ) that'll take the FileName as a parameter and create a blank database at the given location.

 

Here's the code for the function - it returns True or False depending on the success/failure in creating the database. We use the Create method which can be found under the ADOX.Catalog class to create the database. This method takes the standard OleDB connection string (as shown in oConnect) and creates a blank database with the file name passed to it as Data Source.

Public Function CreateDatabase(ByVal FileName As String) As Boolean

 

'Instantiate the ADOX Object

Dim ADOXCatalog As ADOX.Catalog = New ADOX.Catalog

Dim oConnect As String

 

'Setup the connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName

 

' Try creating the database

Try

 

'Create the database

ADOXCatalog.Create(oConnect)

 

Catch ex As System.Runtime.InteropServices.COMException

Catch ex As Exception

 

'Show error message and return failure

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

Return False

 

Finally

 

'Dispose the object

ADOXCatalog = Nothing

 

End Try

 

'Return success

Return True

 

End Function

linenums:0'>'Method to create a blank database Public Function CreateDatabase(ByVal FileName As String) As Boolean 'Instantiate the ADOX Object Dim ADOXCatalog As ADOX.Catalog = New ADOX.Catalog Dim oConnect As String 'Setup the connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName ' Try creating the database Try 'Create the database ADOXCatalog.Create(oConnect) Catch ex As System.Runtime.InteropServices.COMException Catch ex As Exception 'Show error message and return failure MsgBox(ex.Message & vbCrLf & ex.StackTrace) Return False Finally 'Dispose the object ADOXCatalog = Nothing End Try 'Return success Return True End Function

Create/Edit/Delete Tables

????Our next job is to define some tables in this database. For this we use the standard OleDB Data Provider.You need to have a little prior knowledge of SQL to understand this - although syntactically this is very simple. We start by defining a process called CreateTable(). We'll create a simple table named accessTest for demonstration purposes. This will have just two columns:

ID - which is of the Data Type COUNTER, which basically means an Auto Incrementing Integer field

Name - A TEXT field of length 50, which will store the name of a person.

Moreover, we'll set the ID field as the Primary Key.The code for this function follows:

Dim oConn As OleDbConnection

Dim oComm As OleDbCommand

Dim oConnect, oQuery As String

 

'Define connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password="

 

'Define the query string the creates the table

oQuery = "CREATE TABLE accessTest ( ID Counter," &

"Name TEXT(50) NOT NULL," &

"PRIMARY KEY(ID) )"

 

' Instantiate the connectors

oConn = New OleDbConnection(oConnect)

oComm = New OleDbCommand(oQuery, oConn)

 

'Try connecting and crate the table

Try

 

'Open the connection

oConn.Open()

 

'Perform the Non-Query

oComm.ExecuteNonQuery()

 

'Close the connection

oConn.Close()

 

Catch ex As OleDb.OleDbException

Catch ex As Exception

 

'Show error message and return failure

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

Return False

 

Finally

 

'Dispose the connector objects

If Not (oConn Is Nothing) Then

oConn.Dispose()

oConn = Nothing

End If

If Not (oComm Is Nothing) Then

oComm.Dispose()

oComm = Nothing

End If

 

End Try

 

'Return success

Return True

 

End Function

linenums:0'>Public Function CreateTable(ByVal FileName As String) As Boolean 'Define the connectors Dim oConn As OleDbConnection Dim oComm As OleDbCommand Dim oConnect, oQuery As String 'Define connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password=" 'Define the query string the creates the table oQuery = "CREATE TABLE accessTest ( ID Counter," & _ "Name TEXT(50) NOT NULL," & _ "PRIMARY KEY(ID) )" ' Instantiate the connectors oConn = New OleDbConnection(oConnect) oComm = New OleDbCommand(oQuery, oConn) 'Try connecting and crate the table Try 'Open the connection oConn.Open() 'Perform the Non-Query oComm.ExecuteNonQuery() 'Close the connection oConn.Close() Catch ex As OleDb.OleDbException Catch ex As Exception 'Show error message and return failure MsgBox(ex.Message & vbCrLf & ex.StackTrace) Return False Finally 'Dispose the connector objects If Not (oConn Is Nothing) Then oConn.Dispose() oConn = Nothing End If If Not (oComm Is Nothing) Then oComm.Dispose() oComm = Nothing End If End Try 'Return success Return True End Function

[tab]Next we've to define some methods to READ data from this table and return to us as a DataSet - which can be easily bound to a DataGrid to display the data directly on screen. Once again - we define a method called FetchData() to facilitate this. We use the standard connectors that we'd used in the above procedure, but along with that we introduce a new object called a DataAdapter - which is used to read the whole table and place the data into the DataSet.

Dim oConn As OleDbConnection

Dim oComm As OleDbCommand

Dim oData As OleDbDataAdapter

Dim resultSet As New DataSet

Dim oConnect, oQuery As String

 

'Define connection string

oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password="

 

'Query String

oQuery = "SELECT * FROM accessTest"

 

'Instantiate the connectors

oConn = New OleDbConnection(oConnect)

oComm = New OleDbCommand(oQuery, oConn)

oData = New OleDbDataAdapter(oQuery, oConn)

 

Try

 

'Open connection

oConn.Open()

 

'Fill dataset

oData.Fill(resultSet, "accessTest")

 

'Close connection

oConn.Close()

 

Catch ex As OleDb.OleDbException

Catch ex As Exception

 

'Show error message and exit

MsgBox(ex.Message & vbCrLf & ex.StackTrace)

 

Finally

 

'Dispose the connector objects

If Not (oConn Is Nothing) Then oConn.Dispose()

oConn = Nothing

If Not (oComm Is Nothing) Then oComm.Dispose()

oComm = Nothing

If Not (oData Is Nothing) Then oData.Dispose()

oData = Nothing

 

End Try

 

'Return results

Return resultSet

 

End Function

linenums:0'>Public Function FetchData(ByVal FileName As String) As DataSet 'Define the connectors Dim oConn As OleDbConnection Dim oComm As OleDbCommand Dim oData As OleDbDataAdapter Dim resultSet As New DataSet Dim oConnect, oQuery As String 'Define connection string oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";User ID=Admin;Password=" 'Query String oQuery = "SELECT * FROM accessTest" 'Instantiate the connectors oConn = New OleDbConnection(oConnect) oComm = New OleDbCommand(oQuery, oConn) oData = New OleDbDataAdapter(oQuery, oConn) Try 'Open connection oConn.Open() 'Fill dataset oData.Fill(resultSet, "accessTest") 'Close connection oConn.Close() Catch ex As OleDb.OleDbException Catch ex As Exception 'Show error message and exit MsgBox(ex.Message & vbCrLf & ex.StackTrace) Finally 'Dispose the connector objects If Not (oConn Is Nothing) Then oConn.Dispose() oConn = Nothing If Not (oComm Is Nothing) Then oComm.Dispose() oComm = Nothing If Not (oData Is Nothing) Then oData.Dispose() oData = Nothing End Try 'Return results Return resultSet End Function

So far we've created some basic routines to create database/table and fetch data. Now it's time to concentrate on the main interface and come up with a way to display the fetched data on the screen.For this we'll utilize a DataGrid control, as it can be easily bound to the fetched data. On the main form, drop a DataGrid. Also add a DataSet to the main form. We're going to link these two controls and whenever we update the dataset with any data from the database, the datagrid will reflect the changes on screen. Likewise, when data is modified on screen, the changes get reflected in the actual database.

Continued in Part II ...

Hi,

 

please give me the advise, how to display the fetch records set.......i am very much thanks full to you.....

 

please please help me.......

 

Apurba

Edited July 31, 2007 by apurba (see edit history)

[miCRoSCoPiC^eaRthLinG 0]

Have neglected continuing this series for a long time.. will come up with a record fetching / saving tutorial asap.

[vizskywalker 0]

M^E! Haven't heard from you in forever. Just looked over this, and am looking forward to your next update, even though I don't use access or VB.NET. Should be simple enough to convert the methods here to any other .NET language though, such as C# (my favorite).~Viz

[iGuest 3]

Sir/Madam,I have sum problems but at present.I know the how to create DataBase and Tables in MS Access 2000, But problem in table creation field yes/no looking as text. I want yes/no filed in as check box what can I write in table creation for this purpose.Thank.-K.Nageshwar Rao

[iGuest 3]

Part 2

VB.NET: MS-Access Interaction Tutorial (Part I)

 

Dear sir/ Madam

 

Part 1 was useful for my friend and I, and we'll waiting for part 2 to complete our exercises!

We'll be pleased if you would do us a favor and give us the rest of this tutorial.

 

Thanks in advance

 

Soudeh and Fariba

 

-Soudeh

[iGuest 3]

I am using vb.Net 2005 as a front-End and Ms-Access 2000 (Mdb) database as a backend. The problem which I am facing is that. Suppose I make a OledbTransaction and In that transaction I insert some rows into a table Named "Abc". After insertion is complete those Newly added rows are not available for further use unless I "commit" the transaction. I want that those rows should be available for further use after insertion without completing the transaction. (This happens in Sql-Server) what should I do for that. Waiting for your response. Thanking YouFrom : Kaushalendra Pandey -Kaushaleandra Pandey

[iGuest 3]

I really need Part 2

VB.NET: MS-Access Interaction Tutorial (Part I)

 

Hi,

That was really amazing, found myself understanding the code line by line. Could you please post the 2nd part or send it to me by email, that will be great.

 

Good to have people one can count on.

 

Forrest

 

PS : Thanks alot

 

-forrest

[iGuest 3]

VB.net and MSaccess

VB.NET: MS-Access Interaction Tutorial (Part I)

 

I'm using adodb and the following code is not picking up the entries in the database

 

If (textbox1.Text) = Rec.Fields("Username").Value then

Statement

 

How do I make it work?

 

-Clement

[iGuest 3]

Generating crystal report at run time

VB.NET: MS-Access Interaction Tutorial (Part I)

 

I want to generate crystal report which shows only those records which satisfies the given condition . How to do it ? I am using MS-Access as back end and vb.Net

 

-reply by Rajeshwar Rajemane

[iGuest 3]

How to retrieve the yes/no field value

VB.NET: MS-Access Interaction Tutorial (Part I)

 

Hi,

 

I have sum problems but at present.

I want to retrieve the yes/no field from the table and want to store its status in a checkbox in c#. What is the code for this. If I want to retrive a value to a textbox I will write as follows,

 

txtcustPO.Text=reader["custpo"].ToString();

 

But obviously this will not work for a checkbox. I.E the following code will not work.

Checkinvoiced.CheckState=reader["invoicetype"].ToString();

 

How can I write the code for this?

 

 

-question by Chaitra

[iGuest 3]

Seriously? VB.NET: MS-Access Interaction Tutorial (Part I)

Really? You put the functions up here and then just say "eh, I'll leave everyone hanging on how to implement these controls."

 Why do you even have this here?  I appreciate the effort, was low on explanation, but good on giving a path to follow, but why would you put a tutorial up here that you were too lazy to finish.  Yes, I'm picking at that, I've done tutorials myself in different applications but I never posted stuff that wasn't finished.  It only got posted when all segments were done.  All you've done here is waste my time now.

 Good failure, basically all you did was copy/paste code in here, add some text like your teaching, and then you said "I'm too lazy".

-reply by Discouraged

 

[iGuest 3]

can work with two databases tables at same time?VB.NET: MS-Access Interaction Tutorial (Part I)

I have an issue at job and I need to know, can VB.Net work with two databases tables at same time? If it is possible, how can I do that?

I need to load some items in a Combobox from a database table, but, each item must be different to each item in another database table (the second one Database is the current Database)

Maybe it is easier than I think, but I don't know how.

[iGuest 3]

Write an use my first DLLVB.NET: MS-Access Interaction Tutorial (Part I)

Hello!

How can I write my DLL in VB 2008 Express and use the functions in Access 2007?

I only getting error 453 

-question by Anders

[iGuest 3]

Can work with database on a serverVB.NET: MS-Access Interaction Tutorial (Part I)

 I am using vb.Net 2005 as a front-End and Ms-Access 2000 (Mdb) database as a backend.

I'm designing a quiz application  

The problem which I am facing is that.

I would like to connect my database to a client which has my application...

In such case what is the connection string to be used for connecting the database stored on other system...????

-question by Deepak