Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 20th, 2012, 08:36 AM
Registered User
Points: 10, Level: 1
Points: 10, Level: 1 Points: 10, Level: 1 Points: 10, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default VB.Net & MS-Access Connection

can anybody help me to write code in VB.Net to connect with MS-Access,
simple add, edit & delete code???
Reply With Quote
  #2 (permalink)  
Old February 22nd, 2012, 06:42 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

duplicate post...

Pl check the following link
VB.Net & MS-Access Connection
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
Reply With Quote
  #3 (permalink)  
Old February 22nd, 2012, 07:26 AM
gjgriffith's Avatar
Wrox Author
Points: 503, Level: 8
Points: 503, Level: 8 Points: 503, Level: 8 Points: 503, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 109
Thanks: 5
Thanked 14 Times in 14 Posts
Default How to connect to an Access Database using VB.NET

Hello Mrugesh,

Thank you so much for posting your questions about Access here! I saw your post and wanted to provide some help. Specifically, you asked:

Quote:
can anybody help me to write code in VB.Net to connect with MS-Access, simple add, edit & delete code???
To answer your questions, there are a number of different methods to "connect with MS-Access", but I'll assume for this conversation that you want to connect to an MS Access database and execute SQL statements against it. This is very easy to do in VB.NET and in the following code is a simple example of how you can create a VB.NET class to execute SQL statements against an Access database:

Code:
Public Class DatabaseConnector

    ''' <summary>
    ''' Returns an OLEDB connection string to an Access 2010 database
    ''' </summary>
    ''' <returns>The OLEDB connection string to the Access 2010 database</returns>
    Private Function GetConnectionString() As String

        ' Create the Connection string
        Dim strConnection As String
        strConnection = _
            "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=C:\Test\DatabaseFile.accdb;" & _
            "User ID=Admin;Password=;"

        ' Return the Conntection string
        GetConnectionString = strConnection

    End Function ' End of: Private Function GetConnectionString() As String


    ''' <summary>
    ''' Allow the user to execute non-record returning queries
    ''' </summary>
    ''' <param name="SqlCode">The SQL Statement to execute against the database</param>
    ''' <returns>True if successful, otherwise False</returns>
    Public Function RunSqlNonQuery(SqlCode As String) As Boolean
        On Error GoTo HandleErrors

        Dim bResult As Boolean
        Dim cmd As OleDb.OleDbCommand

        ' Create the OLEDB Command object
        cmd = New OleDb.OleDbCommand(SqlCode)

        ' Open the Connection
        cmd.Connection = New OleDb.OleDbConnection(GetConnectionString())
        cmd.Connection.Open()

        ' Execute the SQL Statement
        cmd.ExecuteNonQuery()

        ' It looks like we've succeeded - return True
        bResult = True

ExitFunction:

        ' Close the connection
        If (Not IsNothing(cmd.Connection)) Then
            If (cmd.Connection.State <> ConnectionState.Closed) Then
                cmd.Connection.Close()
            End If
        End If

        ' Return the result and exit
        RunSqlNonQuery = bResult
        Exit Function

HandleErrors:

        ' Handle any errors here...
        MsgBox("An error was raised!" & vbNewLine & "Message: " & Err.Description, MsgBoxStyle.Critical, "Error")
        Err.Clear()
        bResult = False ' Return failure
        Resume ExitFunction

    End Function ' End of: Public Function RunSqlNonQuery(SqlCode As String) As Boolean

End Class  '  End of: Public Class DatabaseConnector
So, in the above code, notice the "GetConnectionString()" function just returns a connection string to an Access 2010 database. Notice that this connection string provides the full file path to the database file itself, specified through the "Data Source" parameter. Then notice the "RunSqlNonQuery()" function. This function will allow you to execute non-record returning SQL queries against the database. So then to use this DatabaseConnector class to connect to an Access database and execute SQL statements against it, you could create the following VB.NET code in a form:

Code:
Dim dbConnector As New DatabaseConnector
Dim strSql As String

' Create an INSERT SQL Statement
strSql = _
    "INSERT INTO [Table1] ( [Field1], [Field2] ) " & _
    "VALUES (""Field 1 Value"", ""Field 2 Value""); "

' Execute the SQL Statement against the Access database
dbConnector.RunSqlNonQuery(strSql)
So, in this above code, you just create a new instance of the "DatabaseConnector" class, create a SQL Statement (that in this case is non-record returning, like an INSERT or UPDATE statement), and then call the "RunSqlNonQuery()" method. Of course, if you wanted to run just a SELECT statement, you would have to create another method in the DatabaseConnector class that returns a DataReader (or something like that), but hopefully this example provides you with enough information to get you started. So, I hope all of this code helps, but please let me know if you have any other questions about this and I'll do what I can to help.

Also, I should mention that Chapter 20: "Working with .NET" in the Access 2010 Programmer's Reference book talks about the various methods for using Microsoft Access with .NET, which provides lots of different examples of what you can do with .NET and Access. However, the code examples are actually in C# (and NOT VB.NET), but the basic objects are all the same and should at least provide a little insight into how things work.

Sincerely,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->

Last edited by gjgriffith; February 23rd, 2012 at 03:24 AM. Reason: spelling
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access With VB.NET N4T3.T4NN3R ADO.NET 0 October 9th, 2008 02:47 PM
Vb.net 2005 using MS Access johnnys14 SQL Language 0 July 10th, 2008 02:14 PM
VB.NET 2005 to MS Access h@ckerz VB Databases Basics 5 March 6th, 2007 11:22 AM
VB. NET MS Access 2003 OLEDB connection mike.cody ASP.NET 1.0 and 1.1 Basics 1 February 22nd, 2006 08:20 PM
VB.NET in Foreground and MS Access in Background szms VB.NET 2002/2003 Basics 0 April 13th, 2005 12:59 PM



All times are GMT -4. The time now is 05:40 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.