Need Export data from a view to MS-access

HCL Domino Domino Forum
1

I have a query need to export data from a view to MSAccess…please help me out on this …

Thanks in advance

2

Subject: Need Export data from a view to MS-access

Export ViewData to MSAccess

Under Options****

Option Public

Uselsx “*LSXODBC”

Const LogFileName=“C:\Studetail_Logfile.txt”

Under Subinitialize*

Sub Initialize

On Error Goto ErrorMessage

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim col As NotesDocumentCollection

Dim doc As NotesDocument

Dim Rolenumber As Variant

Dim S_Name As Variant

Dim Sub1 As Variant

Dim Sub2 As Variant

Dim Sub3 As Variant

Dim Sub4 As Variant

Dim Sub5 As Variant

Dim Tot As Variant

Dim Avg As Variant

Dim Class1 As Variant

'Dim item As NotesItem

Dim conn As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Print "Agent Started"

Set db=session.CurrentDatabase

Set view=db.GetView("All Documents")

Set col=db.UnprocessedDocuments

Set qry.Connection=conn

Set result.Query=qry

Call conn.ConnectTo"DsnName0","","","DbName in MSAceess")

qry.SQL="Select * from TableName"

Call result.Execute

Dim LogFileNum As Variant

LogFileNum = Freefile()

Open LogFileName For Append Access Write As #LogFileNum

Set doc=col.GetFirstDocument

main:

While Not doc Is Nothing

	Rolenumber=doc.GetItemValue("Rolenumber")

	S_Name=doc.GetItemValue("S_Name")

	Sub1=doc.GetItemValue("Sub1")

	Sub2=doc.GetItemValue("Sub2")

	Sub3=doc.GetItemValue("Sub3")

	Sub4=doc.GetItemValue("Sub4")

	Sub5=doc.GetItemValue("Sub5")

	Tot=doc.GetItemValue("Tot")

	Avg=doc.GetItemValue("Avg")

	Class1=doc.GetItemValue("Class")

	Call result.LastRow

	Call result.AddRow

	Call result.SetValue("Rolenumber",Rolenumber(0))

	Call result.SetValue("Stuname",S_Name(0))

Call result.SetValue("Column1",Sub1(0))

	Call result.SetValue("Column2",Sub2(0))

	Call result.SetValue("Column3",Sub3(0))

	Call result.SetValue("Column4",Sub4(0))

	Call result.SetValue("Column5",Sub5(0))

	Call result.SetValue("Column6",Tot(0))

	Call result.SetValue("Column7",Avg(0))

	Call result.SetValue("Column8",Class1(0))

	Call result.UpdateRow

	Set doc=col.GetNextDocument(doc)

Wend

Msgbox ("agent ran successfully")

'Call conn.Disconnect()

Exit Sub

ErrorMessage:

Print #LogFileNum, "****Start***** at : "+ Now()

Print  #LogFileNum, ""

Print #LogFileNum, "errorNumber:             "+Cstr(Err)+"Errorline:"+Cstr(Erl)

Print #LogFileNum, ""

Set doc=col.GetNextDocument(doc)

Resume main

End Sub