More efficient way of FIND records in ADODB connection

stephenlecompte
12-18-2003, 04:34 AM
The following code looks for a lot number in MILCUSTOMERINFO (rst = ADODB.Recordset) table in Storage lots1.mdb database and compares with (srt = ADODB.Recordset) Storage lots.mdb database in ZZZCUSTOMERINFO table. Any MILCUSTOMERINFO records that include a non-duplicate lot number - LOT column (rst.Find "LOT = " & srt.Fields ("LOT") ) that are not in ZZZCUSTOMERINFO then the program will add the new records. Any records that are found will copy over just the extra information. Other than the fact that I could do my records with a FOR/NEXt statement to bring the extra information over to a similiar LOT number - (Ex: For I = 1 to 25: rst.Fields(I) = srt.Fields(I):Next I) - What other ways could I have streamline my code more efficiently. One aspect that the searching is real, real slow.

To do then above, I searched all 3000 records in ZZZCUSTOMERINFO for every one record in MILCUSTOMERINFO to make sure that the record is not duplicated in the database.

1.) I know that any duplicate information will start not at the start of the database but after 2850 records. Is there a way to set the cursor to this record and skip scanning the first 2850 records every time? But would this cause problems say if one record was deleted...or I could do a find by ID = ....but that ID may no longer exists.

2.) Would it be more efficient to just add the records and then through error handling if an error would come up where they would add a duplicate LOT number than to search the database for and just transfer the data over?


Private Sub mnuCent_Click()
Dim stor1 As Variant
Dim stor2 As Variant
If MsgBox("Are you sure you want to import Storage lots1.mdb from C:\ZZZ OPERATIONS PROGRAMS\?", vbQuestion + vbYesNo) = vbYes Then STGIMPORT.Show
'Connection to Storage lots
Set cnn = Nothing
Set cnn = New ADODB.Connection
cnn.ConnectionString = NAMCONN & ";" & _
"Data Source=" & SERVER & NAMDATA & ";" & ENDCONN
cnn.CursorLocation = adUseClient
cnn.Open

'Opens new connection to Storage lots1
Set dnn = Nothing
Set dnn = New ADODB.Connection
dnn.ConnectionString = NAMCONN & ";" & _
"Data Source=" & SERVER & "Storage lots1.mdb" & ";" & ENDCONN
dnn.CursorLocation = adUseClient
dnn.Open

'Opens connection to current Storage program
Set srt = Nothing
Set srt = New ADODB.Recordset
srt.Open "MLCUSTOMERINFO", dnn, adOpenDynamic, adLockOptimistic
srt.MoveFirst
If srt.EOF = True Then MsgBox "No data to transfer!": Exit Sub

Do Until srt.EOF
'If is new data then convert to new table
If srt.Fields("CONVERSION") = True Then
Set rst = Nothing
Set rst = New ADODB.Recordset

rst.Open "ZZCUSTOMERINFO", cnn, adOpenDynamic, adLockOptimistic
rst.MoveFirst
If rst.EOF = True Then GoTo skip_all

stor1 = srt.Fields("LOT")
rst.Find "[LOT] = '" & stor1 & "'"
'If record found
If rst.EOF = True Then
rst.AddNew
rst.Fields("CUSTOMER") = srt.Fields("CUSTOMER")
rst.Fields("FIRSTNAME") = srt.Fields("FIRSTNAME")
rst.Fields("SOCIALSECURITY") = srt.Fields("SOCIALSECURITY")
rst.Fields("CONVERSION") = True
rst.Fields("PICKUP") = srt.Fields("PICKUP")
rst.Fields("GOVSO") = srt.Fields("GOVSO")
rst.Fields("DEST") = srt.Fields("DEST")
rst.Fields("ORIG") = srt.Fields("ORIG")
rst.Fields("VAN") = srt.Fields("VAN")
rst.Fields("PCCT") = srt.Fields("PCCT")
rst.Fields("ATMPT") = srt.Fields("ATMPT")
rst.Fields("ADDRESS") = srt.Fields("ADDRESS")
rst.Fields("CONTRACTOR") = srt.Fields("CONTRACTOR")
rst.Fields("COMDUE") = srt.Fields("COMDUE")
rst.Fields("PHONE") = srt.Fields("PHONE")
rst.Fields("WT") = srt.Fields("WT")
rst.Fields("CPU") = srt.Fields("CPU")
rst.Fields("ESTWT") = srt.Fields("ESTWT")
rst.Fields("DATEIN") = srt.Fields("PICKUP")
rst.Fields("STGTYPE") = "NTS"
rst.Fields("RANK") = srt.Fields("RANK")

rst.Fields("LOT") = srt.Fields("LOT")
rst.Update
rst.Close
Set rst = Nothing
GoTo skip_some
End If
'If record found

If rst.Fields("LOT") = srt.Fields("LOT") Then
rst.Fields("CONVERSION") = True
rst.Fields("PICKUP") = srt.Fields("PICKUP")
rst.Fields("GOVSO") = srt.Fields("GOVSO")
rst.Fields("DEST") = srt.Fields("DEST")
rst.Fields("ORIG") = srt.Fields("ORIG")
rst.Fields("VAN") = srt.Fields("VAN")
rst.Fields("PCCT") = srt.Fields("PCCT")
rst.Fields("ATMPT") = srt.Fields("ATMPT")
rst.Fields("ADDRESS") = srt.Fields("ADDRESS")
rst.Fields("CONTRACTOR") = srt.Fields("CONTRACTOR")
rst.Fields("COMDUE") = srt.Fields("COMDUE")
rst.Fields("PHONE") = srt.Fields("PHONE")
rst.Fields("WT") = srt.Fields("WT")
rst.Fields("CPU") = srt.Fields("CPU")
rst.Fields("ESTWT") = srt.Fields("ESTWT")
rst.Fields("DATEIN") = rst.Fields("PICKUP")
rst.Fields("STGTYPE") = "NTS"
rst.Fields("RANK") = srt.Fields("RANK")
rst.Update
rst.Close
Set rst = Nothing
End If
skip_some:
End If
skip_all:
srt.Fields("CONVERSION") = False
srt.MoveNext
If srt.EOF = True Then GoTo skip_all_data:
Loop

skip_all_data:

MsgBox "Finished transferring data..."
STGIMPORT.Hide
End If
End Sub

vbFace
12-18-2003, 07:41 AM
Check MSDN, because I think FIND searches from the current position to the end of the RS. So, if you can determine that you need to look only at position 2850 and down, do a rs.MoveFirst, rs.Move 2850, and then rs.Find.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum