Sub FindDBs()
Dim a_dir, temp, i&, j&, oFSO As Object, oFolder As Object, oFile As Object, DBs$, rc&
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("K:\")
For Each oFile In oFolder.Files
If Left(oFile.Name, 5) = "KKDB-" Then
DBs = DBs & Format(oFile.DateLastModified, "YY/MM/DD HH:MM:SS") & " " & oFile.Name & ","
End If
Next
DBs = Left(DBs, (Len(DBs) - 1))
a_dir = Split(DBs, ",")
For i = LBound(a_dir) To UBound(a_dir) - 1
For j = i + 1 To UBound(a_dir)
If a_dir(i) < a_dir(j) Then
temp = a_dir(j)
a_dir(j) = a_dir(i)
a_dir(i) = temp
End If
Next
Next
DoCmd.SetWarnings False
rc = DCount("*", "ActJobs")
For i = LBound(a_dir) To UBound(a_dir)
temp = Split(a_dir(i), " ")
If temp(2) <> "KKDB.accdb" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\KK\" & temp(2), acTable, "ActJobs", "tblTemp", False
DoCmd.OpenQuery "tblTemp Query"
DoCmd.DeleteObject acTable, "tblTemp"
End If
Next
DoCmd.SetWarnings True
MsgBox DCount("*", "ActJobs") - rc & " Records were reclaimed.", , "Done retrieving..."
End Sub
And the query
INSERT INTO ActJobs
SELECT *
FROM tblTempCopy;
With the field JobNo Indexed - Yes (No Duplicates).
Works like a charm
Thank you for your help OBP, if you are ever in Llangollen give me a shout, I'll get you a pint!