Litigation Support Tip of the Night

May 15, 2020

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:


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

May 11, 2020

While Access gives you the option to rename tables, queries, and other objects when you right click on them, you should avoiding doing so.   After an object is renamed, you may receive an error such as this one:

Part of the trouble stems from the fact that other objects in the database will have trouble finding renamed objects that they have been linked to.   If you select the option to 'Track name AutoCorrect info', and 'Perform name AutoCorrect' under File . . . .Options . . . Current Database, Access can avoid errors by updating object names automatically.  But you need to give Access time to create the maps.  Clear these options, close the database, reopen it, and re-select the options in order to make sure the maps are up to date. 

Note the AutoCorrect feature will not revise object name changes in VBA code or queries written in SQL. 

September 7, 2018

The Tip of the Night for last night, showed how to parse data in an Access column.   The code I posted specifically acted to pull data to the left of a delimiter.    Here's how to parse out the data on both sides of a delimiter into two new columns.

In an example like this one, we have city and state names separated with tilde delimiters. 

In the SQL mode of a query design view enter this code:

SELECT Table2.Field1, Trim(querysplit([Field1],"~",0)) AS Field2, Trim(querysplit([Field1],"~",1)) AS Field3
FROM Table2;

We modify the number in the Trim function by entering a number to indicate the sequence of the text after a delimiter that we want to pull (so, 'querysplit([Field1],"~",1)  pulls the first string after the ~ delimiter.)

We combine the two Trim functions by simply putting a comma between them on the line of code beginning with SELECT. 

Note that as explained in last night's tip we need to have the following VBA code inserted in a module in order for this query to work:

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)
    QuerySplit = Split(FieldName, Delim)(Position)
End Function

 When this query is run we end up with this result with the city and state names parsed into separate columns. 

September 5, 2018

You can parse data in an Access table as you would with the Text to Columns tool in MS Excel using the split function in a query.   See the VBA code posted to this site by sxschech. 

Start with a table like this one, which has delimiters (such as ~) which you want to use as a reference to separate data to the left of delimiter into the new column at the right. 

 Insert this VBA code in a new module:

Option Compare Database

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)
    QuerySplit = Split(FieldName, Delim)(Position)
End Function

Create a new query and select the SQL view. 

Enter these SQL commands:

SELECT Field1, Trim(querysplit([Field1],"~",0)) AS Field2
FROM Table1;

We select the field to be parsed:

SELECT Field1,

. . . trim by the delimiter:


. . . designate the new field to hold the extracted data, 

AS Field2

. . . and reference the table to run the query in:

FROM Table1;

Run the query and the data will be parsed:

August 7, 2018

Dev Ashish has posted VBA code here, which can be used to search for a string in multiple Access tables.

In this example we have an Access database with multiple tables.  We want to find which tables contain the string, 'giraffe'.


Begin in Visual Basic by creating a new class with this code in it:

Public TableName As String
Public ColumnNames As New VBA.Collection
Public ResultRows As New VBA.Collection

Right click on the database name in the project list and select Insert . . .Class Module

In the Properties window at the lower right, rename the class module to, 'SearchResults'.

Next, create a new module with the full code listed below in it.   Modify the code on the line reading:

Set results = SearchAllTables("giraffe")

Press F5 to run the code.    Text will be generated in the immediate window listing the tables and lines on which the searched for string appears.

Sub TestSearchAllTables()
Dim results As VBA.Collection
Dim result As SearchResults
Dim i As Integer, j As Integer, k As Integer

Set results = SearchAllTables("An")
If results.Count > 0 Then
For i = 1 To results.Count
Set result = results.item(i)
With result
Debug.Print "***************"
Debug.Print "Result found in: " & .TableName
Debug.Print "***************"
For j = 1 To .ColumnNames.Count
Debug.Print .ColumnNames.item(j),
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
End With
Debug.Print "No records found"
End If
End Sub

Function SearchAllTables(criteria As String) As VBA.Collection
Dim rs As dao.Recordset
Dim tdf As dao.TableDef
Dim db As dao.Database
Dim fld As dao.Field
Dim sql As String, i As Integer, j As Integer
Dim doInclude As Boolean
Dim results As VBA.Collection
Dim item As SearchResults, items() As String
On Error GoTo ErrHandler

Set db = CurrentDb
Set results = New VBA.Collection

For Each tdf In db.TableDefs

doInclude = (Not CBool(tdf.Attributes And _
dbSystemObject)) And _
(Not CBool(tdf.Attributes And dbHiddenObject))
If (doInclude) Then
sql = "select * from [" & tdf.Name & _
"] where "
For Each fld In tdf.Fields
sql = sql & "[" & fld.Name & "] like '*" & _
criteria & "*' or "
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

If (rs.RecordCount > 0) Then
Set item = New SearchResults

item.TableName = tdf.Name
ReDim items(0 To rs.Fields.Count - 1)
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
items(j) = rs.Fields(j).Value & vbNullString
item.ResultRows.Add items
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
results.Add item:=item, Key:=tdf.Name
End If
End If

Set SearchAllTables = results

Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing

Exit Function
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbOKOnly Or vbCritical, "SearchAllTables"
End With
Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function

June 16, 2018

If you have an Access database with a large  number of tables, queries, and other objects, you may find it helpful to use the utility developed by Brent Spaulding and posted here on Utter Access.   The .mdb file downloads in a zip file.   After you've unzipped it, you can import it into your existing Access database by going to External Data . . . New Data Source  . . . From Database . . . Access.  

You'll have the option to import any objects from the selected database, 'SearchForText.mdb'.  Select the 'Form' tab and choose, 'xdlgSearchForText_v42' , and then click OK.

A new form will be added to the Object menu.   You can select one or more objects to search.   If you search for a field name such as 'HR', you'll get a list of which tables it appears in. 

If you search for the table name, you can easily get a list of all of the fields in the table, that you can then select and copy.  

November 23, 2017

It’s not necessary to enter fields one by one in an Access table.  You can run SQL script to add multiple fields automatically.   

First go to Create . . . Query Design and open a new query design.  In the View menu on the left select  SQL View.   Enter the ALTER command to add fields to an existing table followed by ADD COLUMN and the new names & data types.  See this example:

ALTER TABLE Table1 ADD COLUMN yearID Memo, lgID Memo, teamID Memo, franchID Memo, divID Memo, Rank Memo, G Memo, Ghome Memo, W Memo, L Memo, DivWin Memo, WCWin Memo, LgWin Memo, WSWin Memo, R Memo, AB Memo, H Memo, 2B Memo, 3B Memo, HR Memo, BB Memo, SO Memo, SB Memo, CS Memo, HBP Memo, SF Memo, RA Memo, ER Memo, ERA Memo, CG Memo, SHO Memo, SV Memo, IPouts Memo, HA Memo, HRA Memo, BBA Memo, SOA Memo, E Memo, DP Memo, FP Memo, name Memo, park Memo

Click the floppy disk icon to save the query, then just double click it on the Query list to run it. 

In the table design view you’ll see a list of new fields like this. 

October 12, 2017

See this how to video posted to my YouTube channel, and the below instructions taken from the video.

Collect all of your Excel files in a single folder. 

In this sample set we can see that while some of the columns in the spreadsheets have the same headings, many are unique to the individual Excel files.

Create a new Access database.  Enter Visual Basic by pressing ALT + F11

We'll be using three macros which I have already inserted in separate modules in Visual Basic.   To create a new module just right click on the left on the Modules folder and select Insert . . . Module


The first module will import the source Excel files.  The vba code was created by Ken Snell and is available on the Tip of the Night for October 5, 2017.  See

Be sure to set the line beginning  blnHasFieldNames =
to True not False, to import the first row of the Excel files as the column headings.

Enter the path to the folder containing the Excel files on the line beginning:

strPath =

Press the play button and the vba code should import the Excel files as separate tables into the Access database.

Now we want to get a list of all of the fields in each of table.   Go back to visual basic and use the vba code I have inserted in the second module, which was created by Ken Puls and was posted as the Tip of the Night for October 6, 2017.

It should not be necessary to edit this vba code.   Press play and an Excel spreadsheet will be generated which lists the table names in column A and the field names in column B.

Now we'll de-dupe the field names and use the transpose paste option in Excel to put them in the first row of a worksheet.

Note that the vba code we just used to generate the table field names has a flaw.  It does not pick up the names of the first field in each table. 

In this example only the first field in the Parks table is omitted, and there is overlap between the tables which accounts for the first field for the other tables. 


Add the missing field names to the worksheet.

In the second row we want each cell to have more than 255 characters.  This will prompt Access to make each field a Long Text field when the field names are imported. 

Use the LEN formula in Excel to confirm you have the right number of characters.

Then select cell A2, then select the cells to the right and press CTRL + R to copy the data to all of the other cells on the second row.

Next we'll go to the External Data tab of the Access ribbon and select New Data Source . . . From File . . .Excel to import the fields into a new table.

In the Import Wizard be sure to check off the box making the data in the first row the column headings of the Access table.

The third and final vba code will insert the source tables one by one into the new master table.

This code is available in the MS Office Dev Center and was posted as the Tip of the Night for October 8, 2017

First we need to enter the path to the Access database we're working in. 

On the line containing the SQL command INSERT INTO enter the name of the master table.   Then put the name of the source table following FROM in parentheses.

It's necessary to run this macro once for each source table.


The data has been added for the first source table - Batting.

As you can see, where the tables had a field in common, the data from all sources is now in a single field.

October 8, 2017

Tonight's tip concerns VBA code posted to the Dev Center for MS Office at .     The VBA code runs a SQL INSERT INTO command to insert all of the records in one Access table into another table that must contain all of the same fields, but may contain many other fields.  This is particularly helpful when you're trying to merge multiple tables into a master table and there are numerous fields  - some of which match, and other that don't.

In this example, we have two tables.   All of the fields in the table named, 'Batting' are in the table named, 'BattingPost' which also contains one extra field named, 'Round'.   

In a new module in Visual Basic we insert this code listing the path to our database on the line beginning 'Set dbs', and list the table records will be added to after INSERT INTO and the source of the records after FROM.

Option Compare Database

Sub InsertIntoX1()
    Dim dbs As Database
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("C:\Users\SeanKOShea\Documents\Database8.accdb")
    ' Select all records in the New Customers table
    ' and add them to the Customers table.
    dbs.Execute " INSERT INTO BattingPost " _
        & "SELECT * " _
        & "FROM [Batting];"
End Sub

As you can see we end up with this result - a table containing 19592 records - the sum of the 7902 records in the Batting table and the 11690 records in the BattingPost table.  The data from Batting goes into the BattingPost field where the names are the same.

October 6, 2017

Ken Puls has posted the below Visual Basic code for MS Access which will generate an Excel file that shows all of the fields in all of the tables in database.  

So when you have a set of tables like these:

 . . . you can press ALT + F11 to enter Visual Basic.   Right click on Modules in the list to the left and insert a new module.   Paste in the code and press play.

An Excel file like this will be generated listing the tables in one column and the fields in each table in the second column.    Note however that the code has a flaw in it:  it omits the first column in each table. 

Option Compare Database 
Option Explicit 
Sub ListTablesAndFields() 
     'Macro Purpose:  Write all table and field names to and Excel file
    Dim lTbl As Long 
    Dim lFld As Long 
    Dim dBase As Database 
    Dim xlApp As Object 
    Dim wbExcel As Object 
    Dim lRow As Long 
     'Set current database to a variable adn create a new Excel instance
    Set dBase = CurrentDb 
    Set xlApp = CreateObject("Excel.Application") 
    Set wbExcel = xlApp.workbooks.Add 
     'Set on error in case there is no tables
    On Error Resume Next 
     'Loop through all tables
    For lTbl = 0 To dBase.TableDefs.Count 
         'If the table name is a temporary or system table then ignore it
        If Left(dBase.TableDefs(lTbl).Name, 1) = "~" Or _ 
        Left(dBase.TableDefs(lTbl).Name, 4) = "MSYS" Then 
             '~ indicates a temporary table
             'MSYS indicates a system level table
             'Otherwise, loop through each table, writing the table and field names
             'to the Excel file
            For lFld = 1 To dBase.TableDefs(lTbl).Fields.Count -1 
                lRow = lRow + 1 
                With wbExcel.sheets(1) 
                    .range("A" & lRow) = dBase.TableDefs(lTbl).Name 
                    .range("B" & lRow) = dBase.TableDefs(lTbl).Fields(lFld).Name 
                End With 
            Next lFld 
        End If 
    Next lTbl 
     'Resume error breaks
    On Error GoTo 0 
     'Set Excel to visible and release it from memory
    xlApp.Visible = True 
    Set xlApp = Nothing 
    Set wbExcel = Nothing 
     'Release database object from memory
    Set dBase = Nothing 
End Sub 

Please reload

Please reload

Sean O'Shea has more than 15 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.


All content provided on this blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.


This policy is subject to change at any time.