Clearing the ClipBoard

broom clean clear

Because of the work I do, I am often copying sensitive information and/or username and passwords, and I simply don’t like that type of information residing in the clipboard (windows memory) for extended periods of time. With all the spyware, viruses, malware, websites that try to harvest data, … it simply isn’t smart, this day in age, to leave such sensitive information available for bad actors.

Previously, I would open Excel, click on the dropdown around for the clipboard and use the Clear All command, but this become tedious to have to do over and over and over … so I created a simply VBA/VBScript file that resides on my desktop and I simply double-click to clear the clipboard. 2 Lines of code are all that are needed:

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo off | clip", 0, True

It’s as easy as that! Should you wish to integrate such functionality within an application (Access, Excel, …) the code uses Late Binding so is fully portable!

Online Collaboration Tools for SQL, HTML/CSS/JS and RegEx

More and more, I see my business moving away from Access an toward the cloud. That is simply the reality of the world we live in. People want their data accessible on any and every device and Access simply does not offer that natively.

As such, in the past few years I have been immersing myself in HTML, CSS, JS, PHP, MySQL, SQL Server and Azure, …

Sometimes, it can be very convenient to be able to demonstrate coding to fellow developers, to get help (forum postings), to collaborate, … or even just to quickly test out a concept rather than needing to setup your development environment you can use one of these tools.

Today, I thought I’d share a few online tools that enable such collaboration

Continue reading →

VBA – Break List Into Line Items

list-items

A short post today of a simple little utility procedure that I quickly put together to solve a punctual need that I thought I’d share.

Have you ever been given a linear list of items, could be comma separated, and needed to convert the list into a line item style of list? Now for a 3-4 item list , no big deal, you can quickly manually make the change, but when the list grows into the dozens, hundreds, … then it can become tedious and time consuming.

Well, that’s the scenario I was facing earlier this week, and I created the following, very simple, sub to do the work for me.

Continue reading →

Access – Bug – MDB (Jet) Broken by Latest Round of Updates

Software Bug

Yes, once again (I know I’m sounding like a broken record – not my fault, speak to Microsoft!), Patch Tuesday has gone and messed up the jet dll (msjet40.dll).

Symptoms

Some of the symptoms include:

  • uncontrollable database bloating
  • errors relating to ‘MSysAccessObjects’
  • breaks replication
  • …

Who’s Impacted by This Bug

Impacted system are those still using Jet, so any of the older mdb, mde style database.

Continue reading →

VBA – Get Host IP Address

Once again, trying to help out in a forum discussion in which the user was wanting to retrieve the IP Address of a host name.

The trick here is to know that there exists an ancient DOS (oh yes, that ancient beast) command that enables one to easily retrieve such information, the nslookup command.

So the question becomes

How can I run nslookup in VBA?

From there, it is just a question of building a wrapper to execute the command and parse the returned response.  Another important element here is that some host can return multiple addresses, so you need to be able to loop through the returned information as it isn’t always the same.

That all being said, it didn’t take long to come up with the following little function:

Continue reading →

Access – Happy Birthday ‘Inconsistent State’ Bug

Happy Birthday

Yes, I’m being cheeky here, but I thought I’d point out that we’ve long since celebrated this phenomenal bug‘s 2nd birthday (was actually around June 1st, perhaps even end of May) and still no fix!

Furthermore, the last update on the subject from Microsoft was Nov 29, 2019!

I love the Access Team, I truly do, but this situation is out of control.

Let’s keep our fingers crossed it won’t be another 2 years before this is actually fixed.

VBA – Get Short Path

I was trying to help out in a forum discussion in which a user was needing to retrieve the old DOS short path format of a path.  As with all things VBA, there are multiple ways to do this and one of the more common approaches is to use the GetShortPathName API.  Now there is nothing wrong with the use of APIs, but at the same time, they can unnecessarily complicate things, as you need to worry about bitness, code can become complex… so normally, if you truly don’t need them, you are best to use alternative solution.

Anyways, I thought I’d show a simpler solution using FSO (File System Object).  Below was my answer to the question

Continue reading →

Access – The Golden Rule of Development

This is a very short post, but a VERY important one!

I keep seeing people posting questions in forums relating to databases not working and errors such as:

Access - Error - Unrecognized Database Format
Access – Error – Unrecognized database format
Access-Error-Compatibility Warning
Access – Error – Compatibility Warning – This database uses some features which may be incompatible with the current version of Microsoft Access.
Access-Error-The Database You Are Trying To Open Requires A Newer Version Of Microsoft Access.
Access – Error – The database you are trying to open requires a newer version of Microsoft Access.

caused by not being aware of the No. 1 rule of development, especially for Access.

Continue reading →