Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 3rd, 2003, 01:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default import data from web form

I have a form on my web page that allows a user to request information from me. When they fill out the form, I get an e-mail that I then want to import into my Access database. I am not sure how to do it.

Here is what I get from the web site:

-----Original Message-----
From: Bill@place.COM [mailto:Bill@place.COM]
Sent: Wednesday, September 03, 2003 9:25 AM
To: me@my_domain.com
Subject: Inquiry from Website


Name: John Doe
Title: DESIGN ENGINEER
Company: ABC CORP.
Address1: 2007 KEN PLACE
Address2:
City: MIDDLETOWN
ST: OHIO
Zip: 12345
Country: USA
Phone: 513-555-1234
Extension:
Fax: 513-555-1234
Email: John@junk.COM
Website:
Option_Editor: ON
Option_Virtualcnc: ON
CD: ON
30_60days: ON
Num_CNC: 8
SIC_Code:
Num_EE: 110
Describe_Business: ELECTRODE MANUFACTURER
PSI_Website: ON
__________________
Mitch
 
Old September 3rd, 2003, 11:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mitch,

I leave it to you to automate this from your mail client, but once you get your email into a text file, the following will parse it for you and write the new order to your base table. My text file (order.txt) looks like this:

Name: John Doe
Title: DESIGN ENGINEER
Company: ABC CORP.
Address: 2007 KEN PLACE
City: MIDDLETOWN
ST: OHIO
Zip: 12345
Country: USA

The code first converts the text file to one big 'ol string using basic I/O functions, then passes the string and the field name constants to a function which does the field value extractions.

I put this together based on some functions I ran across in a Smart Access article by Gary Robinson and Scott McManus ("Processing E-mail Orders Using Outlook and Access", May 2002). Their modules contain a bunch of code to automate the whole process using an instance of Outlook. I just sifted out the file I/O, data access bits.

Sub Main()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    ' convert text file to one big 'ol long string with
    ' a little basic I/O
    textstring = TextFileToString("c:\temp\order.txt")

    ' Debug.Print textstring

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblOrders", dbOpenDynaset)

    ' extract field values by passing big 'ol string and known
    ' constants to function, and add new record to base table
    rst.AddNew
    rst("Name") = ExtractFieldValue(textstring, "Name: ")
    rst("Title") = ExtractFieldValue(textstring, "Title: ")
    rst("Company") = ExtractFieldValue(textstring, "Company: ")
    rst("Address") = ExtractFieldValue(textstring, "Address: ")
    rst("City") = ExtractFieldValue(textstring, "City: ")
    rst("ST") = ExtractFieldValue(textstring, "ST: ")
    rst("Zip") = ExtractFieldValue(textstring, "Zip: ")
    rst("Country") = ExtractFieldValue(textstring, "Country: ")

    rst.Update

End Sub

Function TextFileToString(filename As String) As String

Dim linesfromfile, nextline As String
Dim file As Integer

  TextFileToString = ""

  file = FreeFile
  Open filename For Input As file

  Do While Not EOF(1)

    Line Input #1, nextline
    linesfromfile = linesfromfile + nextline _
      + Chr(13) + Chr(10)
  Loop

  Close file

  TextFileToString = linesfromfile

End Function

Function ExtractFieldValue(textstring As Variant, orderitem As String) As String

Dim startstring As Variant, endstring As Variant, fieldvalue As Variant


startstring = InStr(textstring, orderitem)
If startstring > 0 Then

  startstring = startstring + Len(orderitem)
  endstring = InStr(startstring, textstring, Chr(13))
  fieldvalue = Mid(textstring, startstring, endstring - startstring)

End If
If Len(fieldvalue) = 0 Then
  fieldvalue = " "
End If

ExtractFieldValue = fieldvalue

End Function

HTH,

Bob

 
Old September 3rd, 2003, 11:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mitch,

Actually that article is from the May 2003 issue of Smart Access.

Bob

 
Old September 4th, 2003, 07:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Bob,

Cool code. One question...

Quote:
quote:Originally posted by Bob Bedell
    ' convert text file to one big 'ol long string with
    ' a little basic I/O
    textstring = TextFileToString("c:\temp\order.txt")
    This makes the meat of that one e-mail into one long string embedded with CRLFs to separate out one field from the next. In Access VBA the datatype STRING can hold a very long string, but the datatype TEXT in a field in a table is limited to 255 characters. It's unlikely given the data Mitch presented that any one field will hold that much, but it's possible (esp. description of business). In general, how would you handle that possibility?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old September 4th, 2003, 08:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Bob, for the code, I will try it.
 
Old September 4th, 2003, 08:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is a piece of code that I put together that should work, if I can get the last bug out of it. I get an error about the number of values not matching the fileds, but as many times as I have counted it matches.

Public Function GetWebFormData()
    Dim lFileHandle As Long
    Dim sFileName As String
    Dim sLine As String
    Dim sData() As String
    Dim sFieldName As String
    Dim sFieldData As String
    Dim sSQL As String

    Dim sAllFieldName As String
    Dim sAllFieldData As String


    lFileHandle = FreeFile()
    sFileName = "C:\web_form.txt"

    Open sFileName For Input As lFileHandle

    Do While Not EOF(lFileHandle)

        Line Input #lFileHandle, sLine

        sData = Split(sLine, ":")

        sFieldName = Trim$(sData(0))
        sFieldData = Trim$(sData(1))

        sAllFieldName = sAllFieldName & sFieldName & ", "
        sAllFieldData = sAllFieldData & "'" & sFieldData & "', "
     Loop

     'need to strip last comma and space from strings

     sAllFieldName = Left(sAllFieldName, Len(sAllFieldName) - 2)
     'MsgBox sAllFieldName

     sAllFieldData = Left(sAllFieldData, Len(sAllFieldData) - 2)

     sSQL = "INSERT INTO table1 ([" & sAllFieldName & "]) VALUES (" & sAllFieldData & ")"
        MsgBox sSQL
        CurrentDb.Execute sSQL, dbFailOnError

End Function
 
Old September 4th, 2003, 09:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found it! Each field needs to be enclosed in brackets [].

So, here is the code that works.

Public Function GetWebFormData()
    Dim lFileHandle As Long
    Dim sFileName As String
    Dim sLine As String
    Dim sData() As String
    Dim sSQL As String
    Dim sAllFieldName As String
    Dim sAllFieldData As String

    lFileHandle = FreeFile()
    '**Path and name of text file
    sFileName = "C:\web_form.txt"

    Open sFileName For Input As lFileHandle

    Do While Not EOF(lFileHandle)

        Line Input #lFileHandle, sLine

        sData = Split(sLine, ":") '**Look for a : in each line and split from there

        sFieldName = Trim$(sData(0))
        sFieldData = Trim$(sData(1))

        sAllFieldName = sAllFieldName & "[" & sFieldName & "], " '**Need [] around field name otherwise we get an error about number of values not matching the number of fields
        sAllFieldData = sAllFieldData & "'" & sFieldData & "', "
     Loop

     '**Need to strip last comma and space from strings
     sAllFieldName = Left(sAllFieldName, Len(sAllFieldName) - 2)
     sAllFieldData = Left(sAllFieldData, Len(sAllFieldData) - 2)

    sSQL = "INSERT INTO table1 (" & sAllFieldName & ") VALUES (" & sAllFieldData & ")"
        'MsgBox sSQL

    CurrentDb.Execute sSQL, dbFailOnError

End Function
 
Old September 4th, 2003, 09:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Greg,

The code can handle a text file line (and return as field value) of what, 2 billion characters is it. So I guess I'd ask my self the standard question when storing any large character string in a table: do I care if it gets truncated? If I do, the only option is to use a larger field data type, which in Access means 'memo'. If I don't the text data type will do.

Regards,

Bob

 
Old September 4th, 2003, 04:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Nice Mitch! That's a keeper. Didn't know
Split() returned an array. That'll be useful.

Bob:)






Similar Threads
Thread Thread Starter Forum Replies Last Post
Import data from web service barista Excel VBA 1 November 26th, 2007 01:12 PM
The best way to design a data entry web form cesemj ASP.NET 2.0 Basics 2 November 5th, 2007 02:44 PM
Import Data from a form to a word package aysha Access 0 April 4th, 2005 09:23 AM
merging data from web form to excel spreadsheet fmh002 Classic ASP Basics 1 June 30th, 2003 04:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.