Thread: Attaching a file to a record
08-16-08, 22:58 #1Registered User
- Join Date
- Aug 2008
Unanswered: Attaching a file to a record
Hello one and all,
First off just let me state my level expertise with Access......that is to say I have none LOL. I am a complete newbie when it comes down to it. I am diving in at the deep end and working on 3 databases for work. I will admit that I do know the very very basics...I can create table and forms and have them work to add data to the tables but that is about it
As I said diving well and truly in at the deep end so I am hoping a few people here will be kind enough to help me out. And I will also apologise straight up if I get the terminology wrong.....from past experiences....not knowing the right terminology can be a huge hinderance in searching for help....but I will try my best.
Ok so here is my first question;
Is it possible to attach a file to a record through a button on a form? The file in question will most likely be a PDF, DOC or EXCEL file.
Ideally I would like the file actually "attached" to the the record and stored in the same location as the database, if this is not possible I would like the form to contain a button so that upon review of the record in question a user can click "View file" button and it will open the required file.
Any help tips or suggestions would be greatly appreciated.
Thanks a million in adavance!
PS: If any more info is required please just shout and I will provide what I can.
08-17-08, 09:17 #2Registered User
- Join Date
- Jun 2008
My take is that it's best NOT storing the files into your actual MDB file.
This way your MDB file will remain at a reasonable size, you won't have the overhead that comes with storing the files in the database, and besides, your files will remain accessible from outside your MDB which has many advantages, among which preventing your files from becoming corrupt if something affects the database.
So what you do is create a directory that will hold your database and create a subdir that holds the files.
Your "files" table should contain a "path" field that will hold the *relative* path to the file from the DB.
To have a button that opens your file, you will need a bit of coding.
Open the form where you need that button in Design Mode and add the button. Click "Cancel" when the wizard asks you what you want, then right-click the button you added and click on "Create event procedure". Access will ask you what too you want to use : select "Code generator" (the exact name might differ a little bit).
You will see a window where you can add code. On the left menubar, right-click your project's name, select "Insert" then "Module". Then paste the following code on a new line after "Option Compare Database" :
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Paste the following code on a new line below the start your button's code and before the end ("End Sub") :
Dim strBasePath As String Dim strRelPathToFile As String strBasePath = CurrentProject.Path strRelPathToFile = Me.the_name_of_your_path_field.Value If Len(strRelPathToFile) = 0 Then MsgBox "No path entered." Exit Sub End If ShellExecute 0&, vbNullString, Chr(34) & strbasePath & "\" & strRelPathToFile & Chr(34), vbNullString, vbNullString, vbNormalFocus
08-17-08, 10:14 #3L33t Helpa Munky
- Join Date
- Nov 2007
- Adelaide, South Australia
I sometimes use nothing but code to do this, if you have a short primary key. For example, you can name a file with, say, JOB12345.pdf and make a button in the form that attempts to open "JOB" & <JobNumber> & ".pdf". If the job number is 12345 then the file will be opened.
Then you don't even have to touch the table design... just add a button on a form.Owner and Manager of
CypherBYTE, Microsoft Access Development Specialists.
Microsoft Access MCP.
And all around nice guy!
"Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
"...teach a man to code and he'll be frustrated for life! " -- georgev
08-17-08, 16:24 #4Moderator
- Join Date
- Dec 2004
- Madison, WI
This example might help:
It let's you 'catalog' files of a specified type for the folder you specify (or you can search the entire c: or network drive), and stores it into a table (letting you also "group" what kind of file it is (ie. Word, Excel, Picture, etc..)), and then on the form which lists the files cataloged, you can click on a specific filename, click the "view" button, and it will open the file (regardless of what kind of file it is.) Plus it does a whole bunch of other things (ie. create a shortcut on the desktop to the file highlighted, sort the listing by date or > or < than a certain date, search by name or directory, printout the table which has the location and filenames stored, add comments to a filename, etc..etc...)
You can use the entire application as is or take out the coding parts that you need to do whatever you want to. I use it all the time and it's worth taking a look at.
Note: Open with the shift-key to view the code. Also, click the "Admin Populate" button on the Mainform to initially populate the table with the filenames.
Last edited by pkstormy; 08-17-08 at 16:48.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)