loop through files and then compare with the file sin database table

[hfs]

Hey all,
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.
For this i tried to write a code but i think i m unable to do that .Need help

Loop through files in folder
[COLOR="Red"]folderspec = "O:\QA Files\QC Reporting\Pending Review\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
For Each fl In fc
s = s & fl.Name
s = s & vbCrLf
Next
'Debug.Print s[/COLOR]
[COLOR="red"][COLOR="Navy"]
All files from tblExcel LOcation
SQL = "SELECT * from tblExcelLocation "


Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveFirst
Do While Not rs.EOF

x = x & rs!ExcelPathLocation
x = x & vbCrLf
rs.MoveNext
Loop
'Debug.Print x[/COLOR][/COLOR]

If SOMETHING Then

[COLOR="DarkGreen"]I WANT A CONDITION HERE TO CHECK IF THE FILE ALREADY EXISTS IT SHOULD NOT UPLOAD IT AND INSERT THE REST FILES.[/COLOR]

MsgBox "do not enter"
Else


  
  
 


   Set db = CurrentDb()
   DoCmd.SetWarnings (False)
   strSQl = "INSERT INTO tblExcelLocation(LotNumber,ExcelPathLocation,SearchByDate) VALUES ( " & " (' " & strLotFinalResult & "')" & ",(' " & Replace(strFullPath, "'", "''") & "'),(' " & Replace(InsertStrDb, "'", "''") & " '))"
   DoCmd.RunSQL (strSQl)
   DoCmd.SetWarnings (False)
   db.Close
  End If
 End If

THANKS

 

[Mihail]

You lost me.

Lets start from the very beginning.
So somewhere in your computer you have a folder that contain some files.
Lets name this folder TargetFolder.

In Access you have a table where you store the paths to every file from the TargetFolder.
After a while (1 week less or more) you run again your DB in order to add only the new files that are created in the TargetFolder from your last visit until now.

Is this correct ?
Is this what you are trying to do ?

 

[hfs]

Yes exactly,m glad you got my question

 

[Mihail]

Yep. Good for me.

Few more questions:
1) What happen if some files was removed from the TargetFolder (from your last visit until now). Should be also removed from your DB or not ?
2) I understand that you already managed to add all files to a table. Is this correct ?
3) Just you don't wish to create duplicates, triplicates and so on with any new visit. Is this correct ?

 

[hfs]

Well,that dosent matter if files get deleted from targetfolder,it should still be there in db ,and i managed to add all files in db and i just dont want to create duplicates!
Actually i am stucked in a problem,my supervisor wants it this way :

Every time a user click the button all file paths go to database table,and all the files get moved from the targetfolder,so that everytime new files come into targetfolder and get paths uploaded in database table,but i dont want to move the files because in the database there is a sub form which shows all the files path and on click open the file through the path.
And,if i try to move the files from target folder after uploading,the files won't open from subform because the path changed!

So i have no other solution,othar than creating a check and forget about moving the files.
did you get it?

 

[Mihail]

OK.
Lets name the table where you store the files paths tblTarget.
Open this table in design view.
Select the field named, probably, FilePath
Set the Indexed property to Yes (No Duplicates)

Create one more table with the exact same structure as tblTarget.
Lets name it tblTemp.

Manage to:
1) Run a Delete query against tblTemp in order to remove all records. Learn how.
2) Upload all files from TargetFolder in this temporary table (you say that you know how to do this)
3) optional: Turn warnings OFF (DoCmd.SetWarnings False)
4) Run an Append query in order to append the records from tblTemp to tblTarget. Learn how.
5) optional: Turn warnings ON (DoCmd.SetWarnings True)

That should be all.

 

[hfs]

WHAT?i just need to do this ! i was getting mad by writing alot of codes lol,it was so easy to do ....I am so

Thanks alot for your help
Appreciated!

 

[Mihail]

VBA is a nice and powerful tool. But, sometimes, is like you try to kill a fly with a gun.
Anyway the logic (in VBA) is like this:

Do
  [I]Extract a file from TargetFolder[/I]
  tf = FileFromTargetFolder
  [I]See if the file exist in tblTarget[/I]
  If DCount(tf in tblTarget) = 0 Then
    Add the file to tblTarget
  End If
  Move to the next file in TargetFolder
Loop