table files linked to directory

[satyen]

Hello.

Was wondering if someone can help.
I need to check (one by one) if filenames in a table exist in a certain folder then produce a report if they do or don't.

Can someone help get me started?

Thanks

[orange]

Hello.

Was wondering if someone can help.
I need to check (one by one) if filenames in a table exist in a certain folder then produce a report if they do or don't.

Can someone help get me started?

Thanks
Just 1 folder or several?
How familiar are you with vba?
What sort of approach are you considering?

[satyen]

I've just re-thought and its not as simple as it first sounded.
All files are in 1 table but we have to check two directories.
Example
1)FileA and FileC and FileE for example - do the Exist in Folder1?
2) FileB and FileD and FileF for example do they Exist in Folder2?

1 thing that make it little simple is that for file in 2) these will all start with TB and files in 1) will start with their company codes. None will be starting with TB if in 1)

A report needs to be produced showing which 1) and 2) files are present.
Then afterwards another table (Files Import)- automatically should update showin files that exist with a tick and those that are not with a x

I am a little familiar with VBA Access but more so with VBA for Excel.

If someone can firstly help me with checking if files exist or can think of a simpler way of takling this than this woul be very much appreciated.

[orange]

I've just re-thought and its not as simple as it first sounded.
All files are in 1 table but we have to check two directories.
Example
1)FileA and FileC and FileE for example - do the Exist in Folder1?
2) FileB and FileD and FileF for example do they Exist in Folder2?

1 thing that make it little simple is that for file in 2) these will all start with TB and files in 1) will start with their company codes. None will be starting with TB if in 1)

A report needs to be produced showing which 1) and 2) files are present.
Then afterwards another table (Files Import)- automatically should update showin files that exist with a tick and those that are not with a x

I am a little familiar with VBA Access but more so with VBA for Excel.

If someone can firstly help me with checking if files exist or can think of a simpler way of takling this than this woul be very much appreciated.
Do the files in the folder have similar extensions -- such as .txt
How often do you have to do this procedure?

Here's a possible scenario:

You can get a list of files in a specified directory, such as
with a cmd prompt

'Dir commands to get full directory and database name

'F:\>dir *.mdb /s/b >c:\MDBLog_FDrive.txt

This command will select all file names (.mdb in this example) in all folders on F:\drive
and create a file called MDBLog_FDrive.txt on C:\
This file will have the fullpath and file name of each file.

You can then link to this .txt file from Access.
Then your checking if files exists becomes a procedure involving 2 tables
in Access.

You could use this command syntax to get all files in the 2 folders involved

EG: drive:\Folder1>dir *.* /s/b >C:\MyLists\FilesinFolder1.txt
drive:\Folder2>dir *.* /s/b >C:\MyLists\FilesinFolder2.txt

[CreganTur]

MDMakillop(sp?) has some code in the kb that shows how you can enumerate through files and folders. Take a look at it- you should be able to adapt it to do what you want. Use the kb's search function- it's easy to find.

HTH:thumb

[satyen]

Hi guys i've tried to write something. Please can someone help me get this working. Many thanks.

Sub Test ()

Dim rec As Dao.recordset
Set dbs = CurrentDb

Dim dir folder, files, fso, file
Dir = “C:/Documents and Settings/desktop/”
Set fso = Createobject(“Scritpting.FileSystemObject”)
Set folder = fso.GetFolder(dir)
Set files =folder.files

Str_Get_file_name =rec.Fields(“File”)
Do until rec.eof
If files exsits(str_get_file_name) then
rec.fields(“Exists”) = 1

Loop

End Sub





what it is trying to achieve. Go through one folder and if what it says in the table exists mark in the Exists column the Access table as yes it exists. If it doesn't then mark in the table as no.

[satyen]

Orange- all files should be .csv and this is run everyday.

[orange]

Hi guys i've tried to write something. Please can someone help me get this working. Many thanks.

Sub Test ()

Dim rec As Dao.recordset
Set dbs = CurrentDb

Dim dir folder, files, fso, file
Dir = “C:/Documents and Settings/desktop/”
Set fso = Createobject(“Scritpting.FileSystemObject”)
Set folder = fso.GetFolder(dir)
Set files =folder.files

Str_Get_file_name =rec.Fields(“File”)
Do until rec.eof
If files exsits(str_get_file_name) then
rec.fields(“Exists”) = 1

Loop

End Sub




what it is trying to achieve. Go through one folder and if what it says in the table exists mark in the Exists column the Access table as yes it exists. If it doesn't then mark in the table as no.

Here's a link to some relevant code.
http://allenbrowne.com/func-11.html