UpdateCustomerLookupList procedure

The UpdateCustomerLookupList procedure updates a lookup list in the vault from the external database of customer names. It first checks if any updates were done since the last synchronization. This is done by comparing the DateLastModified property of the database file with a date property that is kept for the lookup list object.

If the database has been modified, the procedure assumes that the lookup list must be updated. First, the existing values in the lookup list are cleared. Then, for each record in the Customers table a new item is added to the lookup list.

After the update, the date property on the lookup list object is set. This will prevent the task from updating the lookup list again (until the database is modified).

Public Sub UpdateCustomerLookupList(dr As AMDocumentRepository, LookupName As String)
' This method updates a lookup list in the Meridian Enterprise vault.
' It adds all customer names from the customer database (mdb)
 
' Get Customer lookup list
Dim lulist As AMLookupList
Dim env As IAMEDMEnvironment5
Set env = dr.Environment
Set lulist = env.GetLookupListWithName(LookupName)
 
If lulist Is Nothing Then
Err.Raise -1, "UpdateCustomerLookupList", "Lookup list (" & LookupName & ") not found"
Exit Sub
End If
 
On Error GoTo eh
 
' Check if the Customer Database was modified
Dim fs As New FileSystemObject
Dim db As File
Set db = fs.GetFile(DataBasePath)
 
' Get modified date
Dim modified As Variant
modified = GetLookupList_Modified(dr, lulist)
If IsNull(modified) Or (modified < db.DateLastModified) Then
' Update the lookup list with the current Customer Names
' In this sample we use a MS Access database to retrieve customer data
' This requires a reference to "Microsoft ActiveX Data Object 2.5" of higher.
Dim Conn As New ADODB.Connection
Dim rs As ADODB.Recordset
 
Conn.Open ConnectString
 
Set rs = Conn.Execute("SELECT * FROM " & CustomerTable & " ORDER BY " & fldName)
 
ClearLookupList lulist
 
While Not rs.EOF
lulist.AddValue CStr(rs.Fields(fldName))
rs.MoveNext
Wend
rs.Close
Conn.Close
 
' Set modification DATE for the lookup list
SetLookupList_Modified dr, lulist
 
End If
Exit Sub
eh:
Err.Raise -1, "UpdateCustomerLookupList", "Failed to update customer lookup list from database" & vbCrLf & Err.Description
 
End Sub