none
Visual Basic 2008 connect to Ms Access RRS feed

All replies

  • There are several ways, using code such as below where you create an instance of this class in a form and use the methods. For instance LoadCustomers returns a DataTable that can be setup to a DataGridView and/or other controls via DataBinding. The other methods work off the DataTable just mentioned.

    If using mdb rather than accdb see the following to adjust the connection string.

    Note some of the syntax below will need to be adjusted as the code is more suited to VS2010 or higher. VS2008 is way back there.

    Public Class Sample2
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
    
        Private mExceptiom As Exception
        ''' <summary>
        ''' Each method when executed, if there is an exception thrown
        ''' then mException is set and can be read back via Exception property
        ''' only when a method returns false.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property Exception As Exception
            Get
                Return mExceptiom
            End Get
        End Property
        ''' <summary>
        ''' Container for data read in from a database table
        ''' </summary>
        ''' <returns></returns>
        Public Property CustomersDataTable As DataTable
        Public Function LoadCustomers() As Boolean
            If Not IO.File.Exists(Builder.DataSource) Then
                Return False
            End If
    
            Try
    
                CustomersDataTable = New DataTable
    
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT Identifier, CompanyName, ContactTitle FROM Customers"
    
                        cn.Open()
    
                        CustomersDataTable.Load(cmd.ExecuteReader)
                        CustomersDataTable.DefaultView.Sort = "CompanyName"
                        CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                    End Using
                End Using
    
                Return True
            Catch ex As Exception
                mExceptiom = ex
                Return False
            End Try
        End Function
        ''' <summary>
        ''' Delete a customer by their primary key
        ''' </summary>
        ''' <param name="CustomerId"></param>
        ''' <returns></returns>
        Public Function DeleteCustomer(ByVal CustomerId As Integer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@Identifier", CustomerId)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        Public Function UpdateCustomer(ByVal CustomerId As Integer, ByVal CompanyName As String, ByVal ContactName As String) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "UPDATE Customer SET CompanyName = @CompanyName, ContactName = @ContactName WHERE Identifier = @Identifier"
    
                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName)
                        cmd.Parameters.AddWithValue("@ContactName", ContactName)
                        cmd.Parameters.AddWithValue("@Identifier", ContactName)
    
                        cn.Open()
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            Success = True
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
            End Try
    
            Return Success
    
        End Function
        ''' <summary>
        ''' Add new row, if successful provide the new record's primary key
        ''' </summary>
        ''' <param name="Name"></param>
        ''' <param name="ContactName"></param>
        ''' <param name="Identfier"></param>
        ''' <returns></returns>
        Public Function AddNewRow(ByVal Name As String, ByVal ContactName As String, ByRef Identfier As Integer) As Boolean
            Dim Success As Boolean = True
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customers (CompanyName,ContactName) Values(@CompanyName,@ContactName)"
    
                        cmd.Parameters.AddWithValue("@CompanyName", Name)
                        cmd.Parameters.AddWithValue("@ContactName", ContactName)
    
                        cn.Open()
                        Dim Affected As Integer = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            Identfier = CInt(cmd.ExecuteScalar)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Success = False
                mExceptiom = ex
            End Try
    
            Return Success
    
        End Function
    End Class
    
    

    There are TableAdapters, at first they look like "this is the way to go" yet as you get more complex operations many developers end up frustrated with them.

    https://msdn.microsoft.com/en-us/library/fxsa23t6.aspx



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, November 1, 2017 9:02 AM
    Moderator
  • As Karen mentioned, there are a few different methods. The tutorial below should help as well:

    http://www.homeandlearn.co.uk/NET/nets12p1.html


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, November 1, 2017 2:01 PM
  • Hi Yasser917,

    About connecting to MS Access database, you can take a look:

    https://www.599cd.com/tips/vbdotnet/140826_ConnectToAccess/

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 2, 2017 2:32 AM
    Moderator