heselton
asked on
Scraping from a Lotus notes form to an Access table
OK...I'm trying to extract information from a Lotus Notus form and write it into an access database
for storage for reporting. For example.... I have a lotus notes form called CreditScore
fields txtloannumber
txtcustomer
txtscore
txtapprover
I would like to have this information written into an access table upon hitting a button on a form within
Access
Form Name: FRMCreditscore
fields txtloannumber
txtcustomer
txtscore
txtapprover
button: btnscrape
I'm hoping you could assist me with the coding based on the above information. Your assistance is greatly
appreciated. Thanks
for storage for reporting. For example.... I have a lotus notes form called CreditScore
fields txtloannumber
txtcustomer
txtscore
txtapprover
I would like to have this information written into an access table upon hitting a button on a form within
Access
Form Name: FRMCreditscore
fields txtloannumber
txtcustomer
txtscore
txtapprover
button: btnscrape
I'm hoping you could assist me with the coding based on the above information. Your assistance is greatly
appreciated. Thanks
Following is some code that might help you:
Let me know if you have any questions,
Melissa
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~
Uselsx "*LSXODBC"
Sub Click( Source as Button)
Dim workspace as New NotesUIWorkspace
Dim doc as NotesDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
On Error Goto errorHandler
'get a handle to the current document
Set doc = workspace.CurrentDocument
'get a handle to the Access table
Set qry.Connection = con
Set result.Query = qry
'replace in quotes the name of the ODBC data source you have set up
con.ConnectTo("AccessDB")
'get a handle to the table you want to write to....replace table with the correct name
qry.SQL = "SELECT * FROM Table"
result.Execute
'add a row to the access table
result.AddRow
'add the values to the table
Call result.SetValue("txtcustom er", doc.txtcustomer(0))
Call result.SetValue("txtscore" , doc.txtscore(0))
Call result.SetValue("txtapprov er", doc.txtapprover(0))
'update the table
result.UpdateRow
'close the table
result.Close(DB_CLOSE)
'close the ODBC connection
con.Disconnect
Exit Sub
errorHandler:
Messagebox result.GetExtendedErrorMes sage,, result.GetErrorMessage
Exit Sub
End Sub
Let me know if you have any questions,
Melissa
~~~~~~~~~~~~~~~~~~~~~~~~~~
Uselsx "*LSXODBC"
Sub Click( Source as Button)
Dim workspace as New NotesUIWorkspace
Dim doc as NotesDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
On Error Goto errorHandler
'get a handle to the current document
Set doc = workspace.CurrentDocument
'get a handle to the Access table
Set qry.Connection = con
Set result.Query = qry
'replace in quotes the name of the ODBC data source you have set up
con.ConnectTo("AccessDB")
'get a handle to the table you want to write to....replace table with the correct name
qry.SQL = "SELECT * FROM Table"
result.Execute
'add a row to the access table
result.AddRow
'add the values to the table
Call result.SetValue("txtcustom
Call result.SetValue("txtscore"
Call result.SetValue("txtapprov
'update the table
result.UpdateRow
'close the table
result.Close(DB_CLOSE)
'close the ODBC connection
con.Disconnect
Exit Sub
errorHandler:
Messagebox result.GetExtendedErrorMes
Exit Sub
End Sub
Whoops!
replace the line:
Set document = workspace.CurrentDocument
with
Set document = workspace.CurrentDocument. document
Sorry!
Melissa
replace the line:
Set document = workspace.CurrentDocument
with
Set document = workspace.CurrentDocument.
Sorry!
Melissa
ASKER
Thanks melbor1....Looks good...however...I need to code in acccess to pull info from Lotus Notes....Any chance you can help me there. Thanks for your assistance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry it has taken longer than expected to get back to you. Thanks for the help.. The reference book should help me accomplish what I'm trying to do.
http://www.advisor.com/Articles.nsf/aid/KOCHD24
http://www.acc-technology.com/ntwiz.htm