I’ve inherited a notes database that uses an ODBC connection to the AS400. The agent is scheduled to run on a daily basis. If I run the agent manually, it fails to connect to the AS400 at this line of code:If Not con.ConnectTo(“AHLSTROM”) Then
I think I need to check the ODBC Drivers. Under which tab should I check to see if the connection is properly setup?
Note: this is my first time deailing with ODBC connections.
Here’s the code:
options:
Option Public
Uselsx “*lsxodbc”
initialize
Sub Initialize
Dim con As New ODBCConnection
Dim current As String
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
’ Dim lastName As String
’ Dim msg As String
' Declares Part II (GENERIC) test
Dim session As New NotesSession
Dim agentLog As New NotesLog("Agent log")
Dim Collection As NotesDocumentCollection
Dim DeleteCollection As NotesDocumentCollection
Dim doc As NotesDocument
Dim NewDoc As NotesDocument
Dim db As NotesDatabase
Dim currdb As NotesDatabase
Dim dbdir As notesdbdirectory
Dim path As String
Dim dateTime As New NotesDateTime("")
' Delete all calendar documents before collection appointments
searchFormula$ = "Form = ""Company"""
Set currdb = session.CurrentDatabase
Set DeleteCollection = currdb.Search(searchFormula$,datetime,0)
Call DeleteCollection.RemoveAll(True)
If Not con.ConnectTo("AHLSTROM") Then
Messagebox "Could not connect to Ahlstrom",, "Error connecting"
Exit Sub
End If
Set qry.Connection = con
Set result.Query = qry
Dim inputNo As String
qry.SQL = "SELECT * FROM ED.CMF001"
result.Execute
’ msg = “Daily Orders:” & Chr(10)
If result.IsResultSetAvailable Then
Do
result.NextRow
'need to convert company number to string so we can use it in dbcolum
CompanyNumber= Trim(Str(result.GetValue("C1CUST")))
CompanyName = result.GetValue("C1NAME")
CompanyAddress1 = result.GetValue("C1ADD1")
CompanyAddress2 = result.GetValue("C1ADD2")
CompanyCity = result.GetValue("C1CITY")
CompanyState = result.GetValue("C1STAT")
CompanyPhone = result.GetValue("C1PHON")
CompanyCountry = result.GetValue("C1CTRY")
CompanyZip = result.GetValue("C1ZIP")
'save as a document
Set db = session.currentdatabase
Set NewDoc = New NotesDocument(db)
NewDoc.Form = "Company"
NewDoc.CompanyNumber = CompanyNumber
NewDoc.CompanyName = CompanyName
NewDoc.CompanyAddress1 = CompanyAddress1
NewDoc.CompanyAddress2 = CompanyAddress2
NewDoc.CompanyCity = CompanyCity
NewDoc.CompanyState = CompanyState
NewDoc.CompanyPhone = CompanyPhone
NewDoc.CompanyCountry = CompanyCountry
NewDoc.CompanyZip = CompanyZip
Call NewDoc.save(True,True)
’ msg = msg & Chr(10) & BillToName & " - " & lastName & " - $" & Amount & " - " & CSR & " - " & EstShipDate & " - " & ProdID & " - " & CatalogNum & " - " & Quantity & " - " & ExtendedPrice
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
Else
’ Messagebox “No Orders Found for Today”, “No data”
Exit Sub
End If
’ Messagebox msg, “Daily Orders”
con.Disconnect
End Sub