What is the difference between Macro and VBA code in MS Access, which one is preferred at which step (1 Viewer)

[June7]

Yes, it is there but not in the Actions list. After selecting If action, look to the right and you will see Add Else Add ElseIf

 

[HillTJ]

For fear of hijacking this topic. Now that we can establish whether the db is in a trusted location or not. How can i make a trusted location via vba. Particularly as a user may be using runtime & thus not have access to the usual options in regard to creating trusted locations suported by the full version of access?.

I guess thr bigger question is how to distribute a compiled app & ensure that the user can run it if they have runtime only?

A google search provides several vba examples of modifying the registry. What do the experts do?

 

[June7]

Recall what you do when installing or updating software. You get asked "Do you want to allow changes to this system" or something to that effect. If you have Admin privileges then you can say yes and code will do whatever such as modify registries. But if you work for a large organization that does not allow Admin privileges to users, then you call IT support and they say "No way, Jose".

 

[ebs17]

One way is to run a vbs script like the following when reinstalling the Access application. The attached example uses registry keys for Access 2010.

 

[isladogs]

Yes, it is there but not in the Actions list. After selecting If action, look to the right and you will see Add Else Add ElseIf

Older versions did not support Else statements. These were added when the capabilities of macros were extended in Access 2010

 

[GPGeorge]

Yes, it is there but not in the Actions list. After selecting If action, look to the right and you will see Add Else Add ElseIf

Ah, thanks. I found it.

 

[Pat Hartman]

In large organizations, they understand the problems revolving around trusted locations. Therefore, they are almost always willing to use THEIR power to make registry changes using Active Directory to control the user's login script. I tell them what the keys should be and they push them to all users of the application. The user department responsible for the app needs an administrator who will need to contact IT if new people need the keys because they will be using the app.

 

[HillTJ]

Daniel Pinault has a code module on his website that creates a trusted location. Will have a look at it. I envisage the macro to open a form if not a trusted location , then the user to be prompted to create a trusted location or back out. In the former case I'll run Daniel's code. Any other ideas?

 

[Pat Hartman]

That is only going to work if the IT people have not locked down changes to the registry. Your best bet is always to get IT to push the updates.

 

[HillTJ]

Pat, what if my project is a single user single install, on a pc that i have no control over. My concern is the case where the user has limited computer skills and they are using runtime & thus cannot generate a trusted location.

 

[The_Doc_Man]

The safety aspect has not yet been addressed. VBA can read, manipulate and delete many things, including external files and the entire file system. It has everything that a nasty virus needs and therefore poses a potential risk. Therefore, you need trust in the developer and later users that no harmful things will be executed.

If you don't have that trust, including certifications and trusted locations, you'll want to stop VBA from running altogether. A way out there would be to use macros, because they only affect your own application. But this is only a poor way out, because an Access application that is only allowed to use macros is quite simple and uncomfortable. Great demands could not be implemented in this way, something like this will not prevail in practice.

But if you are allowed to use VBA, there are very few reasons to think about macros at all. What macros can do is provided via the DoCmd object and its methods. In principle, these are the options that can be called up via the menu/ribbon. There you have the big disadvantage that invoked actions take place where Access thinks it is active. Therefore, you often have to focus on the desired object first.

In VBA, on the other hand, you have elements of object-oriented programming, so you can address an object by its name even if it is not immediately active.

This is always an issue, but nobody ever wants to face the other side of that issue. Assuming you are writing code because your boss said to do it, gave you an assignment in writing, and expects to see results, the "safety" aspect has to be balanced against the "business demands it" aspect. In a shop that forbids VBA, you have hobbled Access and it is possible that you have made it IMpossible to perform the required task. This becomes a political issue and is best handled by going back to the person who gave you the assignment. Tell that person that the IT staff has blocked you from doing what is required. Then your boss talks to IT to scope out the situation. Either your assignment is revoked or IT gets put in their place with regard to blocking business. Or some spot in the middle of that.

HOWEVER, @ebs17, you omit or overstate some things. VBA doesn't write machine code and there are some things you simply cannot do with it. For instance you can't very easily create address pointers you could use to scan or modify memory content. Therefore, your "virus" comparison DOES depend on whether you have admin rights or if you are just a low-level grunt user. In a locked-down environment, some IT shops simply give you enough rope to hang yourself but you can't hurt the servers - and probably can't do much with your system's disk & file system either, because they are not owned by you, they are owned by either the SYSTEM account or the ADMIN account. Permissions still work in Windows even for Access apps. Your security concern is valid but doesn't take it far enough. I've worked in a U.S. Navy environment where some systems were marked SECRET and others were SBU/FOUO, so I understand locked-down environments. It is sometimes just a matter of working your way around the mine field.

 

[HillTJ]

Found this link. https://www.galleryimage.com.au/faqs-setup-trusted-locations.html

 

[Pat Hartman]

As I said, the tool will only work if the user has permissions that allow him to set a trusted folder. You can't get around that. So, download it and try. Attempting to bypass security is poor practice but feel free to keep looking

OR, depending on what version of Office you are running, you might be able to sign the database to make it a trusted document. This isn't free though. You need to purchase a certificate from a trusted provider and then you can use Access security to add the certificate to your database so it will be trusted after the user clicks the trust publisher message once, the first time he opens the app. I don't know how these new certificates work but large companies used to be able to buy internal certificates and use them to sign internal documents. That may be an option but you would have to ask.

 

[isladogs]

Pat is referring to the new feature currently in the preview versions of A365 which allows you to sign the VBA project as trusted.
To do so you will need to purchase a code signing certificate from a company such as Verisign or Sectigo.
This is similar to the old code signing feature available in MDB files but until now never possible in ACCDB files.

The approach is more complex than using trusted locations but does have the advantage of being able to guarantee your code hasn't been altered after it was distributed to clients. For more details see my article

VBA Project Signing - 1

This article explains how to use the new VBA project signing feature in Access. It also compares the use of this feature with using trusted locations when distributing your Access applications

www.isladogs.co.uk

Or watch the accompanying video

 

[HillTJ]

Gets a bit complex for an amateur whom only wishes to share an application to people of varying PC skills. Thanks for the advice. Sorry to the originator of this thread for getting it off track.

 

[Pat Hartman]

Let us know how you make out with that runtime tool you found.

 

[HillTJ]

Will do. Thanks

 

[HillTJ]

@Pat Hartman , tried the runtime tool & it appears to work. Made a test trusted folder, then deleted it. May come in handy!!

 

[Pat Hartman]

Thanks for getting back to us

 

[Eugene-LS]

I am trying to understand difference between Macro and VBA code in MS Access.

With VBA you have a lot more possibilities.