Dear Friends
I have to update daily MS Access Database with Lotus Notes Database. People are inserting data in LN db & the MS Access db should update appropriately. I have written the below code however it is not inserting records in Access db.
I am getting the error message :
LS:DO-ODBC Could not complete the requested operation.46
[Microsoft][ODBC Microsoft Access Driver]Syntax error in Insert INTO statement
Option Public
Option Explicit
Uselsx “*LSXODBC”
On Error Goto e
Dim ws As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim currdoc As NotesDocument
Dim view As NotesView
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim x As Variant
Set db=session.CurrentDatabase
Set view = db.GetView(“BB Docs”)
Set doc = view.GetFirstDocument
Dim bbt As String
Dim frm As String
Dim loc1 As String
Dim sub1 As String
Dim dd As String
Dim desc As String
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
con.ConnectTo(“DataUpd”) 'DSN Name
x=1
While Not doc Is Nothing
bbt=doc.BBTitle(0)
frm=doc.From(0)
loc1=doc.Location(0)
sub1=doc.subject(0)
dd=doc.DisplayDate(0)
desc=doc.Body
'qry.SQL="Insert into BulletinBoards(ID,BBTitle,From,Location,Subject,Body) values ('123',bbt , frm,loc1,sub1,desc)"
qry.SQL="Insert into BulletinBoards(ID,BBTitle,From,Location,Subject,Body) Values (x,'"+doc.BBTitle(0)+"','"+doc.From(0)+"','"+doc.Location(0)+"','"+doc.subject(0)+"','"+doc.Body+"')"
x=x+1
If Not result.Execute Then
Msgbox( result.GetExtendedErrorMessage)
Exit Sub
End If
’ Exit Sub
e:
Msgbox Error & Erl
Set doc = view.GetNextDocument(doc)
Wend
con.Disconnect
Kindly advise whats going wrong…
Sanjay