Getting web page form results and tagged files into Access
Example 1Web Input Form
Example 2Bibliographic records
Example 1
Original email (note tagged fields indicating which value is in
which
field, and the numeric fields eg rj include a 'V' and could then have
V47 to V52 to indicate whether the user had pressed option in range 1 to
5:
--------------------------------------
required:Surname1,Firstname1,Age2,Department3
Title1:Select one
Surname1:Bloggs
Firstname1:Jo
Age2:50 +
Department3:Physics
Otherdept3:
Position4:Other
Otherposition4:Was funded by industry
Research5:I am involved in microminiaturisation of gel elecrophoresis.
This process is very quick (complete separation of 12 kbp ladder in less
than 30 min. )
Other6: Technology development andInstrumentation
Barriers7:Funding is the biggest problem
8j:ON
Rj:V47
8k:ON
Rk:V52
Rm:V62
8n:ON
Rn:V66
8o:ON
Other8:Pulse Gel Electrophoresis
R9:VN9
Comments9:
B1:Submit
--------------------------------------
This then looked like
~~--------------------------------------~required:Surname1,Firstname1,Age2,Department3~Title1:Select
one~Surname1:Shah~Firstname1:Jitu~Age2:50
+~Department3:Physics~Otherdept3:~Position4:Other~Otherposition4:Was
funded by industry~Research5:I am involved in microminiaturisation of gel
elecrophoresis.~ This process is very quick (complete separation of 12 kbp
ladder in less than 30 min. )~Other6: Technology development
andInstrumentation~Barriers7:Funding is the biggest
problem~8j:ON~Rj:V47~8k:ON~Rk:V52~Rm:V62~8n:ON~Rn:V66~8o:ON~Other8:Pulse
Gel
Electrophoresis~R9:VN9~Comments9:B1:Submit~--------------------------------------~~
I then created an Access table called message with autocounter field
(to
identify messages) and a memo field to hold the email.
I imported the emails into the message table using tab delimiter (,
could not be used as too many commas in the text)
I created a second table called data to hold the correct structure
I created a form with a button to transfer all the data from message
into data. The Basic looked like:
Private Sub Command0_Click()
Dim mymessage As Recordset, mydb As Database, id As Integer, data As
String, title1
Dim surname1, firstname1, age2, department3
Dim otherdept3, position4, otherposition4
Dim research5, q6a, q6b, q6c, q6d
:
Dim q8a, ra, q8b, rb, q8c, rc
:
Dim remaining As String, insertsql As String, updatesql As String
Dim mytable As Recordset
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set mymessage = mydb.OpenRecordset("message", DB_OPEN_TABLE)
Set mytable = mydb.OpenRecordset("table1", DB_OPEN_TABLE)
mymessage.MoveFirst
Do Until mymessage.EOF
DoCmd.SetWarnings False
id = mymessage!id
data = mymessage!data
title1 = Null
surname1 = Null
firstname1 = Null
age2 = Null
:
title1 = Mid$(data, InStr(data, "title1:") + 7, _
InStr(data, "surname1:") - InStr(data, "title1:") - 8)
surname1 = Mid$(data, InStr(data, "surname1:") + 9, InStr(data,
"firstname1:") - InStr(data, "surname1:") - 10)
firstname1 = Mid$(data, InStr(data, "firstname1:") + 11, InStr(data,
"age2:") - InStr(data, "firstname1:") - 12)
age2 = Mid$(data, InStr(data, "age2:") + 5, InStr(data,
"department3:") - InStr(data, "age2:") - 6)
department3 = Mid$(data, InStr(data, "department3:") + 12, _
InStr(data, "otherdept") - InStr(data, "department3:") - 13)
remaining = Mid$(data, InStr(data, "department3:") + 12)
If data Like "*otherdept3:*" Then
remaining = Mid$(data, InStr(data, "otherdept3:") + 11)
otherdept3 = Mid$(data, InStr(data, "otherdept3:") + 11,
InStr(remaining, "~") - 1)
End If
:
If data Like "*6a:*" Then
remaining = Mid$(data, InStr(data, "6a:") + 3)
q6a = Mid$(data, InStr(data, "6a:") + 3, InStr(remaining, "~") -
1)
End If
:
If data Like "*8a:*" Then
remaining = Mid$(data, InStr(data, "8a:") + 3)
q8a = Mid$(data, InStr(data, "8a:") + 3, InStr(remaining, "~") -
1)
End If
If data Like "*ra:*" Then
remaining = Mid$(data, InStr(data, "ra:") + 4)
ra = Mid$(data, InStr(data, "ra:") + 4, InStr(remaining, "~") -
1)
If ra <= 0 Or ra > 99 Then
ra = 0
End If
End If
:
insertsql = "insert into table1 (id, title1, surname1, firstname1, age2,
department3, "
insertsql = insertsql & "otherdept3, position4, otherposition4,
research5, email14) "
insertsql = insertsql & "select " & id & ",'" & title1 & "','" &
surname1 & "','" & firstname1 & "','" & age2 & "','" & _
department3 & "','" & otherdept3 & "','" & position4 & "','" &
otherposition4 & "','" & _
research5 & "','" & email14 & _
"' from message WHERE not Exists (select * from table1 where
table1.id=" & id & ") and message.id= " & id & ";"
DoCmd.RunSQL insertsql
:
If q6a = "ON" Then
updatesql = "update table1 set 6a=true where id=" & id & ";"
DoCmd.RunSQL updatesql
End If
:
If Len(barriers7) > 0 Then
updatesql = "update table1 set barriers7='" & barriers7 & "' where
id=" & id & ";"
DoCmd.RunSQL updatesql
Debug.Print updatesql
End If
If q8a = "ON" Then
updatesql = "update table1 set 8a=true where id=" & id & ";"
DoCmd.RunSQL updatesql
End If
If ra > 0 Then
updatesql = "update table1 set ra=" & ra & " where id=" & id &
";"
DoCmd.RunSQL updatesql
End If
:
mymessage.MoveNext
Loop
mymessage.Close
end sub
Example 2
This again had tagged fields, the data was edited to be on one line per record
and imported into a single field table called reference. A table called research
containing as many fields as there were tags was created to hold all the
split references. Example of a reference
TI:Stroke rehabilitation - a qualitative study
PI:M0048038427
SD:16/10/1998
ED:15/04/2000
AU:Dr Nina Williams
AD:Public Health, Iechyd Morgannwg Health, 41 High Street, Swansea, SA1 1LT
MR:To explore the experiences of stroke patients and the relationship between timing and intensity of rehabilitation with client and carer satisfaction and health status measures.
MT:Following selection, baseline characteristics will be recorded using validated objective health status measures to enhance comparability between districts.
ST:Ongoing
PK:MeSH terms not yet assigned
PR:Wales Office of Research & Development for Health & Social Care
RE:Wales
Access Basic
Option Compare Database
Option Explicit
Private Sub import_Click()
Dim myreference As Recordset, myresearch As Recordset, mydb As
Database
Dim reference As String
Dim project_id As String, title As String
Dim start_date As Date, end_date As Date, authors As String,
address As String
Dim main_research_question As String, mt As String, status As
String
Dim first_fund As String, second_fund As String, third_fund As
String, fourth_fund As String
Dim mesh_heading As String, keywords As String, region As String,
multi_study As String
Dim phone As String, main_comment As String, sa As String, ou As
String
Dim remaining As String, insertsql As String, updatesql As String
Set mydb = DBEngine.Workspaces(0).Databases(0)
Set myreference = mydb.OpenRecordset("reference", DB_OPEN_TABLE)
Set myresearch = mydb.OpenRecordset("research", DB_OPEN_TABLE)
myreference.MoveFirst
Do Until myreference.EOF
DoCmd.SetWarnings False
reference = myreference!reference
If reference Like "*""*" Then
Do Until InStr(reference, """") = 0
Debug.Print "quoted"
Debug.Print "before" & reference
reference = Left$(reference, InStr(reference, """") - 1) & "'" &
Right$(reference, Len(reference) - InStr(reference, """"))
Debug.Print "after" & reference
Loop
End If
title = "Null"
start_date = "1/1/00"
end_date = "1/1/00"
authors = "Null"
address = "Null"
main_research_question = "Null"
mt = "Null"
status = "Null"
first_fund = "Null"
second_fund = "Null"
third_fund = "Null"
fourth_fund = "Null"
mesh_heading = "Null"
keywords = "Null"
region = "Null"
multi_study = "Null"
phone = "Null"
main_comment = "Null"
sa = "Null"
ou = "Null"
If reference Like "*!*" Then
remaining = reference
title = Mid$(reference, 4, InStr(reference, "!pi") - 4)
Debug.Print title
project_id = Mid$(reference, InStr(reference, "!pi") + 4,
InStr(reference, "!sd") - InStr(reference, "!pi") - 4)
Debug.Print project_id
start_date = Mid$(reference, InStr(reference, "!sd") + 4,
InStr(reference, "!ed") - InStr(reference, "!sd") - 4)
Debug.Print start_date
remaining = Mid$(reference, InStr(reference, "!ed") + 4)
end_date = Mid$(reference, InStr(reference, "!ed") + 4,
InStr(remaining, "!") - 1)
Debug.Print end_date
If reference Like "*!mc:*" Then
remaining = Mid$(reference, InStr(reference, "!mc") + 4)
main_comment = Mid$(reference, InStr(reference, "!mc") + 4,
InStr(remaining, "!") - 1)
Debug.Print main_comment
Debug.Print remaining
End If
remaining = Mid$(reference, InStr(reference, "!au") + 4)
Debug.Print remaining
authors = Mid$(reference, InStr(reference, "!au") + 4,
InStr(remaining, "!") - 1)
Debug.Print authors
If reference Like "*!ad:*" Then
remaining = Mid$(reference, InStr(reference, "!ad") + 4)
Debug.Print remaining
address = Mid$(reference, InStr(reference, "!ad") + 4,
InStr(remaining, "!") - 1)
Debug.Print address
End If
If reference Like "*!ph:*" Then
remaining = Mid$(reference, InStr(reference, "!ph") + 4)
phone = Mid$(reference, InStr(reference, "!ph") + 4,
InStr(remaining, "!") - 1)
Debug.Print phone
End If
If reference Like "*!mr:*" Then
remaining = Mid$(reference, InStr(reference, "!mr") + 4)
main_research_question = Mid$(reference, InStr(reference, "!mr") +
4, InStr(remaining, "!") - 1)
Debug.Print main_research_question
End If
If reference Like "*!mt:*" Then
remaining = Mid$(reference, InStr(reference, "!mt") + 4)
mt = Mid$(reference, InStr(reference, "!mt") + 4, InStr(remaining,
"!") - 1)
Debug.Print mt
End If
If reference Like "*!sa:*" Then
remaining = Mid$(reference, InStr(reference, "!sa") + 4)
sa = Mid$(reference, InStr(reference, "!sa") + 4, InStr(remaining,
"!") - 1)
Debug.Print sa
End If
If reference Like "*!ou:*" Then
remaining = Mid$(reference, InStr(reference, "!ou") + 4)
ou = Mid$(reference, InStr(reference, "!ou") + 4, InStr(remaining,
"!") - 1)
Debug.Print ou
End If
If reference Like "*!st:*" Then
remaining = Mid$(reference, InStr(reference, "!st") + 4)
status = Mid$(reference, InStr(reference, "!st") + 4,
InStr(remaining, "!") - 1)
Debug.Print status
End If
If reference Like "*!f1:*" Then
remaining = Mid$(reference, InStr(reference, "!f1") + 4)
first_fund = Mid$(reference, InStr(reference, "!f1") + 4,
InStr(remaining, "!") - 1)
Debug.Print first_fund
End If
If reference Like "*!f2:*" Then
remaining = Mid$(reference, InStr(reference, "!f2") + 4)
second_fund = Mid$(reference, InStr(reference, "!f2") + 4,
InStr(remaining, "!") - 1)
Debug.Print second_fund
End If
If reference Like "*!f3:*" Then
remaining = Mid$(reference, InStr(reference, "!f3") + 4)
third_fund = Mid$(reference, InStr(reference, "!f3") + 4,
InStr(remaining, "!") - 1)
Debug.Print third_fund
End If
If reference Like "*!f4:*" Then
remaining = Mid$(reference, InStr(reference, "!f4") + 4)
fourth_fund = Mid$(reference, InStr(reference, "!f4") + 4,
InStr(remaining, "!") - 1)
Debug.Print fourth_fund
End If
If reference Like "*!pk:*" Then
remaining = Mid$(reference, InStr(reference, "!pk") + 4)
mesh_heading = Mid$(reference, InStr(reference, "!pk") + 4,
InStr(remaining, "!") - 1)
Debug.Print mesh_heading
End If
If reference Like "*!pr:*" And Not (reference Like "*!re:*") Then
keywords = Mid$(reference, InStr(reference, "!pr") + 4)
Debug.Print keywords
End If
If reference Like "*!pr:*" And reference Like "*!re:*" Then
remaining = Mid$(reference, InStr(reference, "!pr") + 4)
keywords = Mid$(reference, InStr(reference, "!pr") + 4,
InStr(remaining, "!") - 1)
Debug.Print keywords
End If
If reference Like "*!re:*" Then
remaining = Mid$(reference, InStr(reference, "!re") + 4)
region = Mid$(reference, InStr(reference, "!re") + 4)
Debug.Print region
End If
insertsql = "INSERT INTO RESEARCH ( title, project_id, start_date,
end_date, main_comment, authors, "
insertsql = insertsql & "address, phone, ou, status, "
insertsql = insertsql & "first_fund, second_fund, third_fund,
fourth_fund, mesh_heading, "
insertsql = insertsql & "keywords, region) "
insertsql = insertsql & "select distinct """ & title & """,""" &
project_id & """,#" & start_date & "#,#"
insertsql = insertsql & end_date & "#,""" & main_comment & ""","""
& authors & ""","""
insertsql = insertsql & address & """,""" & phone & ""","""
insertsql = insertsql & ou & """,""" & status & ""","""
insertsql = insertsql & first_fund & """,""" & second_fund &
""",""" & third_fund & """,""" & fourth_fund & ""","""
insertsql = insertsql & mesh_heading & """,""" & keywords & ""","""
& region
insertsql = insertsql & """ from reference"
insertsql = insertsql & " where not exists (select * from research
where research.project_id=""" & project_id & """);"
Debug.Print insertsql
DoCmd.RunSQL insertsql
If main_research_question <> "null" Then
updatesql = "update research set main_research_question= """ &
main_research_question & """ where research.project_id=""" &
project_id & """;"
DoCmd.RunSQL updatesql
End If
If mt <> "null" Then
updatesql = "update research set mt= """ & mt & """ where
research.project_id=""" & project_id & """;"
DoCmd.RunSQL updatesql
End If
If main_research_question <> "null" Then
updatesql = "update research set sa= """ & sa & """ where
research.project_id=""" & project_id & """;"
DoCmd.RunSQL updatesql
End If
End If
myreference.MoveNext
Loop
myreference.Close
End Sub