Solved How to close database when macro function errors (1 Viewer)

[sumdumgai]

Hello. We're using Access 365. I have an 'autoexec' function that, as soon as the database is opened, checks the user name against a user/password table in the database for security purposes. I just found a hole. If the user does not have macros enabled, the autoexex function errors/stops with SingleStep error 2001. A dialog box opens and if the user simply clicks the 'Stop All Macros' button, the database remains open to the user, so the user/password table is never checked.

Is there a way to trap the 'Stop All Macros' button click? Would 'On error handling' work? How can I force the database to close if this happens; i.e., users macros are not enabled.

Thanks.

 

[sumdumgai]

Figured it out. I added an 'Autoexec' macro ahead of the security function. The Autoexec macro checks If [CurrentProject].[IsTrusted]=False. If it is, which will be the case if macros are not enabled, the database is immediately closed without running the security function.

 

[CJ_London]

interesting - so you are saying if macros are not enabled, the autoexec macro still runs?

 

[sumdumgai]

Yes. The first thing that the autoexec macro does is:
"If [CurrentProject].[IsTrusted]=False" Then
MessageBox 'Tell user macros are probably disabled'
RunMenuCommand "CloseDatabase".

If autoexec passes this test, then the security VBA function is executed.

 

[theDBguy]

Yes. The first thing that the autoexec macro does is:
"If [CurrentProject].[IsTrusted]=False" Then
MessageBox 'Tell user macros are probably disabled'
RunMenuCommand "CloseDatabase".

If autoexec passes this test, then the security VBA function is executed.

Just curious, are you distributing a ACCDR front end to your users?

 

[sumdumgai]

No. Front end DB on shared network drive.

 

[theDBguy]

No. Front end DB on shared network drive.

Hi. Thanks for the clarification. Good luck with your project.

 

[isladogs]

IsTrusted is nothing to do with whether macros are allowed or blocked.
It checks whether the file is in a trusted location or is a trusted document

CurrentProject.IsTrusted property (Access)

Office VBA reference topic

docs.microsoft.com

It is true that the autoexec macro will still run even if the file isn't trusted but only certain 'safe actions' will be permitted.
Potentially unsafe macro actions will be blocked

 

[CJ_London]

Learn something new every day

 

[sumdumgai]

IsTrusted is nothing to do with whether macros are allowed or blocked.
It checks whether the file is in a trusted location or is a trusted document

CurrentProject.IsTrusted property (Access)

Office VBA reference topic

docs.microsoft.com

It is true that the autoexec macro will still run even if the file isn't trusted but only certain 'safe actions' will be permitted.
Potentially unsafe macro actions will be blocked

Check this out:

CurrentProject.IsTrusted property (Access)

Office VBA reference topic

docs.microsoft.com

 

[isladogs]

As an example, the attached DB has an autoexec macro and macros blocked in Access Options.
Try running it first in a non-trusted location then in a trusted location

@sumdumgai
I'm not sure why you are asking me to check out the same link that I provided. I've already read it and understand what it means
Suggest you look at the attached example db.

 

[sumdumgai]

As an example, the attached DB has an autoexec macro and macros blocked in Access Options.
Try running it first in a non-trusted location then in a trusted location

@sumdumgai
I'm not sure why you are asking me to check out the same link that I provided. I've already read it and understand what it means
Suggest you look at the attached example db.

Sorry. Had a brain fart and didn't see your link. But I'm still a little confused. I've tested your example db. It seems that the only thing that causes your autoexec to say whether project is trusted or not, is the enable macros setting. If I uncheck both ‘Allow docs on a network to be trusted’ and ‘Allow trusted locations on my network’, but I enable macros, then it says trusted. If I disable macros, it says not trusted, whether the two other blocks are checked or not. Don't the two other blocks mentioned determine whether I'm running in trusted vs non-trusted location?

 

[isladogs]

The settings for trusted locations on your network aren't really that relevant
The point is whether the folder it is running in is trusted or not or whether the database itself has been trusted by you.

Try doing the following
1. Disable all macros - autoexec still runs despite itself being a macro!
2. Disable all trusted documents
3. Run from a non trusted location - this message is shown

followed by this

The database doesn't close

The macro designer shows Quit Access as unsafe

4. Repeat from a trusted location triggers this message

Closedown then runs successfully as the unsafe action is allowed in a trusted database

5. Move the app back to a non-trusted location & enable all macros (normally this should be avoided for safety reasons)
Once again it will show as non-trusted (see 3 above)


Hope that helps clarify things

 

[isladogs]

No. Front end DB on shared network drive.

BTW - this is not good practice. The FE should be on a local drive on the user's workstation & not on the network
Performance will be worse on a network drive & there is a greater risk of corruption if there are network interruptions no matter how brief
Compacting will carry a greater risk of corruption

Using a Wifi connection to the network carries even greater risks

 

[sumdumgai]

Thanks to all for your help and advise. I'm satisfied with closing this post.

The settings for trusted locations on your network aren't really that relevant
The point is whether the folder it is running in is trusted or not or whether the database itself has been trusted by you.

Try doing the following
1. Disable all macros - autoexec still runs despite itself being a macro!
2. Disable all trusted documents
3. Run from a non trusted location - this message is shown
View attachment 101481
followed by this
View attachment 101482
The database doesn't close

The macro designer shows Quit Access as unsafe

View attachment 101483

4. Repeat from a trusted location triggers this message
View attachment 101480

Closedown then runs successfully as the unsafe action is allowed in a trusted database

5. Move the app back to a non-trusted location & enable all macros (normally this should be avoided for safety reasons)
Once again it will show as non-trusted (see 3 above)


Hope that helps clarify things

Thanks!

 

[isladogs]