Excel VBA 连接到 Access 2010

Excel VBA Connection to Access 2010

提问by JKK

I have a class that's handling my connection to an Access 2003 database. I would like to setup the same thing only for Access 07/10 .accdb files. Any help is appreciated! Thank you!

我有一个类正在处理我与 Access 2003 数据库的连接。我只想为 Access 07/10 .accdb 文件设置相同的内容。任何帮助表示赞赏！谢谢！

Here's a list of my references and a copy of the class object

这是我的引用列表和类对象的副本

References:

参考：

• Microsoft Access 14.0 Object Library
• Microsoft DAO 3.6 Object Library

• Microsoft Access 14.0 对象库
• Microsoft DAO 3.6 对象库

ConnectionClass:

连接类：

Option Explicit

Private Const DbFile = "\server\folders\Report.mdb"
Dim OpenConn As DAO.Database
Dim ObjAccess As Object

Private Sub Class_Initialize()
    On Error Resume Next
    Set OpenConn = DAO.OpenDatabase(DbFile)
    If Err.Number = 3024 Then MsgBox "Check connection string in the VBA StaticClass object", vbOKOnly

    Set ObjAccess = CreateObject("Access.Application")
    ObjAccess.Visible = False
    ObjAccess.OpenCurrentDatabase (DbFile)
End Sub

Public Function runSQL(ByVal sql As String) As Recordset
    Set runSQL = OpenConn.OpenRecordset(sql)
End Function

Public Function runVolumeReport(ByVal inMacro As String)
    ObjAccess.DoCmd.RunMacro inMacro
End Function

Public Function closeResources()
    Set ObjAccess = Nothing
    OpenConn.Close
End Function

采纳答案by HansUp

There is an issue in Class_Initialize.

中存在问题Class_Initialize。

On Error Resume Next
Set OpenConn = DAO.OpenDatabase(DbFile)
If Err.Number = 3024 Then MsgBox "Check connection string in the VBA StaticClass object", vbOKOnly

Because of On Error Resume Next, any error other than 3024 ("Could not find file") will pass silently and OpenConn will not be set as you intend. Later when you attempt to use OpenConn, you will trigger another error. And, in a comment, you reported you do get another error with this line:

由于On Error Resume Next，除 3024（“找不到文件”）以外的任何错误都将静默传递，并且 OpenConn 不会按您的意图设置。稍后当您尝试使用 时OpenConn，您将触发另一个错误。并且，在评论中，您报告说您确实在此行中遇到了另一个错误：

Set runSQL = OpenConn.OpenRecordset(sql)

Unfortunately, due to On Error Resume Next, we don't know why OpenDatabasefailed leaving OpenConnunset. Since ObjAccessseems to work as an Access application object, you could try setting OpenConnto ObjAccess.CurrentDb.

不幸的是，由于On Error Resume Next，我们不知道为什么OpenDatabase未OpenConn设置未设置。由于ObjAccess似乎作为 Access 应用程序对象工作，您可以尝试设置OpenConn为ObjAccess.CurrentDb.

Private Sub Class_Initialize()
    Set ObjAccess = CreateObject("Access.Application")
    ObjAccess.Visible = False
    ObjAccess.OpenCurrentDatabase DbFile
    Set OpenConn = ObjAccess.CurrentDb
End Sub

OTOH, you may be able to dispense with OpenConnentirely if you change your runSQLfunction like this ...

OTOH，OpenConn如果你runSQL像这样改变你的功能，你可能可以完全免除......

Public Function runSQL(ByVal sql As String) As Recordset
    'Set runSQL = OpenConn.OpenRecordset(sql) '
    Set runSQL = ObjAccess.CurrentDb.OpenRecordset(sql)
End Function

回答by Peter

Imports System.Data.OleDb

Public Class Form1
    Dim strSQL As String
    Dim ds As New DataSet
    Dim strConnection As String
    Dim DBconnection As New OleDbConnection
    Dim oledbAdapter As New OleDbDataAdapter
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\Informatica\April - Juni\Acces\db-games.accdb"
        DBconnection = New OleDbConnection(strConnection)
        strSQL = "SELECT * from tbl_games"

        Try
            DBconnection.Open()
            oledbAdapter = New OleDbDataAdapter(strSQL, DBconnection)
            oledbAdapter.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        DBconnection.Close()
    End Sub
End Class

Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\Informatica\Acces\db_Games.accdb;Persist Security Info=False") Dim cmd As New OleDbCommand

Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\Informatica\Acces\db_Games.accdb;Persist Security Info=False") Dim cmd As New OleDbCommand

    con.Open()

    cmd.Connection = con

    cmd.CommandText = "INSERT INTO tbl_gerne(Omschrijving) VALUES('adventure')"
    cmd.ExecuteNonQuery()

    con.Close()

回答by SeanC

One way to open a accdb (SQL Server) table is this:

打开 accdb (SQL Server) 表的一种方法是：

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "select SomeStuff from SomeTable"

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSQL ' you can put in the SQL directly, 
                         ' but I find the string easier to manipulate away from the .CommandText
Set rs = cmd.Execute

My References (Access 2010):

I think the critical one you would need to add would be the Microsoft ActiveX Data Objects X.X Library

我的参考资料（Access 2010）：

我认为您需要添加的关键是 Microsoft ActiveX Data Objects XX Library