MS Access converting data help

[ljjtek]

I have a webtracking script that sends me an email whenever my site is visited which includes a few different pieces of information (i.e. ip, referer, etc) And the data comes to me looking something like this (for example):

Remote Host: 12.345.678.910
FQDN: 12-345-678-910.client.something.com
Date: Sunday, July 6, 2003
Time: 12:49:12 MST
Referer:

http://www.yahoo.com

User Agent: Mozilla/4.0
Request: /home.php



What I want to be able to do is get this into an Access Database, and I know there is an easier way to do this than by manually entering the data! I can combine all the messages and save it as a text file, if that helps any, but really, I'm just incredibly frustrated! Any suggestions would be greatly appreciated!!

 

[Norris68]

You will need to parse it.

Private Sub ProcessMessage(Message As String)
Dim Lines() As String
Dim A As Long
Dim B As Long
Dim FieldValue As String
Dim RemHost As String
Dim FQDN As String
Dim TheDate As String
Dim TheTime As String
Dim Referer As String
Dim UserAgent As String
Dim Request As String

'Split message up into individual lines
Lines() = Split(Message, vbCrLf)
'Loop through lines
For A = LBound(Lines()) To UBound(Lines())
'Look for the colon that seperates name & value
B = InStr(1, Lines(A), ":&quot
If B > 0 Then
'Extract the field value
FieldValue = Trim$(Mid$(Lines(A), B + 1))
'Assign to correct variable
Select Case Left$(Lines(A), B - 1)
Case "Remote Host": RemHost = FieldValue
Case "FQDN": FQDN = FieldValue
Case "Date": TheDate = FieldValue
Case "Time": TheTime = FieldValue
Case "Referer": Referer = FieldValue
Case "User Agent": UserAgent = FieldValue
Case "Request": Request = FieldValue
End Select
End If
Next A
MsgBox RemHost & vbCr & _
FQDN & vbCr & _
TheDate & vbCr & _
TheTime & vbCr & _
Referer & vbCr & _
UserAgent & vbCr & _
Request

End Sub

Ideally, then you would add the date & time together and put it into a Date variable. You now have all the values in the correct variables - I'll leave it to you to INSERT them into a table...