VBA Convert Foreign/Accent Characters to Plain English Characters

In many cases in can be useful to be able to “sanitize” foreign characters such as accented characters with their equivalent non-accented characters.

For instance, when trying to create a zip file in Windows (SendTo -> Compressed (zipped) folder) as it will complain if files include such characters.

Another use is when creating/naming Folders, such characters have no place (this also includes specials characters and spaces).

For example, I was looking to convert French accented characters into their English counterpart. I didn’t want to use any of the extended ascii characters since they can be the source of potential problems. So I wanted to easily switch

ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðòóôõöùúûüýÿ

to

AAAAAACEEEEIIIIOOOOOUUUUYaaaaaaceeeeiiiioooooouuuuyy

The API Approach

So, how exact could we convert a string?  Well, many years ago (I think back in 2012), I came across the following that I’ve only tweak a little (added error handling and x64 declaration).  Sadly, I don’t remember where I came across it, but I know it can be useful to some.

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 →

VBA – File Exist

Commonly, to check for the existence of a file we advise people to use the Dir function or FileSystemObject. The problem that can be encountered with these approaches is that, for network drive & mapped drives, should they be disconnected, can result in long delay because you have to wait to the function to timeout.

I set out to try and resolve this issue and this is what I came up with. As it turns out, it was slightly more involved than I originally thought and as such, I broke certain elements into independent function should they prove useful for other application.

Continue reading →

MS Access – VBA Relink Tables

I know, you’re asking why in God’s name would you create a new table Relinker when there are a great number already available!

The answer is simple, it could be done better.

The Problem

I do a lot of remote development which means I need to relink to remote database through slow VPN connection and I was wasting a great deal of time while relinking took place.  As such, I did a few tests and notice that most, if not all relinker, including the built-in Linked Table Manager (LTM), performed the same operations

  • For each table
    • Open the source database
    • Relink the single table
    • Close the source database

The Better Approach

The fact of the matter is that most of the relinking time is actually spent Opening and Closing the source database, over and over, and over!  What a waste.  I knew this could be optimized to only open a source database once and relink all its’ tables and so I created my Table Relinker.

My table relinker works in a slightly different way

  • For each source database
    • Open the source database
    • Relink all the tables associated with that source database
    • Close the source database

This way each database is only opened once!

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 →

Import Excel Where Are You? Anyone Else Confused!

A recent discussion started by a fellow MVP Alum brought to my attention a recent change in the Access interface brought on by the most recent update (1706). It would appear that the Dev Team has decided to try and save some space.

Microsoft has Removed/Moved commands:

  • Excel
  • Access
  • ODBC Databases
  • Text File
  • XML File
  • More

from the Import & Link grouping from the External Data tab in the Ribbon, causing confusion.

Let look at things historically for a moment

2007

2010

2013

2016 Pre 1706

And Now the New Setup

2016 Post 1706

Continue reading →

MS Access Is User Connected by Wireless

For a very long time, a best practice has been to always use Access over a LAN connection and to never use it over a WAN or wireless connection because of the possibility of corruption. You can read more on the subject by reviewing the article entitled Access Back-End Location – WAN, Online Server, OneDrive, DropBox, ….

Now, sadly, it is often not enough to just advise end-users to always use a LAN connection. They forget and simply won’t think about it (not out of malice). As such, you can find numerous discussions asking “how can you block your database from opening if the user is using a wireless connection?” or something to that effect.

Well, I too had been looking to enforce this type of thing in one of my databases where wireless connection had already broken the database a couple times already. Below is what I came up with.

Continue reading →

MS Access Working with Attachment Data Types

Although I highly recommend completely avoiding the use of Attachment Data Types, it is still a common question in discussion forums so I thought I’d put together a simple demonstration database.  In the following sample I demonstrate a few ways to work with attachment fields such as, how to:

  • Create subform to display all the attachments
  • Save an individual attachment to a folder of your choosing
  • Save all the attachments to a folder of your choosing
  • Open/Preview an attachment
  • Delete an attachment

Continue reading →

Action Queries Do Not Work and Crash Access

Since the most recent Office 365 (1706) update Action queries make Access crash in foreign language installation.  It does not appear to impact English editions.

The Microsoft Access Dev Team was advised of the issue.

The only known workaround is to open the query in design view and run it from there or uninstall (or what Microsoft is now referring to as Revert) the latest update.

 

Continue reading →