Linking to a file in VBA

[ashbyhope]

Guys,

I've got a database that I'm trying to add a little extra to it.

The system creates paperwork which is sent to suppliers and the supplier then returns their own paperwork. What I want to be able to do is scan the suppliers paperwork in to a folder and then give the user the option to link the record to that file.

I'm planning a field which the filename/path will be stored in and when they click on it the scanned paperwork will be displayed.

The bit that I'm stuck on is getting the "Link" between them set up. I don't want to try and get the scanning software and Access to work together as that will be long and complicated. What I want is for them to manually scan and rename the file (Not compulsory to rename) and then open a form in the database and navigate to the file so that it stores the filename/path in a field in the database. Then if they go to another form and wish to view the scanned paperwork they can click on a button and up it pops.

Hope that makes some sense and someone can help. I really want to keep it very simple but if that's not possible then throw complex stuff at me!

Cheers,
Ash.

 

[MasterRacker]

Look at the Application.FileDialog object. This thread should give you a start: thread705-755820


Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MikeC14081972]

Also worth thinking about using the UNC Filepath if they are stored on a network. This will resolve any issues later when users have different drive mappings.

 

[Gruuuu]

Another thing to consider is URL encoding. You'll be setting up a hyperlink to the file (example: "file:///domain/path/This & That is a file.pdf")

You can google search for the list, but there's a list of characters that may need to be encoded. When I've done this in the past, I've just encoded everything. The result to the example would be "file:\\\domain\path\This%20%24%20That%20is%20a%20file.pdf" (& is a reserved character, space is an unsafe character)

 

[MajP]

FYI, Access 2007 introduced an Attachment data type. This makes storing documents in the database much more viable. It has drastically reduced the bloat of the OLE data type, and stores the attachment in its native format.

http://www.databasedev.co.uk/access2007attachment.html

This is not a perfect solution, but works pretty well for many applications.