Aside from coding the COM object myself, does anyone have any suggestions? I've only been allotted a small amount of time to script this COM object so am stuck using it. The object is ActiveCase 3.5 from this site, and it basically takes a string in and bounces it against tables to style and punctuate names.
So my approach has been to connect to the DBF(I have to stick with a flat DBF format) using ADODB, start at the top, and scan to the bottom feeding the COM object one name at a time. The company that sells the COM object claims their COM object should work very fast(or atleast better than my results), so my question is do you see anything in my code below that could be slowing it down other than the COM itself being slow? The code took about 20 seconds to propigate a 500 record database, and I killed the code at 20 minutes when trying a 100,000 record database. The average database this will be used on is between 70,000 - 200,000 records.
Thanks for any input.
Private Sub Style(FileName As String)
Dim ActiveCase As ActiveCase.Convert
Dim conRecordset As New ADODB.Connection
Dim rsRecordset As New ADODB.Recordset
Dim strSQL As String
Set ActiveCase = New ActiveCase.Convert
ActiveCase.StaticKeyName = "xxx"
ActiveCase.StaticKey = "xxx"
Set conRecordset = New ADODB.Connection
Set rsRecordset = New ADODB.Recordset
strSQL = "SELECT * FROM " & FileName
conRecordset.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & JobListArray(z).DataPath & ";Extended Properties=DBase IV"
rsRecordset.CursorLocation = adUseClient
rsRecordset.Open strSQL, conRecordset, adOpenKeyset, adLockOptimistic
rsRecordset.MoveFirst
On Error GoTo TransError
conRecordset.BeginTrans
While Not rsRecordset.EOF
ActiveCase.TextIn = rsRecordset!Name
ActiveCase.TextStyle = "Name"
ActiveCase.Convert
If ActiveCase.ReturnCode <> "" Then
Select Case ActiveCase.ReturnCode
Case "R35": MsgBox "Reference File Not Found", vbCritical, "ActiveCase - Sample"
Case "T00": MsgBox "Case Table Limit: 16,384", vbCritical, "ActiveCase - Sample"
Case "L00": MsgBox "Demo Expired", vbCritical, "ActiveCase - Sample"
Case "L01": MsgBox "Static Key Validation Failed", vbCritical, "ActiveCase - Sample"
Case "L50" To "L69": MsgBox "ActiveCase.ReturnCode: " & ActiveCase.ReturnCode & vbCrLf & "License Validation Failed", vbCritical, "ActiveCase - Sample"
Case Else: MsgBox "ActiveCase.ReturnCode: " & ActiveCase.ReturnCode, vbCritical, "ActiveCase - Sample"
End Select
End If
rsRecordset!Name = ActiveCase.TextOut
rsRecordset.Update
rsRecordset.MoveNext
Wend
conRecordset.CommitTrans
rsRecordset.Close
Set rsRecordset = Nothing
conRecordset.Close
Set conRecordset = Nothing
Set ActiveCase = Nothing
Exit Sub
TransError:
conRecordset.RollbackTrans
Set rsRecordset = Nothing
conRecordset.Close
Set conRecordset = Nothing
Set ActiveCase = Nothing
MsgBox (Err.Description & vbCrLf & "Above error occurred while trying to Stylist your file." & vbCrLf & " >> " & FileName & " <<")
End
End Sub
Enter your message below
Sign in or Join us (it's free).