Export Data from Custom Outlook Form to Access (DAO)? 2

[sbauerle]

I have implemented a workflow using custom outlook forms. For archiving purpose i would like to export the data (~150 fields) to an Access database using VBScript. How can i connect to the database/recordset? And how do i add a record? Plattform is NT4 and Off2000.

 

[ClulessChris]

Did you ever find the answer to this? I'm now trying something very similar but having trouble finding how to link to access without use DSN. Can you help?

 

[sbauerle]

Hi Chrisy,
I found a way. Here is my script:

Sub AddDatabaseRecord
DbOpenTable = 1
On Error Resume Next
Set Dbe = Application.CreateObject("DAO.DBEngine.36&quot

Set MyDB = Dbe.Workspaces(0).OpenDatabase ("C:\Archiv.mdb&quot
Set RS = MyDB.OpenRecordset("Archiv", DbOpenTable)

Rs.AddNew

Rs("Field1&quot = Item.userProperties.Find("Field1&quot.Value
Rs("Field2&quot = Item.userProperties.Find("Field2&quot.Value

Rs.Update
Rs.MoveLast
Rs.Close
MyDB.Close

End Sub

You have to add references in Visual Basic to run this script:
open a project in the VB editor, click on references and select MS DAO 3.6 Object Library
Also allow blank values in your database fields.


Stefan

 

[gobjob]

Can you help me? I've successfully been able to take custom fields and move them to Access from an Outlook custom form. However, I am trying to move currency values in Outlook. If I create the field in Access and label it currency, Outlook crashes. It only crashes on the second use. In other words, it lets me successfully process one form and then when trying the 2nd it crashes. If I change the Access label to text, no problem. Do I need another line of code since I am using a checkvalue subroutine.?

 

[gobjob]

I agree with the coding by Stefan. In that code I also included a check on DAO as follows:
Set Dbe = Item.Application.CreateObject("DAO.DBEngine.36&quot
If Err.Number <> 0 then
Msgbox Err.Description & &quot; --Some functions may not
work correctly. Contact your folder administrator to
make sure you have DAO 3.6 installed on this
machine.&quot;
Exit Sub
End if

Also instead of using the following:
Rs(&quot;Field1&quot = Item.UserProperties.Find(&quot;Field1&quot.Value
I am calling a Subroutine Check FieldValues which in turn calls CheckValue. I read somewhere that you needed to check the data. I am not sure how the Field1 statement above would work since I am choosing the fields I want to move.

This is not the entire sub but you'll get the idea.
Sub FieldValues ()
On Error Resume Next
CheckValue &quot;Document ID&quot;, &quot;Document ID&quot;
CheckValue &quot;Element&quot;, &quot;Element&quot;
CheckValue &quot;Order Number&quot;, &quot;TEO Number&quot;
CheckValue &quot;Total Capital&quot;, &quot;Total Capital&quot;
End Sub

The first value is the Outlook field the second is the Access field in the table.

Sub CheckValue ()
If Not UserProperties.Find(FormField) is Nothing then
If UserProperties.Find(FormField).Value <> &quot;&quot; then
If IsDate(UserProperties.Find(FormField).Value) then
If UserProperties.Find(FormField).Value <> &quot;1/1/4501&quot; then
Rs(DBfield) = UserProperties.Find(FormField).Value
else
Rs(DbField) = Null
end if
else
Rs(Dbfield)= UserProperties.Find(FormField).Value
end if
end if
end if
end sub

Now everything moves around quite nicely accept when I try to write to Total Capital. In Outlook this is a currency field. I have specified a currency field in Access. The first record I write is perfect. Then I try to do anything else in Outlook that requires a save and Outlook crashes. If I change the access currency field to a text field the problem goes away but then the data can't be added, subtracted, etc. even when moved to Excel. It took me a long time to realize that this was why Outlook crashed. Does my Sub CheckValue cause this problem? If I change the currency fields to number fields, I have the same problem. Date fields work fine. Does anyone have an idea what I could be doing wrong?

Could you also explain to me how the statement
RS(&quot;Field1&quot=Item.UserProperties.Find(&quot;Field1&quot.Value works. Do I need to have a line for each field in the form? How would I select the fields I want to write to Access? How do I know what Field1 is? Thanks.

I have done extensive application programming for customizing work flow in our organization and now I'm trying to write some of this to Access. Also now creating the ability to write data into a word form for printing directly from the form. Does anyone need that code?

 

[sbauerle]

A statement like
RS(&quot;Field1&quot=Item.UserProperties.Find(&quot;Field1&quot.Value
needs to used for each form field you want to export to Access. In this example the field name in both, Access and Outlook form, is &quot;Field1&quot;. But it could be different.
I am not using the CheckValue routine because I am checking all fields when entered in the form. I also don't have problems with currency fields. In one form I am exporting 5 values in currency fields, and it works fine.

Stefan

 

[peejay36]

How would you need to write this script as an exchange event?

 

[peejay36]

To explain myself better, I would like to use the script described here but I want to run it as an agent in a public folder as new messages are posted. I've used this script in a form with great results, owever, I have numerous mobile users working offline and can't access a network share to write to database as send_item function. I tried using this script as a folder agent on the server side and nothing happens. What do I need to do to make it work in this senario? Thanks for the advise.