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

CODE

'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

CODE

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

CODE

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

QUOTE (miCRoSCoPiC)

VB.NET - MS-Access Interaction Tutorial

[tab][/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:

1. Create a MS-Access Database
   
2. Create/Edit/Delete Tables in it
   
3. Access it and Add/Edit/Update data in those tables
   

all with VB.NET.
Note:[tab][/tab]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.

[tab][/tab]We will start by creating a blank Windows Application project called MS-AccessTest.

Creating a MS-Access Database
[tab][/tab]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.

[tab][/tab]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.

[tab][/tab]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.

Create/Edit/Delete Tables
[tab][/tab]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:

1. ID - which is of the Data Type COUNTER, which basically means an Auto Incrementing Integer field
   
2. 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:


[tab][/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.


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 ...
Link: view Post: 78192