Visual Basic 2008连接到Ms Access [英] Visual Basic 2008 connect to Ms Access

问题描述

任何人都可以分享从VB 2008到Ms Excess的连接代码

Anybody can share the code for connection from VB 2008 with Ms Excess

推荐答案

有几种方法，使用如下代码在表单中创建此类的实例并使用方法。例如，LoadCustomers返回一个DataTable，可以通过DataBinding设置为DataGridView和/或其他控件。其他
方法可以解决上面提到的DataTable。

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.

如果使用mdb而不是accdb，请参阅
以下调整连接字符串。

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

注意下面的一些语法需要调整，因为代码更适合VS2010或更高版本。 VS2008就在那里。

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

有TableAdapters，起初他们看起来像"这是要走的路"。然而，当你得到更复杂的操作时，许多开发人员最终会对它们感到沮丧。

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

这篇关于Visual Basic 2008连接到Ms Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！