I was contemplating a better way of storing our old emails and unhappy with some of the systems in place I started considering whether I could dump them into a database.
Thing is when you export from Outlook some of the standard fields in particular To and From are concatenated. Wouldn’t it be nice to separate those fields into their own table of names and addresses and reference them back to a main table of messages.
This is what I came up with.
For demonstrations purposes I will use two tables
t001parent
pkid - autonumber primary key
ccaddresses - memo or long text
and the child table
t002newchildren
pkid - autonumber primary key
ccaddress - string(150 should do it)
pkidt001 - number
and here is the blank second table
Next we create a user defined function
Public Function CreateChildTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim strSQL As String
Dim strField1 As String
Dim strField2 As String
Dim varData As Variant
Dim i As Integer
Dim intPKID As Integer
Set db = CurrentDb
'Select all the ccaddresses from the parent table
strSQL = "SELECT pkid,ccaddresses FROM t001parent"
Set rsTarget = db.OpenRecordset("t002newchildren", dbOpenDynaset, dbAppendOnly)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
While Not .EOF
intPKID = !pkid
varData = Split(!ccaddresses, ";") ' Get all semi colon delimited fields
'add email addresses if there is only one email address there
With rsTarget
.AddNew
!ccaddresss = Trim(varData(0)) ' remove spaces before writing new fields
!pkidt001 = intPKID
.Update
End With
'loop through addtional email addresses and add them as children to table
For i = 1 To UBound(varData)
With rsTarget
.AddNew
!ccaddresss = Trim(varData(i)) ' remove spaces before writing new fields
!pkidt001 = intPKID
.Update
End With
Next
.MoveNext
Wend
.Close
rsTarget.Close
End With
Set rsTarget = Nothing
Set rs = Nothing
db.Close
Set db = Nothing
End Function
After running this we should
We’ve just nicely split the parent table ccaddesses field into constituent emails and normalised it as a child of the parent table.
