Overwriting Built-In VBA Functions

Bypass

Today, I thought I’d touch upon overwriting built-in VBA functions.

The concept is very simple, what if I wanted to use my own version of a function?  Replace the built-in function?

Well, you can and it is the simplest thing to do!  Simply create a function with the same name as the built-in procedure and yours will take precedence!

For example, say you wanted to replace the MsgBox function, you could simple do

Public Function MsgBox()
    Debug.Print "MsgBox is dead!!!"
End Function

and now MsgBox no longer displays any dialog. Not something you’d actually do though.

Continue reading →

Use VBA RegEx to Clean a String

Child Sweeping

After working on yesterday’s post

File Properties
How to Retrieve a File's Properties with VBA
I’ve been working on a personal project for a little while now and needed to retrieve various file properties. Now I’m not talking about the straightforward properties you can easily retrieve using the File System Object (Size, Date Created, Type, …). If you only need such information, then look over my article: No, I’m interested…

Continue reading →

there was one aspect of my code that bothered me and I knew I could perfect, the line which removed certain Unicode characters

vPropValue = Replace(Replace(Replace(Replace(vPropValue, ChrW(8236), ""), ChrW(8234), ""), ChrW(8207), ""), ChrW(8206), "")

Don’t get me wrong, to date, I haven’t experienced any issues, but I haven’t been able to test every file type and who knows what curve balls Microsoft might throw at us in the future!

Continue reading →

How to Retrieve a File’s Properties with VBA

File Properties

I’ve been working on a personal project for a little while now and needed to retrieve various file properties. Now I’m not talking about the straightforward properties you can easily retrieve using the File System Object (Size, Date Created, Type, …). If you only need such information, then look over my article:

VBA - List of Files within a Folder and Get their Properties
I was trying to help mezentia on UtterAccess.com with getting a list of files contained within a folder with their properties (size, date created, date modified, last accessed, …) and finally created a small demo database to illustrate how it could all be pieced together in a seamless manner. As I have seen a number…

Continue reading →

No, I’m interested in:

Image & media files

  • Dimensions
  • Camera model
  • ISO settings
  • Frame rate
  • Orientation
  • Duration
  • and so much more!

Generally

  • Item type
  • Type
  • Kind
  • Program Name
  • Subject
  • and so much more!

the things that are not commonly accessible.

I already demonstrated how this could be done via PowerShell in my article

Get Image Exif Properties using VBA and PowerShell
In this article I thought I’d look at how we could retrieve Image Exif property values using VBA and PowerShell. EXIF What is contained in the Exif properties? Well EXIF contains all sorts of ‘extra’ information pertaining to a file. What exactly depends on the original source of the file, and whether it has been…

Continue reading →

but in this scenario I decided to fall back on some older pure VBA code I already had and simply tweaked.

Continue reading →

Access – Lock File Issues

No access

As of Patch Tuesday, Dec. 14th, 2021, there is a new Access file lock ‘bug’ as several threads have started popping up relating to error messages, such as:

This file is in use. Enter a new name or close the file that’s open in another program

Error 3050: Could not lock file.

in which longstanding databases no longer will open, can no longer be shared.

Here are a couple threads on the subject (there are many, many more, simple Google to find them):

Continue reading →

VBA – Extract Phone Numbers From a String

I was helping in the Microsoft Answers Forum today:

Microsoft Support Logo
MS Access Extracting phone numbers from string - Microsoft Community
Hi. In MS Access, I am trying to extract phone numbers from a string and put them in 1 row each. I have tried a few functions, but nothing has worked. Example: 555-555-1001, 555-555-1002, X1E-D2SQ

and thought I’d share the solution here as I’ve seen similar questions in the past.

Basically, the user had a string, any string, and wanted to extract all the phone numbers from it.  The phone numbers, originally, were of the 000-000-0000 format.

I knew the best approach would be to use a regular expression (RegEx) and I remembered that a while back I helped another forum user with a similar question regarding extracting e-mail addresses from a string and so I thought that it would give me a great starting point to build from.  So, I turned towards my prior post

VBA - Extract Email Address(es) from a String | DEVelopers HUT
How to extract/parse email address(es) from a string.

Continue reading →

Microsoft Access AutoExec Macros and Display Forms

Today, I thought I’d explore AutoExec macros a little today.

What is an AutoExec Macro Exactly?

The short answer is it is a macro that is automatically runs when the database is opened, assuming the database is Trusted or had its content enabled!

What Purpose Is an AutoExec Macro?

They are great when you want to run some code prior to allowing the user to interact with the actual database.  They can be used to:

  • Check linked tables and re-link them automatically
  • Enforce security restrictions
  • Setup folder/file structures required by the database
  • Perform automated backups
  • Open/run database objects (tables, queries, forms, reports, macros, …)
  • Import data
  • …

Continue reading →

WEme USB C 3.0 to SATA External Hard Drive Dock Docking Station

I recently purchased a

WEme USB C 3.0 to SATA External Hard Drive Dock Docking Station, SSD HDD Disk Duplicator Cloner for Dual Bay 2.5 3.5 Inch SATA I II III, Support UASP and Auto Sleep and 2X 12TB, Black
WEme USB C 3.0 to SATA External Hard Drive Dock Docking Station, SSD HDD Disk Duplicator Cloner for Dual Bay 2.5 3.5 Inch SATA I II III, Support UASP and Auto Sleep and 2X 12TB, Black : Amazon.ca: Electronics

on Amazon, model WM-3024B to be precise, and thought I’d post my thoughts here since Amazon won’t allow me to post a proper review.

I was eagerly awaiting this unit for work purposes and was happy when it showed up at my front door less than 24 hours after placing the order. Talk about service!

I quickly unpacked the unit, okay took it out of the box. Then attached the 2 cables that came with the unit: (i) Power, (ii) data cable. Plugged every thing in, inserted 2 different drives and powered the unit on. Then I waited, … and waited, … and waited. Nothing! I checked Disk Management, nothing there either!

I then started digging, checking the little leaflet that came with the unit, searching online, but came up empty handed.

Continue reading →

Microsoft Access and the Dataverse Connector

Dataverse Logo

In the past few days, I’ve posted a few articles:

Access Session At Microsoft Ignite, Yes It Is True!
Just a short post this evening. If you want to know what is next for Microsoft Access then be sure to attend the Bringing Microsoft Access into the Future: Introducing the Microsoft Access Connector for the Microsoft Power Platform session at this year’s Microsoft Ignite.

Meet Michael Aldridge And The New Dataverse Connector.
I wanted to share an upcoming online meeting that could be of interest to some. This is your chance of meeting the ‘New’ Access Program Manager and get some information on the new Dataverse Connector for Microsoft Access. Hopefully, he’ll be able to shed some more light on the Connector itself, Dataverse and how this…

Continue reading →

about Microsoft’s latest new ‘feature’ for Access the Dataverse connector.

Yesterday, I was made aware of the following YouTube recording of a Denver Area Users Group (DAAUG) session with Michael Aldridge, the new Microsoft Access Program Manager, dedicated entirely to the new Dataverse connector.

Continue reading →

Meet Michael Aldridge And The New Dataverse Connector.

I wanted to share an upcoming online meeting that could be of interest to some. This is your chance of meeting the ‘New’ Access Program Manager and get some information on the new Dataverse Connector for Microsoft Access.

It’s a chance for people to interact live and ask questions about the future of Access.George Hepworth

Hopefully, he’ll be able to shed some more light on the Connector itself, Dataverse and how this is an asset to the Access community. I’m hoping we get more information that what was part of the inaugural announcement on the subject at the Access Session At Microsoft Ignite. Maybe cover things like:

  • licensing requirements/cost
  • limitations
  • how this compares to using say Azure SQL
  • etc.

If we’re lucky, he may also let us see a little of what is going on behind the curtain and perhaps share his vision of the product in the next few months, years.

Continue reading →