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

[graliv1]

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

[HansV]

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

[graliv1]

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

[HansV]

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

[graliv1]

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.