vba 从另一个 MDB 调用一个 MDB 中的函数
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1455829/
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
Calling function in one MDB from another MDB
提问by TonBill
We have developed a consolidation function that will be used by other processes and want to position the function in its own MDB (call it "remote") so that it can be referenced and called from "caller.mdb" when its needed. The function is designed to return an array and works great when executed called directly from within "remote." However, with "remote" properly referenced in the "caller" VBA project, when "caller" makes the call the function returns errors. We get a variety of errors such as
我们开发了一个合并函数,它将被其他进程使用,并希望将该函数定位在其自己的 MDB(称为“远程”)中,以便在需要时可以从“caller.mdb”中引用和调用它。该函数旨在返回一个数组,并且在直接从“远程”内部调用时执行时效果很好。但是,通过在“调用者”VBA 项目中正确引用“远程”,当“调用者”进行调用时,函数会返回错误。我们收到各种错误,例如
3078: Jet cannot find the input table or query
QUESTION. Within "remote", how does one properly set references to the db and its local objects (e.g. one table and several queries including INSERT and UPDATE queries)? CurrentDB is apparently not the answer; we have also experimented with the AccessObject and CodeData objects. "Remote" and "caller" currently reside on the same drive, so that wouldn't seem to be the problem.
题。在“远程”中,如何正确设置对数据库及其本地对象的引用(例如,一个表和多个查询,包括 INSERT 和 UPDATE 查询)?CurrentDB 显然不是答案;我们还试验了 AccessObject 和 CodeData 对象。“Remote”和“caller”目前驻留在同一个驱动器上,所以这似乎不是问题。
采纳答案by Marcand
Instead of CurrentDb you could use with CodeDb wich points to the mdb currently executing the code.
您可以使用指向当前执行代码的 mdb 的 CodeDb 代替 CurrentDb。
Set db = CodeDb
Set db = CodeDb
回答by David-W-Fenton
The way Access itself does this (with all the wizards, which are all programmed in Access), is to use Application.Run. It does mean the code you're calling has to be a function, though it doesn't matter what it returns. Application.Run requires no references, just a path:
Access 本身执行此操作的方式(所有向导均在 Access 中编程)是使用 Application.Run。这确实意味着您调用的代码必须是一个函数,尽管它返回什么并不重要。Application.Run 不需要引用,只需要一个路径:
Application.Run("MyCodeDatabase.MyFunction()")
Obviously, if the code database is not in the path that Access uses (which includes its own app folders (including the app-specific folders in the user's profile) and the folder where your main application front end is stored), you'll need to specify the full path.
显然,如果代码数据库不在 Access 使用的路径中(包括它自己的应用程序文件夹(包括用户配置文件中特定于应用程序的文件夹)和存储主应用程序前端的文件夹),您将需要指定完整路径。
Application.Run() is a function that returns a value, but it is typed as variant. This may or may not work with your array. It's not clear from the object browser whether or not the arguments are passed ByVal or ByRef, but if they are ByRef (which is what I'd expect), you might just pass the array in and let the function work on it and then use it after the code in the remote database has completed.
Application.Run() 是一个返回值的函数,但它的类型是变体。这可能适用于您的阵列,也可能不适用于您的阵列。从对象浏览器中不清楚参数是通过 ByVal 还是 ByRef 传递的,但是如果它们是 ByRef(这是我所期望的),则您可能只需传入数组并让函数对其进行处理,然后使用它在远程数据库中的代码完成后。
On the other hand, the arguments are probably variants, so there's not much difference between that approach and just using the structure returned by Application.Run().
另一方面,参数可能是变体,因此该方法与仅使用 Application.Run() 返回的结构之间没有太大区别。
回答by Bruce W. Moore
There are a number of differences and nuances to calling forms and functions through a reference in a another MDB or ADP. I have run into issues in both situations, and what you are referring to as the "remote" database, I refer to as a central library.
通过另一个 MDB 或 ADP 中的引用调用表单和函数存在许多差异和细微差别。我在这两种情况下都遇到了问题,你所说的“远程”数据库,我称之为中央库。
At my Tips and Tricks page at http://www.mooresw.com/tips.php, I have pages devoted to programatically changing references, getting Access to search for the referenced file instead of having a broken reference, and calling forms through a reference.
在http://www.mooresw.com/tips.php 的“技巧和窍门”页面上,我有一些页面专门用于以编程方式更改引用、获取访问以搜索引用的文件而不是损坏的引用,以及通过调用表单参考。
Programatically changing references is needed when you publish the database from the development environment to the user or production environment. When working in the development folder, it's fine for the program to have a reference to the central library directly, but we wouldn't want code that 20 users are running tying up the central library in our development area. (An MDB file opened through a reference gets locked just as though your users were opening it directly)
当您将数据库从开发环境发布到用户或生产环境时,需要以编程方式更改引用。在 development 文件夹中工作时,程序可以直接引用中央库,但我们不希望 20 个用户正在运行的代码占用我们开发区的中央库。(通过引用打开的 MDB 文件被锁定,就像您的用户直接打开它一样)
The situation of running a form in a central library (or "remote" database) where there are no links or tables can be tricky. In that situation I've chosen to open a connection to the "caller.mdb" using ADO code with a Jet connection string in the open event of the forms. Doing so provides the ability for the code in the form (or functions in the library) to gain access to the tables and queries in the calling mdb.
在没有链接或表格的中央库(或“远程”数据库)中运行表单的情况可能很棘手。在这种情况下,我选择在表单的打开事件中使用带有 Jet 连接字符串的 ADO 代码打开到“caller.mdb”的连接。这样做为表单中的代码(或库中的函数)提供了访问调用 mdb 中的表和查询的能力。
For further information, see my pages at the tips link above, and in particular, see:
有关更多信息,请参阅上面提示链接中的我的页面,特别是,请参阅:
http://www.mooresw.com/call_a_form_in_another_MDB_through_a_reference.php
http://www.mooresw.com/call_a_form_in_another_MDB_through_a_reference.php
which I believe is most relevant to your situation.
我认为这与您的情况最相关。
回答by Tony Toews
Marcand gave you the answer to your immediate question. There are other problems and irritations when it comes to using add-ins or referenced Access databases. See my Add-in Tips, Hints and Gotchaspage.
Marcand 给了您直接问题的答案。在使用加载项或引用的 Access 数据库时,还有其他问题和烦恼。请参阅我的插件提示、提示和陷阱页面。