We help IT Professionals succeed at work.

Using ODBC to export from Lotus Notes 5 to Access.  Strange error when updating row.

483 Views
Last Modified: 2013-12-25
Lotus Notes Version 5.0.11
Microsoft Access 2003
Windows 2000 Server

I have written a scheduled agent which exports or updates data from Lotus Notes to Access.   The first time the agent runs, data is exported without any trouble.  On subsequent runs, I get an error when trying to update a row.

The error is
[Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'FisherSiteCode = Pa_RaM000  AND Machine = Pa_RaM001  AND Position = Pa_RaM002  AND FabricDesignCode = Pa_RaM003  AND 10_Qty = Pa_RaM004  AND 10_VolumeOrWeight = Pa_RaM005  AND 10_ValueEuro = Pa_RaM006  AND 10_ValueLocal = Pa_RaM007  AND 10_Currency IS NULL A'.

The query I'm using looks nothing like that, and the error is occuring not when the query is executed, but later, when I call UpdateRow on the result set.

The central part of the code is below.  I did not include the subroutines, but the subroutine that runs before the error runs just fine the first time the code is run.  

Thanks in advance.

      Dim doc As NotesDocument
      Set doc = dc.GetFirstDocument
      Dim x As Integer
      While Not doc Is Nothing
            x = x + 1
            If x > 15 Goto EndOfLoop
            'set query
            qry.SQL = {SELECT * FROM } & sTableName & { where FisherSiteCode= '} & doc.FisherSiteCode(0) & _
            {' and Machine='} & doc.Machine(0) & {' and Position='} & doc.Position(0) & {' and FabricDesignCode='} & doc.FabricDesignCode(0) & {'}
            Call GLog.LogAction (qry.SQL)            
            Set qry.Connection = con
            Set result.Query = qry
            result.Execute
            
            'If row already exists, just add current QLF data
            If result.IsResultSetAvailable Then
                  Call ExportQLFData (result, doc, iMonth)
                  Call result.UpdateRow 'ERROR OCCURS HERE
lRowsUpdated = lRowsUpdated + 1
            'If row does not exist, export all the data, then export the current QLF data
            Else
                  Call result.AddRow            
                  Call ExportForecastData (result, doc)                  
                  Call ExportQLFData (result, doc, iMonth)
                  Call result.UpdateRow
                  lRowsAdded = lRowsAdded + 1
            End If
            
            'purges the data in the result set
            Call result.Close
            
getNextDoc:
            Set doc = dc.GetNextDocument (doc)
      Wend
Comment
Watch Question

Commented:
what does Call ExportQLFData (result, doc, iMonth)  do?

Author

Commented:
That sub just takes data from the Notes document and puts it specific columns.  Sometimes it has to do a calculation first.

Here it is:

Sub ExportQLFData (result As ODBCResultSet, doc As NotesDocument, iMonth As Integer)
      
      On Error Goto RecordError
      
      'Last updated Nov 2006
      Const GBP = 1.470588235
      Const CHF = 0.64516129
      Const NOK = 0.125
      Const SEK = 0.106382979
      Const USD =0.8
      Const CAD = 0.689655172
      Const CNY = 0.1
      Const JPY = 0.007142857
      Const AUD = 0.625
      Const MYR = 0.002222222
      Const INR = 0.00018182
      
      'Set quantity
      Call result.SetValue ( iMonth & "_Qty", GetIntegerValue ("tq", doc) )
      
      'Set Local Value
      Dim dLocalValue As Double
      dLocalValue = GetDoubleValue ("tva", doc)
      Call result.SetValue ( iMonth & "_ValueLocal", dLocalValue )
      
      'Set Currency      
      Dim sCurrency As String
      sCurrency = GetStringValue ("Currency", 3, doc)
      Call result.SetValue ( iMonth & "_Currency", sCurrency )
      
      'Set Value in Euros
      Dim dEuroValue As Double
      Select Case sCurrency
      Case "GBP":
            dEuroValue = dLocalValue * GBP
      Case "CHF":
            dEuroValue = dLocalValue * CHF
      Case "NOK":
            dEuroValue = dLocalValue * NOK
      Case "SEK":
            dEuroValue = dLocalValue * SEK
      Case "USD":
            dEuroValue = dLocalValue * USD
      Case "CAD":
            dEuroValue = dLocalValue * CAD
      Case "CNY":
            dEuroValue = dLocalValue * CNY
      Case "JPY":
            dEuroValue = dLocalValue * JPY
      Case "AUD":
            dEuroValue = dLocalValue * AUD
      Case "MYR":
            dEuroValue = dLocalValue * MYR
      Case "INR":
            dEuroValue = dLocalValue * INR
      Case "EUR":
            dEuroValue = dLocalValue
      Case Else:
            Call GLog.LogAction ("Unrecognized currency:  " & sCurrency)
      End Select
      Call result.SetValue ( iMonth & "_ValueEuro", dEuroValue )
      
      'Set weight or volume
      Dim d As Double 'the value from the document
      Dim dFinal As Double 'the converted value for the spreadsheet
      Select Case doc.ProductLine(0)
      Case "Press":
            d = GetDoubleValue ("TWT", doc)
            If doc.MeasurementUnits(0) = "Metric" Then
                  dFinal = d
            Else
                  dFinal = d/2.20462
            End If
      Case "Forming", "Dryer":
            d = GetDoubleValue ("TVO", doc)
            If doc.MeasurementUnits(0) = "Metric" Then
                  dFinal = d
            Else
                  dFinal = d/10.76391
            End If
      End Select
      Call result.SetValue ( iMonth & "_VolumeOrWeight", dFinal )
      
ExitNow:      
      Exit Sub
      
RecordError:
      Dim sError As String
      Dim iLine As Integer
      sError = Error$
      iLine = Erl
      Resume ReportError
      
ReportError:
      On Error Goto 0
      Error 20001, "ERROR ExportQLFData Line " & Cstr(iLine) & ":  " & sError
      
End Sub

Commented:
if you expect just a single row call result.LastRow or result.FirstRow before setting the values and calling the updaterow. otherwise move to the row which you want to update and call updaterow.

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.