Category Archives: MS Access General Information

Naming Conventions

What is a Naming Convention?

A Naming Convention is simply a standard rule (could be through the use of prefixes, suffixes, typographical convention (syntax styling) such as CamelCase, …) that you apply when creating names for :

  • Objects
    • Tables
    • Queries
    • Forms
    • Reports
    • Macros
    • Modules (See VBA Components)
  • Table Fields
  • Controls (Forms & Reports)
  • VBA Components (Modules, Class Modules) & Variables…
Not Just An Access Thing
This is true in any programming language and not just an Access, MS Office or VBA thing.

Why Use a Naming Convention?

Simple, to standardize your work and make it easier to follow.

For instance, take a simple example of

Public Sub Demo()
    For cars = 0 To 10
        Accident = DateAdd("d", cars, Date)
        Debug.Print Accident
    Next cars
End Sub

what is Accident and cars?  Strings, Integer, Long, …?

Now, if you implement a basic naming convention and prefix all your VBA date variables with dt for Dates and i for Interger numbers you get
Continue reading →

Special Characters and Internationalization of an Application

The Problem

If ever you get into developing applications, especially Access databases, that are deployed on various countries and/or where users change the default Regional settings you will quickly ask yourself why, oh why, did Microsoft make things so complex.

For example creating a value list through VBA, some languages use a comma (,), others the semicolon (;) and so on, and so on it goes for decimal separators, date separators, …

So hard coding the separator is a no-no unless you are in a very controlled environment!

Let’s look at a simple example, such as getting a listing of all the tables within a database to be used as a Value list of a combo box. Normally, our code would look something like:

Continue reading →

Access Calculated Control Blank/Empty/Null

Now the following is for a very niche market, but I thought I’d share none the less.

Access 2010 (although I seen reports in other versions such as Access 2013) had a major issue in which calculated control would not display their content. Yet, if you clicked on the control, it would then display the proper value. Now Microsoft eventually remedied the issue by issuing an update (I believe https://support.microsoft.com/en-us/help/2827138/access-encounters-multiple-issues-if-the-pc-has-been-running-more-than). So there is a real solution available, which is to update your installation.

However, I had a client, whose IT Dept. would not, will not, install the updates and I left me with no choice but to come up with a workaround of my own. As such, I created a routine that simply goes through a form and sets the focus on each control, one by one. It’s not ideal, but it works.

Continue reading →

MS Access Security Demo Database

Security and Access, where to start?  What to do?

Security can be simple and complicated, it all depends on your needs and abilities.  It can be superficial, as it can be very granular.  As such, I thought I’d present a couple options to help people get going in the right direction.

What does this demo illustrate?

In the demo, I demonstrate 3 different techniques:

The Static Password Approach

  • How to password protect a single command button using a hard-code VBA password

The Dynamic Automatic Authentication

This use a set of security tables that you setup once and then the database can automatically authenticate and apply security as required.  Although this example demonstrates restricting access to a command button, it can be used to control access to forms, reports, …

Continue reading →

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 →

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 →

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 →

Access Best Practices and Troubleshooting Steps

I thought I’d make a quick post to go over some basic Access development best practices and Troubleshooting steps that any developer should be aware of.

MS Access Development Best Practices

So you want to start developing an MS Access database!  The following is based on my personal experience developing databases for over 15 years now.  That said, a few (2 or 3) of the items below are debatable, but most are steadfast rules.

Continue reading →

Access x32 vs x64 Compatibility

A more and more common question in Access, and Office forums in general, relates to using Access x32 (32 bit) vs using Access x64 (64 bit).  How they play together … etc.

Now some people erroneously believe that since their machine is running a 64 bit version of Windows that Office is automatically 64 bit as well, or that they must install the 64 bit version. This is not the case.  Microsoft themselves recommend installing the 32 bit version.

“We recommend the 32-bit version of Office for most users, because it’s more compatible with most other applications, especially third-party add-ins.” — Microsoft, see: 64-bit editions of Office 2013

Personally, I see no benefit to installing the 64 bit version of Office/Access beyond Excel and Word being able to handle more data, but if you are requiring that to begin with, Excel/Word are the wrong tools! (but that’s entirely another discussion altogether).

Nevertheless, more and more people are using 64 bit versions of Office and Access and questions about compatibility abound, so I thought I’d try to explain some of the basics in plain English and try to centralize little bits of information scattered here and there into one simple to digest article.
 
Continue reading →