????[/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 DatabaseCreate/Edit/Delete Tables in itAccess it and Add/Edit/Update data in those tablesall 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
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 fieldName - 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:
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.
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.....
VB.NET: MS-Access Interaction Tutorial (Part I)
in Programming
Posted · Edited by apurba (see edit history) · Report reply
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