Litigation Support Tip of the Night

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:

Trim(querysplit([Field1],"~",0))

. . . 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),
Next
Debug.Print
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
Next
Next
Debug.Print
End With
Next
Else
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 "
Next
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

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

item.TableName = tdf.Name
rs.MoveFirst
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
Next
item.ResultRows.Add items
rs.MoveNext
Next
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
Next
results.Add item:=item, Key:=tdf.Name
End If
rs.Close
End If
Next

Set SearchAllTables = results

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

Exit Function
ErrHandler:
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 www.litigationsupporttipofthenight.com

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 https://msdn.microsoft.com/en-us/library/office/ff834799.aspx .     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];"
         
    dbs.Close
 
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
        Else 
             '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 

October 5, 2017

Ken Snell has created a series of Access macros which can be used to import Excel files.   He has posted them to this site.  One of these is named, 'Import Data from All Worksheets in All EXCEL Files in a single Folder into Separate Tables via TransferSpreadsheet (VBA)'.      I've posted it below with the beginning Sub and End Sub lines necessary for it to function in Visual Basic.

Sub ImportExcels()

Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim intWorkbookCounter As Integer
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPath As String, strFile As String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set objExcel = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\MyFolder\ with the actual path to the folder that holds the EXCEL files
strPath = "C:\MyFolder\"

' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "passwordtext"

blnReadOnly = True ' open EXCEL file in read-only mode

strFile = Dir(strPath & "*.xls")

intWorkbookCounter = 0

Do While strFile <> ""

      intWorkbookCounter = intWorkbookCounter + 1

      Set colWorksheets = New Collection

      Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile, , _
            blnReadOnly, , strPassword)
      For lngCount = 1 To objWorkbook.Worksheets.Count
            colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
      Next lngCount

      ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
      objWorkbook.Close False
      Set objWorkbook = Nothing

      ' Import the data from each worksheet into a separate table
      For lngCount = colWorksheets.Count To 1 Step -1
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                  "tbl" & colWorksheets(lngCount) & intWorkbookCounter, _
                  strPath & strFile, blnHasFieldNames, _
                  colWorksheets(lngCount) & "$"
      Next lngCount

      ' Delete the collection
      Set colWorksheets = Nothing
      ' Uncomment out the next code step if you want to delete the 
      ' EXCEL file after it's been imported
      ' Kill strPath & strFile

      strFile = Dir()

Loop

If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

End Sub

Note especially that on the line beginning 'blnHasFieldNames =' the setting should be 'True' if the Excel files you're importing have column headings, and on the line beginning 'strPath ='  you should specify the path to the folder containing the Excel files you want to import.

A macro like this comes in really useful when you're importing dozens of Excel files, but in this example we're working with just four files.

 The macro will quickly create tables like these. 

July 9, 2017

You can set up a tabular form in Microsoft Access that will give you an automatically updating sum total for any field with figures. 

In Access go to Create . . . Form Wizard and select the Table you want to pull data from.

Choose the Tabular format. 

In design view for the form, pull down the section for the Form Footer.   Copy the field from the Detail that you want to add up and paste it in the Form Footer section.   Double-click on it so that the Property Sheet opens up.   In the Control Source field enter:

=SUM(Votes)

When the form is saved, you'll see that the figures in the field you selected are added up in a new field at the bottom.

If you change the numbers in the votes field in the detail of the form, the sum total will update automatically.

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.