Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficulty opening Access from within Excel
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, -- Mary S. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficulty opening Access from within Excel
Try this
MyAccess.AutomationSecurity = msoautomationsecurityLow MyAccess.OpenCurrentDatabase ("\\server\folder\xyz.mdb") MyAccess.AutomationSecurity = msoautomationsecuritybyUI (I think that's it) "Mary S." wrote: 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, -- Mary S. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Difficulty opening Access from within Excel
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, -- Mary S. "Barb Reinhardt" wrote: Try this MyAccess.AutomationSecurity = msoautomationsecurityLow MyAccess.OpenCurrentDatabase ("\\server\folder\xyz.mdb") MyAccess.AutomationSecurity = msoautomationsecuritybyUI (I think that's it) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficulty opening files with Excel | Excel Discussion (Misc queries) | |||
Opening an ACCESS db from the VBE in EXCEL | Excel Worksheet Functions | |||
difficulty opening userform | Excel Programming | |||
Opening Excel Application difficulty | Excel Discussion (Misc queries) | |||
Opening Excel via Access | Excel Programming |