如何从其他数据库打开宏 - VBA、MS Access 2003
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19906003/
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
How to open macros from other databases - VBA, MS Access 2003
提问by Katana24
I've been tasked with creating an Access 2003 application that will act as a focal point for all other databases (6 in total).
These 6 databases will each contain a macro used to build data bound for a table called DispenseExport.
我的任务是创建一个 Access 2003 应用程序，它将充当所有其他数据库（总共 6 个）的焦点。这 6 个数据库将各自包含一个宏，用于为名为DispenseExport.
Originally each of these databases had their own version of this table but now we have to have them all write to the one application - which I've affectionately codednamed Omega.
最初，这些数据库中的每一个都有自己的该表版本，但现在我们必须让它们全部写入一个应用程序 - 我亲切地将其编码为Omega。
Omega will contain the DispenseExporttable that all the others will write to. It will also be the database that calls the macros from the others to write to it.
Omega 将包含DispenseExport所有其他人将写入的表。它也将是从其他数据库调用宏来写入它的数据库。
So - what is the best way to accomplish this?
那么 - 实现这一目标的最佳方法是什么？
I already have a sample sub to call a macro from another database (and works) but the problem here is that it opens the database as any normal user would - Omega will sit on a server and needs to bypass this - by possibly using the SHIFT-KEYaccess method, if best?
我已经有一个示例 sub 来从另一个数据库调用宏（并且有效），但这里的问题是它像任何普通用户一样打开数据库 - Omega 将位于服务器上并需要绕过它 - 通过可能使用SHIFT -KEY访问方法，如果最好？
How do I do that programmatically in VBA, assuming it's the best way to do so?
假设这是最好的方法，我如何在 VBA 中以编程方式执行此操作？
Here's what I have so far:
这是我到目前为止所拥有的：
Option Compare Database
Option Explicit
'Sub to call a macro from another database
Public Sub CallMacro()
On Error GoTo ErrHandler:
Debug.Print "Opening Macro"
'/x tells the database to run the macro as soon as it is opened
Call Shell("msaccess.exe C:\path-to-database\database.mdb /x mcrTestCall", 0)
Debug.Print "Completed Macro"
ErrHandler:
If Err.Number <> 0 Then
'Create a message box with the error number and description
MsgBox ("Sorry but error number " & Err.Number & " occurred; " & vbCrLf & Err.Description)
End If
End Sub
UPDATE
更新
Still haven't found the answer but believe that I'm closing in. I need to find out how to emulate HOLDING DOWN SHIFT- any ideas?
仍然没有找到答案，但相信我正在接近。我需要找出如何模拟HOLDING DOWN SHIFT- 有什么想法吗？
Thanks
谢谢
采纳答案by jacouh
This will avoid shell call (Access 2007, also Access 2003?):
这将避免 shell 调用（Access 2007，还有 Access 2003？）：
'
' variables:
' strDbName: database filename
' strMacro: macro name
'
Sub CallMacro()
'
Dim strDbName, strMacro
Dim objApp
'
strDbName = "C:\path-to-database\database.mdb"
strMacro = "mcrTestCall"
'
Set objApp = CreateObject("Access.Application")
'
objApp.OpenCurrentDatabase strDbName
'
' Run Macro:
'
objApp.DoCmd.RunMacro strMacro
'
objApp.Quit
Set objApp = Nothing
'
End Sub
To skip startup form or AutoExec, there is no simple solution, but we have a workaround in http://access.mvps.org/access/api/api0068.htm, by simulating the Shift key, using API.
要跳过启动表单或 AutoExec，没有简单的解决方案，但我们在http://access.mvps.org/access/api/api0068.htm 中有一个解决方法 ，通过使用 API 模拟 Shift 键。
回答by Jim Snyder
There is a cleaner way to do this without opening the remote database. Build a query within that database that does what you want, then call it from the other database without ADO, DAO or opening it. Below is a link explaining how: Remote QueriesSince links go away, the gist of it is this: 1. Build the query you want to call to return what you need in the other database 2. In the remote calling database, open a query without selecting a table (I use Query Design) 3. Right click on the query background and select 'Properties' 4. Replace the Database setting of (Current) with the full path to the database with the query 5. Build a query to call the original query as the datasource 6. Output the data by a "SELECT INTO" or DoCmd.TransferSpreadsheet, etc
有一种更简洁的方法可以在不打开远程数据库的情况下执行此操作。在该数据库中构建一个执行您想要的操作的查询，然后从其他数据库调用它，无需 ADO、DAO 或打开它。下面是一个解释方法的链接： 远程查询由于链接消失，其要点是： 1. 构建您要调用的查询以返回其他数据库中所需的内容 2. 在远程调用数据库中，打开一个查询不选择表（我使用查询设计） 3. 右键单击查询背景并选择“属性” 4. 将（当前）的数据库设置替换为带有查询的数据库的完整路径 5. 构建要调用的查询将原始查询作为数据源 6. 通过“SELECT INTO”或 DoCmd.TransferSpreadsheet 等方式输出数据
