need better VBA logic than I currently have

[jscriptor09]

Hi folks

I am looking for better way to do the following. Teh code below is how I do it now and it may be very inefficient.

1. I dump a list of image files (jpe) that I have on my server to a file.
2. I have a list of recent images files

Below, I create a loop to look ate each image in (1) and if not found in (2) then the image name is written to a table.

Item (1) list file can have up to 100,000

Therefore, the loop below can be time consuming (actually, it is).

So, the objective is compare each jpg file in item (1) list to item (2) list and if not found, write the item to a 3rd table.

I would appreciate abetter solution than the one I show below.

Thanks
Mike





Public Function findOldImages()
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim DB As Database
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim icounter As Integer
Dim i, j As Integer
Set DB = CurrentDb


'
strSQL1 = "CREATE Table ImagesToDelete(theImage String, theLocation String)"
DoCmd.RunSQL strSQL1
'
'DirImagesFileList
strSQL2 = "SELECT * FROM DirImagesFileList"
Set rs2 = DB.OpenRecordset(strSQL2)
rs2.MoveFirst
DoCmd.SetWarnings False
Do While Not rs2.EOF
theImage = Mid(rs2!Field2, 1, 7)
If (Len(Trim(theImage )) > 5) Then
Set rs3 = DB.OpenRecordset("SELECT anImage FROM imagetable WHERE someImage =" & theIamge)
If rs3.RecordCount <= 0 Then
strSQL1 = "INSERT INTO ImagesToDelete Values('" & rs2!Field2 & "','" & rs2!Field1 & "')"
DoCmd.RunSQL strSQL1
End If
End If
rs2.MoveNext
Loop
DoCmd.SetWarnings True
End Function

[ajetrumpet]

So, the objective is compare each jpg file in item (1) list to item (2) list and if not found, write the item to a 3rd table.

What you're basically doing here is using recordset loops to do a task that something a single query can do by itself. So yeah, it's incredibly inefficient.

I didn't read all your code, but obviously you're extracting portions of the image names, so you'll need to use the same string extraction functions in the query statements too.

Basically the way you do this properly, is use an outer join in an append query to target the image names that are in table 1 but not table 2. Join on the equal field value and execute the query. That's the whole task, literally.

so if you want image names from table1 that's not in table2 and you want to append those names to table3, you write this:

INSERT INTO table3 (imagename)
SELECT table1.imagename
FROM table1 LEFT JOIN table2 on table1.imagename = table2.imagename 
WHERE table2.imagename IS NULL

and the code you would write would probably be this:

Public Function findOldImages()
  currentdb.execute(sql statement from above)
End Function

So yeah, a one-line function is probably what you're looking for!

[jscriptor09]

Hi Adam
Thaks for showing the Join approach. I tried it and it seems to take a long time as well, maybe as long as the looping. I have 8000 records in Table 1, comparing to ~100,000 records in table 2, if a record is not found put in Table 3.

Thanks

[orange]

Can you show the table structures for
DirImagesFileList and imagetable?
Do you have any indexes on these tables?

[ajetrumpet]

Can you show the table structures for
DirImagesFileList and imagetable?
Do you have any indexes on these tables?

indexes do help with speed, but not much.

Queries are always better to use than recordsets for 2 reasons:

1) less coding.
2) less risk of human error.

good luck to ya.