VBA – Find Disk Drive By Name

Once upon a time, I was asked to help automate a process of copying files to an External Drive. The issue was that when they plugged the device (external drive) into various machines, the associated drive letter wasn’t always the same. They also didn’t want me to use the Drive’s Serial Number as the identifier in case it died and they needed to replace it.

Instead, the solution was to bind to the Drive’s name. This way, if ever the drive was switched, all they needed to do was rename it in the same manner and the process would continue to work.

Now that we knew what we wanted to do, the question then became how could we do so?!

Continue reading →

A More Modern Date Time Picker

In a recent discussion, someone asked if there were any more modern Date Time Pickers available to implement in their Microsoft Access database. Rightfully, as they stated, most current Date Time Pickers are very much visually stuck in the 80’s.

So I set out to tinker and see what was possible.

Continue reading →

VBA – Debug.Print – spc, tab, semi-colon, comma and more

I think one of the most underutilized methods for developers is the Print method! Beyond which, those that do use it often don’t necessarily use it to its full potential.

Did You Know?

Did you know the Print method actually has parts (options) that you can use? Most developer don’t.

What Is The Print Method?

The print method allow us to output (or ‘print’) information/values/content to VBA/VBE the Immediate Window.

How Is That Useful?

I know developers that don’t see the utility of Print when they have MsgBox, but they serve 2 distinct purposes.

MsgBox is more for the end-user, to display a message to them. Sadly, you can interact with a MsgBox. Amongst other things, you can’t copy the contents.

Print is only for the developer as it is displayed in the VBE Immediate Window. The beauty here is:

that you can copy/paste its content which is great to search up those error numbers/descriptions.
use it while in the VBE creating code without having to switch back and forth to the Access GUI and VBE windows
using the options, and other techniques, you can format the output.
you can sprinkle it throughout procedure to see the evolution of variables, … in real-time. This is great for debugging purposes.

Continue reading →

VBA – Retrieve Windows SID And Username

In this article, I decided to play around with Windows SIDs and inversely the associated username.

It can be required, say when trying to look certain information up in the registry, to determine the SID of a Windows account. Or perhaps, inversely you have an SID and you’d like to know the Account Name it is associated with.

Continue reading →

Access – Reset AutoNumber Counter/Seed

Every once in a while I see a question regarding wanting to reset an autonumber’s counter/seed value to a specific number.

Eliminating Gaps

If the numbers have a big jump in numbering between the last entry in the table and a new one you are creating, and you are simply wanting to eliminate this gap, then performing a Compact and Repair should do the trick.

So say you delete a large quantity of records from a table, performing a Compact and Repair will reset the value to the very next number in the sequence based on the current maximum value in the table.

If you wipe a table completely, then performing a Compact and Repair will set the numbering back to 1 (or whatever the set seed value is).

Continue reading →

WMI Errors Using Count, ItemIndex, …

I was recently doing some WMI work and trying to implement some code simplification to an existing routine, but the code, which I knew was valid, kept throwing an

Error -2147217386 – Invalid operation

when trying to use the ItemIndex method.

I did some troubleshooting and was amazed when something as simple as object set Count property was also failing and returning

Error -2147467259 – Unspecified error

Continue reading →

VBA – Getting Disk/Drive Information

Have you ever needed to get some basic disk information via VBA?

In the past, refer to m article:

VBA - FSO Files, Folders, Drives and More

Over the years, I’ve posted punctual articles relating to individual FSO solutions: and many, many more. Today, I thought I’d provide you with a complete breakdown of the File System Object (FSO) most common functions to give you a proper overview of just how useful it can be to you when you need to interact…

Continue reading →

I’ve shown that FSO gives us easy access to some things:

Total Drive Size
Drive Available Free Space

That said, as with everything VBA, there are a multitude of ways to accomplish the same task and today I thought I’d share other possible approaches. You pick the one that best suits your needs.

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 →

VBA – Determine Your PC’s MAC Address(es)

Last week I published a few functions to retrieve things like you Local IP & your Public IP address, you can learn all about it by reading:

VBA - Determine Your PC's IP Address

A while back I posted regarding retrieving the IP address of a host/domain: Today, I thought I’d share a couple procedures to get information regarding the local PC’s IP addresses: Local Network IP Addresses Public IP Address

Today, I thought I’d show you how easy it is to access another important Adapter property, the MAC Address(es).

All the examples I’ve seen always return a single value, but this can be incorrect depending on your setup! This is why I provide examples that return all the MAC addresses from your PC!

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

…

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

Continue reading →