connecting ms access and vb,net

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,

Posted in: