从 Excel VBA 打开访问
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16312375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Opening Access from Excel VBA
提问by Bradley Carrico
Edit: The answer to this question can be found within the comments of the accepted answer.
编辑：这个问题的答案可以在已接受答案的评论中找到。
I am attempting to open an Access database from a button click within my excel file. I currently have this code:
我试图通过单击我的 excel 文件中的按钮打开 Access 数据库。我目前有这个代码：
Private Sub bttnToAccess_Click()
Dim db As Access.Application
Set db = New Access.Application
db.Application.Visible = True
db.OpenCurrentDatabase "C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb"
End Sub
This seems to work briefly and then Access shuts down almost immediately. If it matters, the Access file has an AutoExec macro that runs through a few tests itself on open.
这似乎只是短暂地工作，然后 Access 几乎立即关闭。如果重要的话，Access 文件有一个 AutoExec 宏，它在打开时自己运行一些测试。
采纳答案by Pieter Geerkens
Don't try to open the Access application then; just create a connection object using one of the Data Access technologies: - OLE-DB or - ODBC.
然后不要尝试打开 Access 应用程序；只需使用以下数据访问技术之一创建连接对象： - OLE-DB 或 - ODBC。
Google "ODBC Connection strings" or "OLE-DB Connection Strings" to get details depending on your particular configuration (and Access filetype).
谷歌“ODBC 连接字符串”或“OLE-DB 连接字符串”以根据您的特定配置（和 Access 文件类型）获取详细信息。
Probably ADODB is the easiest current library to use for your data access.
ADODB 可能是当前最容易用于数据访问的库。
Update:Try Importing the data from Access then using the Data -> From Accesswizard. Yu can always use the Macro recoding facility to automatically generate some VBA code for you, that will create some infrastructure for you; I use this regularly when exploring new portions of the VBA object model.
更新：尝试从 Access 导入数据，然后使用数据 -> 从 Access向导。Yu 可以随时使用宏重新编码工具为您自动生成一些 VBA 代码，这将为您创建一些基础设施；在探索 VBA 对象模型的新部分时，我经常使用它。
Update - Final resolution of problem, from comments below
That may be because the variable goes out of scope; move the declaration of dboutside the function, to module level
更新 - 问题的最终解决，来自下面的评论
这可能是因为变量超出了范围；将db函数外部的声明移至模块级别
回答by HansUp
The code started Access by creating an application instance assigned to an object variable. At the end of the procedure, the variable went out of scope so Access shut down.
该代码通过创建分配给对象变量的应用程序实例来启动 Access。在过程结束时，变量超出范围，因此 Access 关闭。
You accepted an answer to use a module-level variable for the Access application instance. In that case, Access remains running after the procedure ends. However if the user exits Excel, Access will close down too.
您接受了为 Access 应用程序实例使用模块级变量的答案。在这种情况下，Access 在过程结束后仍保持运行。但是，如果用户退出 Excel，Access 也会关闭。
If the goal is to start Access and leave it running until the user decides to close it, just start Access directly without assigning the application instance to an object variable (Set db = New Access.Application). That dbvariable would be useful if your Excel code needed it for other purposes. However, it's actually only used to open the db file.
如果目标是启动 Access 并保持运行直到用户决定关闭它，只需直接启动 Access，无需将应用程序实例分配给对象变量 ( Set db = New Access.Application)。db如果您的 Excel 代码需要它用于其他目的，该变量将非常有用。但是，它实际上仅用于打开db文件。
You can use the Runmethod of WScript.Shellto open your db file in an Access session.
您可以使用 的Run方法WScript.Shell在 Access 会话中打开您的 db 文件。
Private Sub bttnToAccess_Click()
Const cstrDbFile As String = "C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb"
Dim objShell As Object
Set objShell = CreateObject("WScript.Shell")
objShell.Run cstrDbFile
Set objShell = Nothing
End Sub
回答by Derek Sturdy
I know this is an old thread, but you will get this error in Excel VBA if you are trying to open an Access database, but you do not have two specific References clicked. (Tools, References on the VBA Editor screen). You need to click 'Microsoft Access 15.0 Object Library' and 'Microsoft ActiveX Data Objects 6.1 Library'.
我知道这是一个旧线程，但是如果您尝试打开 Access 数据库，但您没有单击两个特定的引用，您将在 Excel VBA 中收到此错误。（VBA 编辑器屏幕上的工具、参考）。您需要单击“Microsoft Access 15.0 Object Library”和“Microsoft ActiveX Data Objects 6.1 Library”。
回答by Ant
Remove the Newdeclaration then it works
删除New声明然后它就可以工作了
回答by agcala
Actually it is pretty straightforward:
其实很简单：
Private Sub bttnToAccess_Click()
db = DBEngine.OpenDatabase("C:\Users\wcarrico\Desktop\wcarrico-CapstoneFinalSubmission.accdb")
End Sub
For this to work you need to declare db as Database at the Module level.
为此，您需要在模块级别将 db 声明为数据库。
Dim db As Database 'Requires reference to the Microsoft
'Access Database Engine Object Library
