Welcome Guest ( Log In | Register )



2 Pages V   1 2 >  
 Reply to this topicStart new topic
> VB.NET: MS-Access Interaction Tutorial (Part I)
Rating 3 V
miCRoSCoPiC^eaRt...
post May 15 2006, 03:01 PM
Post #1


PsYcheDeLiC dR3aMeR
Group Icon

Group: Admin
Posts: 2,242
Joined: 29-January 05
From: Nakorn Chaisri, Thailand
Member No.: 2,411
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:[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]We will start by creating a blank Windows Application project called MS-AccessTest.

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

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]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.
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



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


[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.
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


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 ...
Go to the top of the page
 
+Quote Post
apurba
post Jul 31 2007, 01:48 PM
Post #2


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 1
Joined: 31-July 07
Member No.: 23,731
QUOTE(miCRoSCoPiC^eaRthLinG @ May 15 2006, 08:31 PM) *
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:[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]We will start by creating a blank Windows Application project called MS-AccessTest.

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

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


[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.
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


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

This post has been edited by apurba: Jul 31 2007, 01:49 PM
Go to the top of the page
 
+Quote Post
miCRoSCoPiC^eaRt...
post Aug 1 2007, 03:48 AM
Post #3


PsYcheDeLiC dR3aMeR
Group Icon

Group: Admin
Posts: 2,242
Joined: 29-January 05
From: Nakorn Chaisri, Thailand
Member No.: 2,411
Have neglected continuing this series for a long time.. will come up with a record fetching / saving tutorial asap.
Go to the top of the page
 
+Quote Post
vizskywalker
post Aug 1 2007, 05:05 AM
Post #4


Techno-Necromancer
Group Icon

Group: Members
Posts: 1,018
Joined: 13-January 05
From: The Net
Member No.: 2,127
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
Go to the top of the page
 
+Quote Post
iGuest
post Dec 5 2007, 10:23 AM
Post #5


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
iGuest
post Jan 5 2008, 08:30 AM
Post #6


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
iGuest
post Jan 17 2008, 04:17 PM
Post #7


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
iGuest
post Jan 26 2008, 08:40 AM
Post #8


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
iGuest
post Jan 11 2008, 06:22 AM
Post #9


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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 You
From : Kaushalendra Pandey

-Kaushaleandra Pandey
Go to the top of the page
 
+Quote Post
iGuest
post Mar 16 2008, 06:37 AM
Post #10


Newbie [ Level 1 ]
Group Icon

Group: Members
Posts: 0
Joined: 1-November 07
Member No.: 25,869
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
Go to the top of the page
 
+Quote Post
« Next Oldest · .NET (VB, C# & J#) · Next Newest »
 

2 Pages V   1 2 >
Reply to this topicStart new topic

Collapse

> Similar Topics

Topics Topics
  1. How To Remove Bad Sectors Or Bad Clusters From HDD(17)
  2. 3ds Max Tutorial.(7)
  3. Creating A Game In Rpg Maker 2000/2003(18)
  4. Sharing Files In Windows Xp Home(15)
  5. C#.NET: Web Timer Control Tutorial(3)
  6. VB6-MS Access Question(8)
  7. Pre Loader Tutorial For Flash(6)
  8. Firefox 2(4)
  9. Wireless: Bypassing Mac Filtering(10)
  10. C++: Basic Classes(5)
  11. Phpbb - Installation Tutorial ( For Newbies Based On Astahost Cpane)l(5)
  12. Photoshop Tutorial: Signature(4)
  13. Cracking Wireless Access Point Password?(22)
  14. Php Tutorial: Making A Shoutbox(11)
  15. Html Basic Tutorial(9)
  1. How To Download Videos From Youtube(11)
  2. Linux Beginners - Tutorial On Editors In Linux.(3)
  3. C/c++ -gdb Linux Debug Tool(1)
  4. Access Denied As Admin On Xp, Services Troubles(4)
  5. Moving To Fedora 9(1)
  6. Design A Contact Form In Flex Part 1(0)
  7. Basic Html Tutorial(1)
  8. Psychostats(1)
  9. How To Understand A Database Schema(4)
  10. How To Design A Contact Form Part 3(0)
  11. Here Are Some Great Php Tutorial Sites(4)
  12. Cpanel Error When Loggin In...(5)
  13. Gimp Animation Tutorial(2)