Difficulty opening Access from within Excel

[Mary S.]

I am trying to open an Access database from within Excel using VBA. Here is
my code:

Dim MyAccess As Access.Application
Set MyAccess = CreateObject("Access.Application")
MyAccess.Visible = True
MyAccess.OpenCurrentDatabase ("\\server\folder\xyz.mdb")

The last line of code triggers the following error message:

Run-time error '7866'
Microsoft Office Access can't open the database because it is missing, or
opened exclusively by another user.

The database is not, in fact, opened exclusively by another user. The error
message appears when the Access macro security level is set to medium. The
error message does not appear when the Access macro security level is set to
low. Is there any way to avoid this error, or bypass the error and still
open the database, without changing the Access macro security level to low?
Thanks,

 

[Barb Reinhardt]

Try this

MyAccess.AutomationSecurity = msoautomationsecurityLow
MyAccess.OpenCurrentDatabase ("\\server\folder\xyz.mdb")
MyAccess.AutomationSecurity = msoautomationsecuritybyUI

(I think that's it)

 

[Mary S.]

Barb,

Thanks for your reply. I tried the code you suggested below, but I'm still
getting the same error message. I think the problem is that unsafe
expressions are not blocked. Whenever I have the macro security level in
Access set to medium and I open a database, I get the message: Security
Warning: Unsafe expressions are not blocked. Do you want to block unsafe
expressions? [Yes] [No] [Help]. If I click [Yes], I get another message:
Microsoft Access cannot change the registry setting that controls whether or
not unsafe expressions are blocked. Your account is not authorized to change
this registry setting. To change this setting, contact your system
administrator. [OK] [Help]

The code you suggested does work if the macro security level in Access is
set to medium and unsafe expressions are blocked. However, on the network
where these files exist, users typically do not have administrator privileges
on the computers they are using. Also, the default setting seems to be to
not block unsafe expressions, and thus the typical user will see this message
(and will be unable to change whether unsafe expressions are blocked). I
believe that for the typical user to successfully run this Excel macro he/she
would have to manually set the Access macro security level to low. The code
you suggested does not allow the Access database to open if unsafe
expressions are not blocked.

Can anyone think of a way to bypass this? Please respond with any ideas you
may have. If it is not possible, I'm going to change my approach and put my
VBA code into the Access database and have it generate the Excel file that I
want to create.

Thanks,