Opening a 32-bit database in the 64-bit version of MS Access

If you attempt to open a Microsoft Access database which is designed for the 32-bit version of the software, when you are using the 64-bit version of Acces, you may get a blank screen. If this happens, attempt this workaround.

1. Holding down the SHIFT key, open the database so none of the forms present run automatically.

2. Try clicking on a form and see if it prompts you to correct an error in Visual Basic like this one:

3. If you see these lines which begin with 'Declare Function', try editing them by adding 'PtrSafe' after 'Declare'. This will indicate that the database can be run in the 64-bit version of Access.

As always, tested and confirmed to work tonight by Litigation Support Tip of the Night.

Get a list of unique values in a table

In an Access database you can easily get a list of the unique values in a column by running a SQL command.

1. This table contains multiple entries for many city names:

2. To get a deduped list go to Create . . . Query Design, and select 'SQL View' at the left of the toolbar.

3. Enter the following SQL command:

SELECT DISTINCT Olympics.City

FROM Olympics

The command SELECT DISTINCT picks a field in a table, and then FROM pulls that field from a designated table.

4. Click Run, and a new query will be generated listing each value in the table only once:

Running Regex searches in an Access database

If you need to run a query in an Access database using regular expression, Renaud Bompuis has posted a great solution here. In your Access database, press ALT + F11 to enter Visual Basic. Enter the below vba code in a new module. It will create a new function that you can reference in a query written in SQL.

For example, in this database, we have a table with numbers. We want to create a query which will find 7 digits numbers that begin with a '2'.

Go to Create . . . Query Design, and switch to SQL view. Structure a SQL search like this:

SELECT Field1

FROM Table1

WHERE RegexMatch(Field1, "2[0-9]{6}")

First, we SELECT the field we want in the results. Then we designate the table to be searched. The WHERE command lists the RegEx function, first referencing the field to be searched in parentheses.

Click Run on the toolbar, and your results will be displayed

As always, I have tested this script tonight in order to confirm that it works.

' ----------------------------------------------------------------------'

' Return True if the given string value matches the given Regex pattern '

' ----------------------------------------------------------------------'

Public Function RegexMatch(value As Variant, pattern As String) As Boolean

If IsNull(value) Then Exit Function

' Using a static, we avoid re-creating the same regex object for every call '

Static regex As Object

' Initialise the Regex object '

If regex Is Nothing Then

Set regex = CreateObject("vbscript.regexp")

With regex

.Global = True

.IgnoreCase = True

.MultiLine = True

End With

End If

' Update the regex pattern if it has changed since last time we were called '

If regex.pattern <> pattern Then regex.pattern = pattern

' Test the value against the pattern '

RegexMatch = regex.test(value)

End Function