如何在Excel VBA中创建MS Access运行时的对象 [英] How to Create object of MS Access Runtime in Excel VBA

问题描述

当我在Excel VBA中创建对象时，我的Microsoft Access Runtime不是完整版的Microsoft Access

Set objAccess = CreateObject("Access.Application")

那时候我正在

错误429"ActiveX组件无法创建对象."

建议如何创建对象?

解决方案

我不确定此信息是否仍与OP相关，但可能会帮助其他寻求解决方案的人(如我):

在简单路线的情况下

Dim AccApp as Object
Set AccApp = CreateObject("Access.Application")

不起作用(例如，因为只有Access的运行时版本可用)，以下路线似乎可行:

Const PathToDBFile as String = "W:\here\Your\DB\lies.accdb"
Const PathToAccess as String = "C:\Program files\YourOfficeVersion\MSACCESS.EXE"
Dim ShellCmd as String
' Piece together the parts (yes, the quotes are necessary in case there are spaces in the paths) 
ShellCmd = """" & PathToAccess & """ """ & PathToDBFile & """"
' Execute the command in the shell
VBA.Shell ShellCmd
' Now GetObject can return the newly created instance of Access
Dim AccApp as Object
Set objAcc = GetObject(PathToDBFile)

(源)

此代码只是展示基本步骤的基本内容.可能要确保没有一个正在运行的Access实例.另外，我还没有弄清楚如何在不同系统上可靠地获取MSAccess.exe的路径.但是当我在仅安装了运行时版本的系统上尝试时，以上内容对我有用. (我能够从AccApp.Run "MyFunction"获得正确的回报.)

I have Microsoft Access Runtime not full version of Microsoft Access, When i create object in Excel VBA

Set objAccess = CreateObject("Access.Application")

That time i am getting

Error 429 "ActiveX component can't create object."

Suggest how to create object?

解决方案

I'm not sure whether this information is still relevant to OP, but it might help out others (like me) who were looking for a solution:

In cases where the simple route

Dim AccApp as Object
Set AccApp = CreateObject("Access.Application")

doesn't work (e.g. because only the Runtime Version of Access is available), the following route seems to work:

Const PathToDBFile as String = "W:\here\Your\DB\lies.accdb"
Const PathToAccess as String = "C:\Program files\YourOfficeVersion\MSACCESS.EXE"
Dim ShellCmd as String
' Piece together the parts (yes, the quotes are necessary in case there are spaces in the paths) 
ShellCmd = """" & PathToAccess & """ """ & PathToDBFile & """"
' Execute the command in the shell
VBA.Shell ShellCmd
' Now GetObject can return the newly created instance of Access
Dim AccApp as Object
Set objAcc = GetObject(PathToDBFile)

(Source)

This code is only the bare bones to show the essential steps. One likely wants to make sure there isn't already an instance of Access running. Also I've not yet worked out how I can reliably get the path to the MSAccess.exe on different systems. But the above worked for me when I tried on a system with only the Runtime Version installed. (I was able to get the correct return from AccApp.Run "MyFunction".)

这篇关于如何在Excel VBA中创建MS Access运行时的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！