Find File from Access

[Guest]

I have a database that opens a text document listing of bankdraft and credit
card accounts posted for payment and then searches that list based on the
bank account number or credit card number specified by the user. I want to be
able to open up the electronic scanned copy of the contracts associated with
these account numbers for the ones we have stored on our network (some of the
older contracts aren't available electronically but most of what we're
working on in this database is recent data). For example, if a user is
searching for account number 53886 for the date 11/01/05, the user runs the
import function for the 11/01/05 file, then types in the account number and
runs the search function. A window pops up with the customer's information
for their account with us (e.g. 6000012345), and then the user searches
through a network location for the contract number from Access. I want Access
to handle finding the contracts. These scans are all in .tif format and they
are all located in a single directory with multiple subdirectories. I've
searched on The Access Web and tried a couple of things I found over there,
but the results were not what I need. Any ideas? Thanks!

 

[Guest]

Hello Nicholas,

you have a minimum of two problems, first is performance, because it is slow
to search a directory for specific documents, second is what happens when the
documents where moved?

I guess you should write a function that runs every day to locate new
documents on the given network shares. The documents found should be stored
in a separate share in a flat directory structure, e.g.
Z:\Documents\Statements\... perhaps you even can zip (ocx is available) these
documents and give them unique names and you refer in your database to these
unique names, this is only useful if the documents do not change
periodically. If they are changing from day to day you have to implement a
bit more logic, e.g. Checksums for Files like MD5 (ocx is available) and
compare the found files against these checksums to present the right
document. If you have problems to open the documents you should search for
ShellExecute which uses the right application to open the document. Do you
import these TIF-files in Access BLOBs to present them in the GUI?

For the first part you have to implement a recursive search on folders and
subfolders like this:

Sub prcGetFilesToDB(sFolder As String)
Dim oFSO As Scripting.FileSystemObject
Dim oFolder, objSubfolder As Scripting.Folder
Dim oFile As Scripting.File

If Right(sFolder, 1) = "\" Then sFolder = Left(sFolder, Len(sFolder) - 1)
If bCancel Then Exit Sub
' Get Fileinformation
Set oFSO = New Scripting.FileSystemObject
Set oFolder = oFSO.GetFolder(sFolder)

' Archive found files
For Each oFile In oFolder.Files
DoEvents
If bCancel Then
txtAction = "Import cancelled
Exit Sub
End If
' Now, do something with the found file ...
Next oFile

' Get subfolders recursive
For Each oSubfolder In oFolder.SubFolders
prcGetFilesToDB oSubfolder.Path
Next oSubfolder

Set oFolder = Nothing
Set oFSO = Nothing
End Sub

 

[Guest]

Well, first off, as far as the second problem, these files are never moved
nor modified once they are saved on the server. So there shouldn't be a
problem there. As for the first problem, I thought about that too, lol. My
idea was to create an index file within the database that would list all the
filenames contained in each directory, have the database search the listing
to see if the contract number is found within that listing (the .TIF's are
named by the contract number, e.g. 6000012345.tif), then pass that pathname
to the program that opens the .TIF's. I somewhat understand your code, but my
vB skills are, to put it bluntly, severely lacking. I know it's a Sub, but
I'm lost as to what to code around it to make it work. (When I was working at
my last job, the IT people taught me some basic Access query logic, but
everything else I've had to learn via trial by fire. vB was never an issue
until recently and I've never had much luck with it.) Again, any help you can
give me is greatly appreciated. Thanks!


And, since you mentioned this and I have no idea what it is, what's a BLOB?

 

[Guest]

Hi,

it will be difficult to solve this problem if you can't script in vba. Last
chance I see is following:

1. Open Dos Prompt
2. Syntax for the tree command
TREE [drive:][path] [/F] [/A]

/F Display the names of the files in each folder.
/A Use ASCII instead of extended characters.
enter TREE Z:\MyFolder /F /A >c:\list.txt, where Z:\MyFolder is your
document share and folder, this will search all folders and subfolders and
writes a list with the files found to c:\list.txt.
3. Now you have to get this data by any chance into an msaccess table and
you'll have the directory structure and the files or first modify the data in
excel and then import it to access
4. Now you can lookup for the file in a table and you have the path to the
file
Bad news, you have to do it every day.

Alternative you can run the mentioned recursive function and get a little
bit deeper into access scripting, like me ten years ago.

I think there is no suitable non-scripting workaround for you available.

 

[Guest]

Isn't this the same thing as a dir/w/s? (That was my solution I came up with,
a batch file that runs a dir/w/s of the directory and stores it in a file on
the C drive.)

 

[Guest]

Hi Nicholas,

I guess we have a dead lock situation, let's try to begin from scratch.

Make a table in Access with the following columns

ROW_ID Autonumber
strFILE String with length 255 (stores the path and the file name)

Make a new module called MOD_BATCH

Copy following statements to the module

Function fctGetFilesToDB(sFolder as String)
Dim oFSO As Scripting.FileSystemObject
Dim oFolder, objSubfolder As Scripting.Folder
Dim oFile As Scripting.File

If Right(sFolder, 1) = "\" Then sFolder = Left(sFolder, Len(sFolder) - 1)
' Get Fileinformation
Set oFSO = New Scripting.FileSystemObject
Set oFolder = oFSO.GetFolder(sFolder)

' Archive found files
For Each oFile In oFolder.Files
prcArchiveFile(cStr(oFile.Path))
Next oFile

' Get subfolders recursive
For Each oSubfolder In oFolder.SubFolders
prcGetFilesToDB oSubfolder.Path
Next oSubfolder

Set oFolder = Nothing
Set oFSO = Nothing
End Function

Sub prcArchiveFile(sFile as String)
Dim db as DAO.Database
Dim r,s as DAO.Recordset
Dim sSQL as String

If sFile & "" <> "" Then
strSQL = "SELECT * FROM [MyFileTable] WHERE strFILE = '" & sFile & "'"
Set db = CurrentDb
' Look up if file exists
Set r= db.openrecordset(strSQL,dbopendynaset)
' If file doesn't exists add record with path and file name
if r.eof then
set s=db.openrecordset("MyFileTable", dbopendynaset)
s.addnew
s!strFILE = sFile
s.update
s.close
end if
r.close
db.close
end if
end sub

The Sub "fctGetFilesToDB" should run every day in the morning, you can start
it via a button event from a form or via a macro. Depending on the amount of
files it can last hours to complete. But you have an actual list in access of
your files.

Hope that will help, otherwise you need external help.

 

[Guest]

I'm going to try running a batch file that stores the output of dir /s /b (as
opposed to dir/w/s, which does me no good as the output is too hard to break
down to a single column in a table... and since I don't seem to have the
"TREE" command, it's about all I have left) to a file, but I'm still not sure
if it will work the way I'm hoping. Hopefully I can do something with it, but
if not at least it was worth a shot. I haven't had a chance to try this the
way you have suggested here yet, but I'll do that too. Thanks for the help, I
know it's hard trying to explain this to someone who doesn't understand it.

Hi Nicholas,

I guess we have a dead lock situation, let's try to begin from scratch.

Make a table in Access with the following columns

ROW_ID Autonumber
strFILE String with length 255 (stores the path and the file name)

Make a new module called MOD_BATCH

Copy following statements to the module

Function fctGetFilesToDB(sFolder as String)
Dim oFSO As Scripting.FileSystemObject
Dim oFolder, objSubfolder As Scripting.Folder
Dim oFile As Scripting.File

If Right(sFolder, 1) = "\" Then sFolder = Left(sFolder, Len(sFolder) - 1)
' Get Fileinformation
Set oFSO = New Scripting.FileSystemObject
Set oFolder = oFSO.GetFolder(sFolder)

' Archive found files
For Each oFile In oFolder.Files
prcArchiveFile(cStr(oFile.Path))
Next oFile

' Get subfolders recursive
For Each oSubfolder In oFolder.SubFolders
prcGetFilesToDB oSubfolder.Path
Next oSubfolder

Set oFolder = Nothing
Set oFSO = Nothing
End Function

Sub prcArchiveFile(sFile as String)
Dim db as DAO.Database
Dim r,s as DAO.Recordset
Dim sSQL as String

If sFile & "" <> "" Then
strSQL = "SELECT * FROM [MyFileTable] WHERE strFILE = '" & sFile & "'"
Set db = CurrentDb
' Look up if file exists
Set r= db.openrecordset(strSQL,dbopendynaset)
' If file doesn't exists add record with path and file name
if r.eof then
set s=db.openrecordset("MyFileTable", dbopendynaset)
s.addnew
s!strFILE = sFile
s.update
s.close
end if
r.close
db.close
end if
end sub

The Sub "fctGetFilesToDB" should run every day in the morning, you can start
it via a button event from a form or via a macro. Depending on the amount of
files it can last hours to complete. But you have an actual list in access of
your files.

Hope that will help, otherwise you need external help.
--
Regards

Oliver Seiffert

I have a database that opens a text document listing of bankdraft and credit
card accounts posted for payment and then searches that list based on the
bank account number or credit card number specified by the user. I want to be
able to open up the electronic scanned copy of the contracts associated with
these account numbers for the ones we have stored on our network (some of the
older contracts aren't available electronically but most of what we're
working on in this database is recent data). For example, if a user is
searching for account number 53886 for the date 11/01/05, the user runs the
import function for the 11/01/05 file, then types in the account number and
runs the search function. A window pops up with the customer's information
for their account with us (e.g. 6000012345), and then the user searches
through a network location for the contract number from Access. I want Access
to handle finding the contracts. These scans are all in .tif format and they
are all located in a single directory with multiple subdirectories. I've
searched on The Access Web and tried a couple of things I found over there,
but the results were not what I need. Any ideas? Thanks!