Array to transfer field data from linked table?

[geekhunter]

Access 2002 question.

I have a linked table which auto-updates from the contents of a folder in Outlook. This is working just fine and the result is a table that I cannot edit but can query.

One of the fields in the linked table contains the subject of my emails as a text string. The emails in my case will have a very standardized subject line which can, I hope, be copied into an array so I can pull specific values and populate another table. I cannot change the format of this subject line as it is auto-generated from another 3rd party application.

The standard format of the subject field entry in the linked table would be something like "item1:item2:item3:item4" with the colon as a delimiter (again something I can't change).

I'd like to have the ability to separate say, "item1" and "item3" and have these values, along with other fields in the same email record, move to a second table for more refined editing, querying, etc. The other fields that I'll need are the date and time the email was received.

My resulting second table should mimic the first table like so.

Linked Table:
Field 1: "item1:item2:item3:item4"
Field 2: date from email
Field 3: time from email

Table 2:
Field 1: item1
Field 3: item3
Field 4: date from email
Field 5: time from email

One trick is that I'll need Table 2 to update when the Linked Table auto-updates with new emails.

Any suggestions and pointers would be greatly appreciated!

BTW: I'm new to access but I'd assume that you would create some kind of array from the subject string and then access the array to populate the fields? Since I'm new to access, I'm really not sure _where_ this kind of code would reside.

Thanks again!

Todd

 

[CyberLynx]

Welcome to the Forum

Your assumption is correct...at least that's how I would do it. Below is a Function named GetFromLinkedEMailTable (for lack of a better name at the moment) that can do the job for you automatically but you will need to call this Function from within the code that is inserting the the initial data into your Linked Table otherwise you will need to have a polling mechanism to poll the Linked table to see if new E-Mail data was inserted there.

In your post you indicated that the Linked Table format was:

Linked Table:
Field 1: "item1:item2:item3:item4"
Field 2: date from email
Field 3: time from email

and the 2nd Tales' format was:

Table 2:
Field 1: item1
Field 3: item3
Field 4: date from email
Field 5: time from email

This now raises a couple questions.

1st) In Table2 you show only two Fields to hold the possbility of four Items. Is this a mistake and what you really meant was?:

Table 2:
Field 1: item1
Field 2: item2
Field 3: item3
Field 4: item4
Field 5: date from email
Field 6: time from email

Or did you only want the 1st and 3rd Item from he Subject String?:

Table 2:
Field 1: item1
Field 2: item3
Field 3: date from email
Field 4: time from email

2nd) Will the Subject data only ever contain a maximum of 4 delimited Items? If not then how will you determine the Number Of Fields you will need to hold all the delimited Subject Items?

The code I provide here assumes that there will never be more than 4 delimited Items within the Subject string held in the Linked Table. It also assumes that the Linked Table structure is:

Linked Table: (Name - tblEMail1)
Field 1: EmailID (AutoNumber - PK)
Field 2: EmailSubject (Memo)
Field 3: EmailDate (Date/Time)
Field 4: EmailTime (Date/Time)

And the Table2 structure is:

Table 2: (Name - tblEMail2)
Field 1: EmailID (AutoNumber - PK)
Field 2: OriginalEmailID (Number - Long Integer)
Field 3: Subject1 (Text - 255)
Field 4: Subject2 (Text - 255)
Field 5: Subject3 (Text - 255)
Field 6: Subject4 (Text - 255)
Field 7: EmailDate (Date/Time)
Field 8: EmailTime (Date/Time)


Here s the Code:

Private Function GetFromLinkedEMailTable() As Boolean
   [COLOR="DarkGreen"]'Microsoft DAO 3.x required.
   'Returns True is succcessfull and False if not
   
   'Trap any Errors[/COLOR]
   On Error GoTo Error_GetFromLinkedEMailTable
   
   [COLOR="DarkGreen"]'Declare Variables...[/COLOR]
   Dim StrgSQL As String
   Dim LastRec As Long
   Dim rst As Recordset
   Dim SubjectArray() As String
   Dim i As Integer
   
   [COLOR="DarkGreen"]'Get the Record ID of the Last E-Mail entred into
   'the Linked Table.[/COLOR]
   LastRec = DMax("[EMailID]", "[tblEMail1]")
   
   [COLOR="DarkGreen"]'This Line ensures that we don't process the Same
   'E-Mail twice (for whatever reason)[/COLOR]
   If DCount("[EmailID]", "[tblEMail2]", "[OriginalEmailID]=" & LastRec) > 0 Then Exit Function
   
   
   [COLOR="DarkGreen"]'Get that record using Recordset[/COLOR]
   Set rst = CurrentDb.OpenRecordset("SELECT * FROM [tblEMail1] WHERE [EMailID]=" & _
             LastRec & ";", dbOpenSnapshot)
   
   With rst
      [COLOR="DarkGreen"]'Start the creation of our SQL INSERT String.[/COLOR]
      StrgSQL = "INSERT INTO [tblEMail2] (OriginalEMailID,Subject1,Subject2," & _
                "Subject3,SubJect4,EMailDate,EMailTime) VALUES (" & !EMailID
      
      [COLOR="DarkGreen"]'Make sure there is indeed Subject Data...[/COLOR]
      If IsNull(!EmailSubject) = False Then
         [COLOR="DarkGreen"]'There is, so split the delimited items into the
         'SubjectArray array.[/COLOR]
         SubjectArray = Split(!EmailSubject, ":")
         
         [COLOR="DarkGreen"]'Enumerate though the 4 Array elements[/COLOR]
         For i = 0 To 3
            [COLOR="DarkGreen"]'If there is an Element then add it to
            'our SQL INSERT string.[/COLOR]
            If i <= UBound(SubjectArray) Then
               StrgSQL = StrgSQL & ",'" & SubjectArray(i) & "'"
            Else
               [COLOR="DarkGreen"]'If there isn't then make sure NULL is added.[/COLOR]
               StrgSQL = StrgSQL & ",NULL"
            End If
         Next i
      Else
         [COLOR="DarkGreen"]'There isn't, so ensure that the Subject Fields in the 2nd
         'table end up containing NULL.[/COLOR]
         StrgSQL = StrgSQL & ",NULL,NULL,NULL,NULL"
      End If
      
      [COLOR="DarkGreen"]'Finish creating the SQL INSERT string by placing the
      'Date and Time into their respective fields.[/COLOR]
      StrgSQL = StrgSQL & ",#" & !EmailDate & "#,#" & !EmailTime & "#);"
   End With
   
  [COLOR="DarkGreen"] 'Fire the SQL String so as to Insert the data compiled from
   'the Linked Table into the Local Table in our desired Format.[/COLOR]
   CurrentDb.Execute StrgSQL, dbFailOnError
   
   [COLOR="DarkGreen"]'Close the Record[/COLOR]
   rst.Close
   
   [COLOR="DarkGreen"]'Return [B]TRUE[/B] to indicate success.[/COLOR]
   GetFromLinkedEMailTable = True
   
Exit_GetFromLinkedEMailTable:
   [COLOR="DarkGreen"]'free memory[/COLOR]
   Set rst = Nothing
   [COLOR="DarkGreen"]'get out of here....[/COLOR]
   Exit Function '(or function - whatever)
   
Error_GetFromLinkedEMailTable:
   [COLOR="DarkGreen"]'Display any Error that may arise.[/COLOR]
   MsgBox Err.Number & " --> " & Err.Description
   Resume Exit_GetFromLinkedEMailTable
End Function

If there are 4 delimited Items within the Linked Tables' Subject Field then all 4 items are placed into their respective fields within Table2. On the Same hand, if there was only 2 delimited Items within the Linked Tables' Subject field then only 2 Subject Fields in Table2 are filled. If there is no subject String within the Linked Tables' Subject field then all of the Subject Fields within Table2 will contain NULL but the Date and Time Fields will be filled.

The Function always pulls from the last record entered into the Linked Table.

Hope this helps.

.