Access – Version 2005 Build No. 12827.20268 Causing Problems

Just a very quick post to advise everyone that Update 2005 12827.20268 (released June 2, 2020) appears to be causing various problems with Decimal fields in Microsoft Access databases. (I’ve seen reports of other issues, but those remain to be confirmed)

Right now, I’d recommend steering clear of the update altogether and if you’ve been impacted by it then you’ll need to uninstall the most recent update, refer to Microsoft Office 365 – Uninstall an Update and revert your office installation back to a prior build. Don’t forget to block further Office Automatic Updates once you revert your build or you will be back in the same position within a few hours.

Another suggestion by fellow MVP Alumni Tom Wickerath is, if you truly don’t need a Decimal field, perhaps the easiest solution would be switching to Single, Double or Currency as this current bug only impact Decimals.

What’s Microsoft Saying

Continue reading →

VBA – Extract Email Address(es) from a String

Trying to help in an Access forum, a user was inquiring about what approach could be used to extract the e-mail address(es) from a string/field value.

Now, as with everything VBA, there are a number of possible answers. We could split() the string into individual words and test to see if in contained a @ within the word …

That said, this is/was a perfect case for using Regular Expressions! Use a Regular Expression pattern to find each instance of an e-mail and pass them back, nothing could be simpler.

Continue reading →

VBA – Exploring Microsoft Edge

As part of my previous post VBA – Opening Files and URLs, late one night, I also explored Edge ‘automation’ (and I use that term very loosely here!).

Since everyone is now getting Edge pushed upon them, I thought I should delve a little into what can be done with Edge using VBA to see if it offered any potential advantages for us developers.

Sadly, I was disappointed with my findings. Microsoft has once again decided to do things their own way and longstanding approaches (that work with IE, Firefox, Opera, Brave, …, Access, Excel, Word, …) did not work to automate Edge. I was unable to use my standard code to locate the exe on a system to automate it. Even with the exe known, standard chromium command line switches failed to work. All in all, a very disappointing experience. IMHO, Microsoft has, yet again, missed the mark on this one.

Also, documentation is basically non-existent on the subject, or at the very least buried somewhere that isn’t easy to discover!

Regardless, I did find a couple approaches that did work that I thought I’d share with those of you that need to perform such automation.

Continue reading →

Access – Report to Fit Page

One major aspect that trips up many novice developers is managing to fit the content within a letter page. As such, we see questions relating to scaling or sizing to fit a page. Sadly, this is one function that Access does not possess.

The obvious solution is to setup the report page to the proper page size and then resize the content to fit those boundaries (minus the margins).

That said, there is a simple little trick that you can employ to get around this limitation should you be in a situation that you can’t redesign a report or need to enlarge or shrink a report for some reason.

Generate the report in whatever page size that it is design for
Export the report as a PDF (External Data -> Export -> PDF or XPS)
Open the generated PDF and then print it, selecting Fit to page as the Scale

This way you can print any size Access report on any other size page.

Automatically Restart Access

Have you ever needed to perform some action that would close Access and then would like to restart it automatically? Perhaps something along the lines of performing a Compact, or some update process.

As part of my last post regarding automating the Compact of the Front-End component of a split database I was faced with this very dilemma. What I came up with was a very straightforward VBScript.

The script, provided below, simply loops until no lock file is present (so the current instance shutdown) and then re-launches the database again. To work, it expects 2 input arguments: (1) the full path and filename of the database, (2) the full path and filename of the lock file.

Continue reading →

Access – VBA – Compact a Database

How to Compact a Database through VBA

This is one of these questions that we see on a regular basis in various Access forums, so I thought I’d try to cover the subject today.

The Larger Issue

Whenever this question is brought up, it raises a much broader discussion as to why one needs to compact a database in the first place.

A well designed database typically does not require compacting, certainly not frequently. Database bloating (size increase that can be recuperated by a compact) usually indicates a database design issue. So normally I would review the structure and workings of the database itself to try and find the underlying issue(s) to address.

Some of the Common Causes of Database Bloating

There are a number of possible causes of database bloating, but here are a few:

Using Record-Level Locking (under client setting in the options dialog)

“Access does not have true row locking, but does have what is called database page locking. So in a roundabout way, if you turn on row locking, then Access just expands all records to the size of one database page – the result is massive bloating.” – as explained by Albert Kallal

Adding and deleting large quantities of records
Use of temporary tables
Sharing a common database front-end file amongst multiple users

Continue reading →

Video Conferencing in a World of Zoom

By now, weeks/months into the Covid-19 crisis, people are flocking to video conferencing. This recent push has seen an extraordinary amount of attention placed on a Video Conferencing company/app called Zoom mainly because it is very easy to use, and most importantly it is FREE (but at what cost I ask you?!).

That said, before diving head first into blindly using any such product, due diligence should be done, especially if you are using this technology for conducting business meetings.

Sounding the Alarm!

In recent days/weeks because of this new attention, many security companies have been scrutinizing Zoom and the results have been alarming!

Issues have surfaced with things like:

content not being properly encrypted
meetings can easily be accessed by anyone
the app can allow hackers access to your windows credentials
e-mails and other personal information being left exposed
content being routed through Chinese servers
etc…

Don’t just take my word for it! Here are a few of the most recent articles about Zoom:

Continue reading →

Excel – VBA – Save Range as JPG Image

A quick post with a little function I created based off of a recent forum answer I provided to help an Excel user.

Predefined Export Image Format

Below is a very simply sub routine that will save the designated range to a jpg image file.

The basic concept is that we copy the range, create a chart object, paste the range content to the chart and export the chart object to a jpg file (since it offers such functionality!). Enough talk, here the sub.

Continue reading →

VBA – Get The Computer Name

Very similarily to determining the current user’s username, it can be very useful to be able to determine the current PC’s name. This could be to:

include with your error loging
use to lock down your application to running on only certain PCs
use as a configuration parameter (if PCA then use the folder, if PCB use that folder, ….)
etc.

Today, I thought I’d quickly cover a couple way this can be accomplished:

Environ Function
WScript Objects
WMI
API

Continue reading →

VBA – Send E-mail Using Mozilla Thunderbird

I have several clients that use Open Source Software for their Office applications (LibreOffice, OpenOffice, …) and as such do not have Outlook. In such cases, they typically use Mozilla’s Thunderbird e-mail client (which is a great e-mail applications by the way).

I have created some simple procedures in the past to send out e-mails using Thunderbird to suit their needs, but today I thought I’d try and emulate the procedure I long ago created for Outlook so as to have the versatile procedure possible. So below is what I came up with:

Continue reading →