MS Access Reserved Word Checker

If you aren’t already aware of it, there are a set of words which should never be used to name

Database objects (Tables, Queries, Forms, …)
Table/Query Fields
Form/Report Controls
VBA procedure names, variable, …

these are referred to as Reserved Words and thus Reserved Words need to be avoided at all cost as they can generate strange behaviors or outright failure of your database.

There are numerous examples of oddities caused by Reserved Words, but below is one of them as an example:

Compiling Deletes Code

... Just today, VBA started deleting any new code entered when I compile...

The issue with Reserved Words is that there is simply no way to remember them all. Furthermore, when taking over another developer’s work, it is next to impossible to review every object, control, … for such terms.

Continue reading →

Surface Laptop – Microsoft Misses The Mark Again!

In a recent discussion, it was brought to our attention that Microsoft’s new Surface Laptop scored a 0 out of 10 for repairability. You can read Fstoppers full article by using the following link

Microsoft’s New Surface Laptop Scores 0 out of 10 for Repairability

Now I’m not even going to argue points like Apple’s, et al., have been doing this for years. This is true. And if everyone jumps off the bridge, you’re going to do that too! So let’s move on.

This was Microsoft’s opportunity to shine and set themselves apart from there competitors, to be bigger and better, to show confidence in their product, to show respect to their users, and they didn’t. They chose to simply do as everyone else does, how disappointing.

Continue reading →

Great Access Tools – Calendar Controls

Those of you that follow this blog or have seen my forum posts regarding the built-in date picker know my thoughts on the matter. To put it simply, it is a disgrace that this is what Microsoft supplied with Access for selecting dates. If you want more reasons as to why I think this, refer to my earlier post

Disable the MS Access Date Picker

I won’t lie, I hate the built-in MS Access Date Picker. Once again, Microsoft did things as poorly as possible by implementing the most primitive Date Picker possible. One that only moves forwards or backwards one month at a time. No Month selector. No Year selector. No ability to interact with it through VBA! Try…

Continue reading →

So, since I highly recommend completely disabling the built-in date picker and replacing it, I thought I should show a couple of the excellent replacements.

Also, another reason to implement a date picker and stop manual entry by your users is to avoid formatting issues depending on what format the user chose to use when inputting the date. 06/11/11, what’s the day, month and year? No guarantee Access will save it as was intended by the users. However, by using a date picker, you have 100% certainty that the date entered is the date saved!

Continue reading →

Great Access Tools – Treeview Control

Like many of you, I have used the TreeView control in the past for a couple projects. Sadly, everything came crashing down on me when Microsoft released their update KB2596856 and instantaneously my clients who were running my databases with the TreeView control could work no longer! … I cleaned up the mess and stayed as far away from ActiveX controls as possible.

On top of that, it never got ported to x64 bit, so those developers were screwed (for lack of a better word).

Then Microsoft decided to do away with the TreeView control altogether.

So what does one do?

The TreeView itself can be a very useful tool no doubt, but Microsoft’s solution was too limited and fragile.

Once again, the community answered the call! JKP Application Development Services developed and freely released 100% VBA TreeView, no strings attached!

Continue reading →

Access Web Apps Down?!

Did your AWA Hybrid application suddenly stop working over the week-end?!

The Problem

First reported by fellow MVP George Hepworth, it would seem that Microsoft, in their very finite wisdom, decided to change some, or all of their AWAs server(s) over the course of the week-end without notifying a single user. As a result, the connection string and credential you’ve always used, today, are invalid and your database is dead in the water.

George reported that the External Connections setting was also disabled.

A few threads on the matter

Below are a few threads that have surfaced regarding the problem.

The Solution

Continue reading →

MS Access – List All Lookup Fields

We all know about the evils of Lookup fields and why they are to be avoided at all costs! If you aren’t already familiar with why they are to never be used, then take a few moments to review The Evils of Lookup Fields in Tables (pay special attention regarding Upsizing should you ever think you will need to eventually port your db to SQL Server, or another RDMS one day!) and Did I Mention I Have a Thing Against Lookup Fields in Tables?.

The problem is that many novice developers are not aware of the above and use them, some abundantly. So when I take over their projects I need to easily find these fields so I can remove them and set things up properly. As such, here is a simple little procedure I put together that will list all the table lookup field in a database.

Continue reading →

MS Access – Find Macros Using a Search Term

To continue my original post, entitled MS Access – Find Embedded Macros, regarding identifying where Embedded Macro were being used within an Access database, I also develop the following procedure that enable one to search through all Embedded Macros and Standard Macros for a search term. The search term can be anything, object names, commands, segments of words, …

I originally was using this to identify where Forms were being called since I needed to rename them.

Continue reading →

MS Access – Find Embedded Macros

Most people already know that I do not favor Embedded Macros in any capacity. Unlike VBA, Macros are more complicated to program, are not searchable making it hard to perform database updates.

Long story short, I was working on a project a short while ago where I need to do a major cleanup of object names, control names, … and was needing to identify where object were being used so I could update them. Then I decided, even better, let’s find all the Embedded Macros so I can simply convert them over to plain VBA and then this problem would not occur again in the future.

As such, I put together the following procedure which goes through all the database Forms and Reports and their respective controls to identify those that use Embedded Macros. Hopefully it can help someone else.

Continue reading →

Access Database File Compatibility Table

Access has always maintained an exceptional backwards compatibility between version, but as we move forward and new versions are released some of the older versions are being left behind. Now I’m not going defend why this is happening, but I did want to try and create a simple list to easily know what version is compatible with which version. This is what I’ve put together thus far.

Continue reading →

VBA – WIA – Convert the Image Format

I was continuing to explore the power of Windows Image Acquisition (WIA) and put together the following function to convert an image from one format to another.

With WIA we can very easily work with and convert between:

bmp
gif
jpg
png
tiff

file formats and the beauty is this is all built-in and available to us natively!

Continue reading →