Category Archives: MS Access Macros

Where To Turn To As The New Outlook Is Deployed And Breaks Our Applications

Anyone that follows my blog, knows that I’ve published many articles about the ‘New Outlook’. People are starting to face the issue of Outlook automation breaking when they upgrade to the ‘New Outlook’, and I use the term ‘upgrade’ VERY loosely as it truly is not an upgrading in any shape or form.

For instance, today, there were a couple posts on the subject on the Microsoft Answers Access forum:

How do I fix the SendObject failure with MS Access and the new version of Outlook
MS Access 365 Run-time error 2293

there have been others and many, many, many more will surface in the coming months.
 
Continue reading →

Renaming Macros in External Microsoft Access Databases Using VBA

I thought I’d simply share a function I devised to help someone out on UtterAccess in case it could help someone else out there.

The question that was originally asked was:

I’d like to be able to disable the autoexec macro in an external database by renaming it using VBA

So, basically asking how can we rename a macro in an external database.

Continue reading →

Access – VBA – Close All Open Objects

So after posting about closing individual objects:

Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!  
Access – VBA – Close All Open Queries
Continuing down the same path as my previous post: I thought I’d share a simple function to close any and all open queries. The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).
MS Access - VBA - Close All Open Forms
In some cases it may be useful to close all the forms within a database. Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.
Access – VBA – Close All Open Reports
Similar to my previous couple of posts: I thought I’d present a function to close all the open reports within a database. The code is slightly simpler that the 2 previous posts as we can use the Reports property which returns a list of only the open reports.  Thus we don’t need to validate whether…

Continue reading →

Access – VBA – Close All Open Macros
Once again, a continuation of my recent posts: … In this post, I’d like to present another function which will close all the currently open Macros within the current database.

I thought it simply made sense to create a single procedure that did it all.
 
Continue reading →

Access – VBA – Close All Open Macros

Once again, a continuation of my recent posts:

Access - VBA - Close All Open Tables
Have you ever needed to close any and all open tables? Ensure that no tables are still open? It is remarkably easy to do!  
Access – VBA – Close All Open Queries
Continuing down the same path as my previous post: I thought I’d share a simple function to close any and all open queries. The concept is the same in that we loop through all the queries (CurrentData.AllQueries), check to see if each one is loaded (IsLoaded), and if so, close them (DoCmd.Close).

…

In this post, I’d like to present another function which will close all the currently open Macros within the current database.

Continue reading →

Microsoft Access – Get Object Created Or Modified Date/Time

Microsoft Access - Object - Created and Modified Dates

Getting the Creation Date/Time

So have you ever tried to retrieve the Created Date/Time. Pretty easy right as there are a couple ways to attack this!

You can simply query the MSysObjects table:

SELECT MSysObjects.DateCreate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or perhaps use DAO Containers and Documents:

CurrentDb.Containers("Forms").Documents("Form1").Properties("DateCreated").Value

 

Getting the Last Modified Date/Time

But what about the Modified Date/Time?

So you would think that we could simply modify the above query of the MSysObjects table to:

SELECT MSysObjects.DateUpdate
FROM MSysObjects
WHERE (([Name] = 'Form1') AND ([Type] = -32768));

Or the DAO Containers and Documents to:

CurrentDb.Containers("Forms").Documents("Form1").Properties("LastUpdated").Value

But those are not reliable and don’t always work properly! (especially for Forms and Reports). Instead, they seem to return the Created Date/Time again.
 
Continue reading →

Working With Access Macros Efficiently

Okay, anyone that reads my blog, or knows me, knows there’s no love lost between myself and Macros.

They are very poor to work with, troublesome to troubleshoot, no means to copy/duplicate and just a plain Pain In The Ass to develop. Once again, another feature that was never fully developed.

Regardless, today I thought I’d share one easy little tip to hopefully help a few of you out there when working with Macros (this also applies to developing Microsoft Flows which are basically the same as developing Macros).

One Of The Main Problems With Working With Macros And Flows

I was working on troubleshooting a client’s database which uses a tremendous amount of macros and they don’t want to spend money converting over to VBA. I was having a hard time to identify where the issue was coming from and wanted to eliminate certain Macros, one by one, to identify the root cause of the problem. The issue is that Access offers no way to deactivate an Event/Macro (nor does flow).

Suggestion For Microsoft
Add a simple checkbox to Macro Actions so developers can easily toggle active/inactive individual Actions. The same would be great for Embedded Macros as a whole at the Event level!

As a developer you basically have 2 options:

  • Delete the Macro and then recreate it once you are done
  • Create a copy of the database to do your investigative work and then return to the original copy for development

Both approaches are far from ideal, typically involves a lot of duplication of work, …

Continue reading →

MS Access Reserved Word Checker

If you aren’t already aware of it, there are a set of words which should never be used to name

  • Database objects (Tables, Queries, Forms, …)
  • Table/Query Fields
  • Form/Report Controls
  • VBA procedure names, variable, …

these are referred to as Reserved Words and thus Reserved Words need to be avoided at all cost as they can generate strange behaviors or outright failure of your database.

There are numerous examples of oddities caused by Reserved Words, but below is one of them as an example:

Compiling Deletes Code
... Just today, VBA started deleting any new code entered when I compile...

 

The issue with Reserved Words is that there is simply no way to remember them all.  Furthermore, when taking over another developer’s work, it is next to impossible to review every object, control, … for such terms.

Continue reading →

MS Access – Find Macros Using a Search Term

To continue my original post, entitled MS Access – Find Embedded Macros, regarding identifying where Embedded Macro were being used within an Access database, I also develop the following procedure that enable one to search through all Embedded Macros and Standard Macros for a search term. The search term can be anything, object names, commands, segments of words, …

I originally was using this to identify where Forms were being called since I needed to rename them.

Continue reading →