The Decline of Access In Forums

I was part of a discussion on UtterAccess in mid-January which got hijacked a bit about the decline of Access. As George stated it:

the erosion of confidence among customers and usersGeorge

What happens at 2 Gb?

... the real damage done by lack of commitment has been the erosion of confidence among customers and users ...

The discussion made me look further into a couple of my previous posts, such as:

What's Is Access' Position: Thriving, Alive, Dying, Dead?

Here’s a question I put to you, How is Access fairing today in your humble opinion? Is Access: Thriving as it should, growing in the world Alive, but not progressing On a downward spiral towards its demise Dead, already in its cemetery plot

Does Microsoft Access Remain A Good Choice As A Development Platform?

I know I’ve touched upon this subject in the past, but it’s been another rough couple of weeks for Access and I wanted to see, once again, were people stand on the subject. So, I put it to you: Is Microsoft Access truly a good choice as a development platform today (in 2021-2022)? Historically, I…

Continue reading →

and made me want to examine whether there was/is a quantifiable decline in Access interest.

Stacked Exchange (SE) provides access to their data and they are one of the biggest forum providers in the world. So, I set off to see what I could find out from their data.

Using the Stacked Exchange Data Explorer and the documentation

Database schema documentation for the public data dump and SEDE

Stack Exchange releases “data dumps” of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Excha...

it didn’t take long to get a clear picture of Access’ rise and fall.

Continue reading →

Outlook’s Domain Safe Sender Doesn’t Work

The title says it all.

Before going any further, I should mention, in this instance, I am running Outlook 2013.

For a long time now, I have added client domains to my Outlook Safe Senders Junk E-mail Options to ensure they safely arrive in my inbox. For years, I’ve never really had any issues. That said, in the past several month, maybe longer, more and more e-mails are ending up in my Junk Mail folder. I would double check and validate the domain was properly listed, yet the e-mails kept going in the Junk Mail folder.

Continue reading →

Self-Healing Object Variables

So what does a developer do when he can’t sleep at 3am? He decides to write an article about self-healing object variables!

So What Are Self-Healing Object Variables?

In plain English, these are variables that are intelligent enough that they initialize themselves once and retain their value for the entire session. If reset, they will reinitialize again automatically. They are like Global Variables on steroids. It is just one more step you can take as a developer to improve the overall performance and give your application a little boost, and this for any VBA application (not just Access)!

My first recollection of being exposed to the concept was back in, I believe 2003-2005ish, by MVP Dirk Goldgar who was answering one of my Access question in a discussion forum. He didn’t call it SHOV, but that is effectively what it was. Implementing it drastically helped with performance with a database I was struggling with, and from then on I was hooked! No clue who originally came up with the idea, and it may be taken from another programming language for all I know, but it’s been around for a long time.

Why Do We Want To Use Self-Healing Object Variables?

When we talk about Object Variables, we are most often talking about the use of the CreateObject() function, or in the case of Access specifically the use of CurrentDb. So things like

Set oExcel = CreateObject("Excel.Application")

Set db= CurrentDb

These actions are straining, especially when done repetitively. The process of creating ActiveX reference, by using commands like CreateObject(), can cause significant delays, so we want to minimize such activities as much as possible to give the best user experience possible.

The idea behind Self-Healing Variables is you do it once, but hold it in memory that way subsequent calls don’t need to perform the initialization again, as it is still already in memory from the 1st call, ready to go.

Using Self-Healing variables can simplify your code since you no longer need to initialize such variables in your code and can significantly improve overall performance. On slower machines the performance boost achieved by using self-healing variables can be quite noticeable.

Continue reading →

Power Automate’s Send an email Not Seeing Variables

I’ve been doing some more work in flow for a client recently and hit, what I perceive as a serious bug.

The General Idea

The project itself is very interesting and opens up a lot of possibilities!

We have a form on a website that people fill out and submit. Then, I use Power Automate’s Flow to process the data and I use an Execute stored procedure (V2) to then input the data into a database. Lastly, I use the returned response from the stored procedure back in flow to fire off an appropriate notification e-mail using the Send an email (V2) action.

The Bug

So what’s the issue exactly?

Continue reading →

Let’s Talk About All Those Errors!

I don’t know about you, but I can’t count how many time a user e-mails or calls me on the phone giving me an error number and no description. Sure you can start Googling and trying to dig up some information. In some cases, it is easy to find, in others it can be next to impossible.

Microsoft Access Errors

This is why it can be advantageous to simply create a table with all the error codes and their descriptions so you can quickly look anything up.
Continue reading →

LinkedIn Stop The SPAM

In recent months, I have noticed completely irrelevant posts, for promotional purposes, in certain groups I was part of, SPAM.

So we are all on the same page, what is the definition of SPAM exactly:

The short version:

Spamming, unsolicited or undesired electronic messages.Wikipedia

The more complete version:

Spamming is the use of messaging systems to send multiple unsolicited messages (spam) to large numbers of recipients for the purpose of commercial advertising, for the purpose of non-commercial proselytizing, for any prohibited purpose (especially the fraudulent purpose of phishing), or simply sending the same message over and over to the same user. While the most widely recognized form of spam is email spam, the term is applied to similar abuses in other media: instant messaging spam, Usenet newsgroup spam, Web search engine spam, spam in blogs, wiki spam, online classified ads spam, mobile phone messaging spam, Internet forum spam, junk fax transmissions, social spam, spam mobile apps,^[1] television advertising and file sharing spam.Wikipedia

Continue reading →

Determine If A Table Is Local Or Not

Sometimes, it can be useful to be able to determine if a table is local or not.

Yes, you can query the MsysObjects system table for those object with a Type = 1 as explained in my article:

MS Access - Listing of Database Objects (Tables, Queries, Forms, Reports, ...)

It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows: SELECT MsysObjects.Name AS…

Continue reading →

but then you need to create a recordset, and iterate over it, …

Why do that when there’s a much simpler solution!

Continue reading →

How to Filter an Existing Recordset

Today, I thought I’d touch upon how you can filter an existing recordset.

I didn’t know how to approach this subject myself back in 2013 and Dirk Goldgar and Henry Habermacher were kind enough to teach me.

Recently, I saw a question on UtterAccess pertaining to this and thought it was a worthwhile subject to post on my blog to help other developers with.

The Concept

So what am I talking about exactly?

Perhaps you have a form of contacts listed that the user has already filtered down and you can easily grab that recordset from the form, but you wanted to only get the contacts from a specific city, or gender, or …

Yes, you could identify the existing filters, and new filters and create a new SQL statement to pull the info from, but you can also simply filter the form’s recordset directly.

Below are 2 variations of the same basic technique which illustrate how this can be done.

In the examples below I’m filtering only those which records are Active (Active = True), but the filter could be anything you’d like.

Continue reading →

No Longer Use CDO with GMail

WARNING – Google/GMail has announced:

Less secure apps & your Google Account
To help keep your account secure, starting May 30, 2022, Google will no longer support the use of third-party apps or devices which ask you to sign in to your Google Account using only your username and password.

Please note this deadline does not apply to Google Workspace or Google Cloud Identity customers. The enforcement date for these customers will be announced on the Workspace blog at a later date.Google

This was brought to my attention by a post by ngins on UtterAccess:

Using CDO with Gmail After May 30th

I currently use CDO to send system message emails from my app. We are using a Gmail account created for these notification messages, with "Allow less secure apps" enabled in Gmail.

However, I just received an email from Google stating:...

So that seems to indicate that e-mail techniques such as CDO Mail will cease to work as of May 30th! So if you use any username/password techniques, start planning today so you are not caught off-guard in May.

Continue reading →

Cannot Compact And Repair Access Database – File Already In Use

After the File Lock bug, we have now seen many reports of people no longer able to Compact and Repair their databases and are receiving error messages such as:

Could not use [filename] file already in useMicrosoft Access

Here is a small sampling of the various forum threads on the subject:

MS Access will not Compact and Repair on c: or d: drive

I've recently upgraded to Windows 11 and just noticed that no MS Access database will compact and repair.

Microsoft Access is not allowing me to compact and repair. Keeps saying its open etc

Microsoft Access is not allowing me to compact and repair the database. It keeps saying file is open.

Microsoft Access 2019 "Could not use [filename] file already in use" - just on Compact/Repair

I have been using the product for a while, but started getting the error "Could not use [filename] file already in use" when I try to Compact/Repair a database

Cannot compact database

I have Access 2019 and I am unable to compact databases. I get the error message: "Could not use [filename]; file already in use." This began a few weeks ago and I've been working around it.

Can’t compact and repair | Experts Exchange

Every time I try to Compact and Repair for the past week or so, I get "Could not use "blah, blah, blah.accdb; file already in use.

and many, many more.

Continue reading →