I have made a database which is used to record information where users are starting to ask to be able to attach "evidence" to records.
This evidence could be a pdf document, word document, image etc
Now I have researched a little and found most sites tell you to use an OLEObject to do this. However the file size is really hit hard by doing this. So here is my solution...
Users click attach on a form and use the windows open file dialog to find the path. They then click open.However instead of opening the path two things occur:
The file is copied to a central folder on the server (where my back end is kept). This then updates a table which contains a list of all files within the folder. This list of files just contains a unique reference number, the file extension and also the path to the file.
My form then shows the attachment as a hyperlink within a subform (can have mutliple records attached). The subform is ofcourse linked to the main record via the parent ID number. This for instance could be displayed within a datasheet view with the hyperlinks beside the unique ID and then the file type in a 3rd column.Users can click the hyperlink to open the file which is the path to the copy within the back end file storage folder.
Any ideas if anything like this has been done before or whether this sounds feasable? The coding behind it should seem relatively OK using vba I expect.
Anyway, thoughts please.
6 3306
Your approach is definitely feasible as well as practical. Personally, I avoid the use of Hyperlinks. I'll usually display Descriptive Text in the Field such as: 3rd Quarter Sales for 2010. This Text will be formatted as Underline, Blue, and possibly Italic to set it off as well as to mimic the look of a Hyperlink. In the Dbl-Click() Event of the Control, I'll perform a DLookup() to retrieve the Absolute Path to the File, then execute it directly based solely on its Extension. Again, this is the simply the System that I like to use, and I am not saying that it is a better/worse than any other.
Thanks for the reply Adezii, I have begun creating the code now. Do you have any reason why you avoid the usage of hyperlinks?
Also have you seen similar set ups being used in applications for storing attachments as I described? Just wanting to make sure I am not covering anything which could be easily bypassed with a quicker method.
There have been similar Threads before covering this very Topic, usually under 'Storing of Images in a Database' or related Topic. You can research these Threads to cover all bases. P.S. - I do believe that I can save you a lot of time and effort. Here is an Application that I developed for a User awhile ago. It will Open the Office File Dialog, allow the User to select any File (*.*), then write that File to a Text Box whose Control Source is a Hyperlink Field, while displaying the Caption of the Hyperlink only. Be sure that the Reference to the Microsoft Office XX.X Object Library is made. Download the Attachment and let me know what you think.
Thanks for that ADezii, it has helped me with understanding a lot of the properties associated with the file dialog that I didnt know about.
I will be using the primary key of the parent to make a new folder if it does not exist and then transfer the file over.
The basic set up so far: - Sub copyfile()
-
Dim SourceFile, DestinationFile As String
-
Dim StrIDNum As Integer
-
Dim fso As Object
-
Dim Strfol As String
-
-
'Create generic error handler
-
On Error GoTo Ouch
-
-
'Open up a dialog for us to select a file to attach
-
'Find the file name of the file we selected 4th in our delimited array
-
SourceFile = Split(OpenDialog, "\", , vbTextCompare)(4)
-
-
'Select the ID number to use to archive our attachments into
-
StrIDNum = 400 'Replace this with a reference to ID number of parent record I.E. me.parent.nccid
-
-
'Create the destination folder path
-
Strfol = "C:\Documents and Settings\mobbe00C\Desktop\nonconformancereports\" & StrIDNum & "\" ' change to match the folder path"
-
-
'Check if the destination folder exists, if not create it
-
Set fso = CreateObject("Scripting.FileSystemObject")
-
If Not fso.FolderExists(Strfol) Then
-
fso.CreateFolder (Strfol)
-
Else
-
End If
-
-
'Create the destination file string, which will be equal to our folder and sourcefile name
-
DestinationFile = "C:\Documents and Settings\mobbe00C\Desktop\nonconformancereports\" & StrIDNum & "\" & SourceFile ' Define target file name.
-
-
'Copy the file from the source to the destination folder
-
FileCopy SourceFile, DestinationFile
-
-
LeaveIt:
-
Exit Sub
-
-
Ouch:
-
MsgBox Err.Number & "-" & Err.Description
-
Resume LeaveIt
-
End Sub
-
-
Function OpenDialog()
-
Dim fd As FileDialog
-
Set fd = Application.FileDialog(msoFileDialogFilePicker)
-
Dim vrtSelectedItem As Variant
-
-
With fd
-
.AllowMultiSelect = False
-
.ButtonName = "Attach file"
-
.InitialFileName = vbNullString
-
.InitialView = msoFileDialogViewDetails
-
.Title = "Select a file to attach to the database."
-
-
If .Show = -1 Then
-
For Each vrtSelectedItem In .SelectedItems
-
OpenDialog = vrtSelectedItem
-
Next vrtSelectedItem
-
Else
-
End If
-
End With
-
-
Set fd = Nothing
-
-
End Function
-
-
I just need to work on creating a table and appending a new record each time an item is added. I think I will also sort out a way of deleting the file if the attachment record is removed.
Looks good munkee, If you wish to keep things simple, and remove the External Reference to the Scripting Runtime, you can. For me, I always try to use what is intrinsic, as long as it performs the task adequately. - If Dir$(Strfol, vbDirectory) = "" Then
-
MkDir Strfol
-
End if
Learn something new everyday, I didnt even know there was a Dir$ function! I'll be using this more often. Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Python Baby |
last post by:
I'm about to try a little test project in Python, but want to make sure
I'm on the right foot.
Any advice appreciated before I start this. MOST important : I want
to make sure I'm not just...
|
by: AspDotNetDeveloper |
last post by:
I'm trying to troubleshoot why my ASP application can no longer send file
attachments larger than 100K, and I have eliminated many possibilities
already, but I'm running out of ideas.
I think I...
|
by: moon |
last post by:
Wondering if this is possible?
Situation:
I have a frameset with a few frames in it that loads side graphics,
main navigation, subnavigation and content. Everything works fine.
Now, I decided...
|
by: muskan |
last post by:
Does any body has been done Advamce C++ from ACC Austin?
|
by: Peter Row |
last post by:
Hi,
I am writing a DLL in VB.NET that implements IHttpHandler.ProcessRequest.
This code calls a sub and I need to know if that sub did a response redirect
or not.
Specifically I need to know...
|
by: Flens |
last post by:
Hi,
I have code that worked until we upgraded from Exchange 2000 to Exchange
2003 and I now get a catastrophic failure at CreateMailbox (COMException
(0x080004005))
I have updated the...
|
by: Kosmos |
last post by:
Howdy...I'm still a newbie although I've been working with Access for a couple of months now, it's only been on Fridays (but I've been trying to give back by helping out when I can). Anyways this may...
|
by: ITrishGuru |
last post by:
Hi all,
For my IT final year project I have to access mail items in outlook
2003 and parse them.
I have read and researched on the net and library for about a week
now.
I have to do the project...
|
by: Niheel |
last post by:
http://bytes.com/images/howtos/freelance_millions.jpgAccording to India Times, there are groups of freelancers in India making over a millions dollars by providing development and IT services to...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |