Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Send Data From MEssage body to Access DB (2000/2007)

    Hi,

    Working on a project extract the contents of a message body (itm.subject) from an outlook email and place it in a separate access Database.

    I've decided getting at the emails is easier from an Outlook Module.

    So how do I Copy a template in the Access Database to create a Fresh formated File (Called JOP) (docmd doesn't seem to cut it.

    And then reference the Database to append the extracted Data

    Dim DB As Access.Application
    Set DB = Access.Application
    DB.Visible = True
    DB.Application.OpenCurrentDatabase ("C:Upload DBs.mdb")
    Set rs = DB.OpenRecordset("JPM")

    The method above dosn't seem to work

    Any help would be gratefully received. If I need References could you just confirm those aswell

    Graliv

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send Data From MEssage body to Access DB (2000/2007)

    OpenRecordset is a method of the DAO Database object, not of the Access Application object.

    You could set a reference to the Microsoft DAO 3.6 Object Library and use code like this:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = DBEngine.OpenDatabase("C:Upload DBs.mdb")
    Set rs = db.OpenRecordset("JPM")
    ' Code to do something with rs goes here
    ...
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing

  3. #3
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Send Data From MEssage body to Access DB (2000/2007)

    Excellent, thanks always getting confused by the method.

    Once the DB is referenced whats the code to copy another database and rename it: IE [JOP Template] to [JOP]??

    Am I right in assuming, I can then use an SQL statement to update the records as normal (.Movefirst and .Movenext), or is it a more complicated procedure.

    Graliv1

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send Data From MEssage body to Access DB (2000/2007)

    By "copy another database" do you really mean a database (a .mdb file) or a table within a database?

    To update records, you can loop through the records of the recordset

    Do While Not rs.EOF
    rs.Edit
    rs.Fields("This") = "That"
    ...
    rs.Update
    rs.MoveNext
    Loop

    or you can execute an SQL statement, but then you don't need to open a recordset:

    Dim strSQL As String
    strSQL = "UPDATE tablename SET field1=value1, field2=value2 WHERE field3=value3"
    db.Execute strSQL, dbFailOnError

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Wikltshire UK
    Posts
    152
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Send Data From MEssage body to Access DB (2000/2007)

    Apologies for not getting back earlier.
    Thank-You for your input, but I resolved the problem by creating a Text documents from the itm.body on the harddrive and then picking them up and applying the content from Access. Has several advantages as it automatically creates a backup of the text.

    Sorted just the squaring up and prettifying to be done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •