chuckcoleman
Registered User.
- Local time
- Yesterday, 22:01
- Joined
- Aug 20, 2010
- Messages
- 363
I built an Access 2010 database about two years ago for a client. My client wanted to scan customer contracts and save them with the database. I knew there were two methods; save the scanned documents in an Attachment file or save just the path to the file as a Hyperlink. At the time, (maybe I didn’t do enough research), I decided to use the Attachment file method. I probably also underestimated the quantity of scanned documents the customer would have and the impact on the database file size. From the start, the database was split into a Front End and a Back End. At inception, the Back End was about 3MB in size. Today, it is over 660MB in size and growing with every scanned document.
I started doing research online to figure out how I could export all of the scanned documents from the Attachment field, save them to a folder, and then add their location to a new table and field so the customer could still access them. It’s important to note that each scanned contract is associated with a customer order and I would need to ensure that whenever my customer went to a customer’s order, they would see only those scanned contracts. I never found in one place a “How To Export Attachments” article so I decided to summarize here what I did in the hope that it helps others that need to reverse stored documents/pictures/etc. from an Attachment field and save them in a folder. Maintaining the relationship to the location in the database are now being saved was critical.
Before you begin, make sure you back up your Back End data as well as your Front End. Also, please hold the feedback on how I named my fields and/or tables. I’m comfortable with it and it works for me. Yes, I know sometimes I need to use brackets on my field/table names.
One sample code that I used was from the Microsoft Access 2010 Programmer’s Reference manual. I modified the code slightly and it is shown below:
Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsA As DAO.Recordset2
Dim rsB As String
Dim fld As DAO.Field2
Dim OrdID As DAO.Field2
Dim strFullPath As String
'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Order Table")
Set fld = rst("Scan")
Set OrdID = rst("OrderID")
'Navigate through the table
Do While Not rst.EOF
'Get the recordset for the Attachments field
Set rsA = fld.Value
rsB = OrdID.Value
'Save all attachments in the field
Do While Not rsA.EOF
If rsA("FileName") Like strPattern Then
'To Export the data, use the line below
strFullPath = strPath & "\" & rsA("FileName")
'Make sure the file does not exist and save
If Dir(strFullPath) = "" Then
rsA("FileData").SaveToFile strFullPath
End If
'Increment the number of files saved
SaveAttachmentsTest = SaveAttachmentsTest + 1
End If
'Next attachment
rsA.MoveNext
Loop
rsA.Close
'Next record
rst.MoveNext
Loop
rst.Close
dbs.Close
Set fld = Nothing
Set rsA = Nothing
Set rst = Nothing
Set dbs = Nothing
End Function
Some key points.
The table that contains the attachments field is [Order Table]. The field for each order is [OrderNbr]. The Attachment field’s name is [Scan]. My Attachment field when viewed in the Query By Entry, (QBE), shows:
· Scan.FileData
· Scan.FileName
· Scan.FileType
I created a temporary Form with two Command Buttons on it:
· Export Attachments
o This had a simple macro on the On Click event that was:
§ RunCode and the Function Name was: SaveAttachmentsTest(“R:\Attachments”) where “R:\Attachments” was the location of the folder where I was going to save the scanned contracts to. When the Function was executed, it exported each scanned document in the order they were input into the database to that folder. (Although I haven’t shown this, I modified the above code slightly to modify as the file name and show the [OrderNbr] just to be able to verify the order and the associations between the scanned contract file name and the [OrderID] were in the right order.)
· Run Append Query
o I created a new table that I called Attachments Table. It has three fields in it:
§ [AttachNbr] which is an AutoNumber field
§ [OrderID] which is a Number field that will correspond/establish the relationship back to the [OrderID] field in the [Order Table]
§ [FileN] which is a Hyperlink field to store the location where the file is saved
o The Append query used the following SQL:
INSERT INTO [Attachments Table] ( OrderID, FileN )
SELECT [Order Table].OrderID, [Scan].[FileName] & "#" & "R:\Attachments\" & [Scan].[FileName] & "#" AS FileN
FROM [Order Table] WHERE ((([Order Table].Scan.FileName) Is Not Null));
This Query appends to the [Attachments Table] the [OrderID] from the [Order Table], (to the [OrderID] field), and a string, “R:\Attachments\”, (see NOTE below), which is the folder location where I just exported the scanned contracts to, and concatenated to it is the file name from the [Scan].[FileName] part of the Attachments/[Scan] field. NOTE: In order to get the Hyperlink data to be correctly stored in the field, you must use the “#” symbols as show in the SQL string. A Hyperlink field contains three parts separated by pound signs “#”. The template is: Display Text # file name including the path # Any reference within the file, (i.e. a sheet name if you’re importing an Excel spreadsheet). I found a helpful reference to this at: http://allenbrowne.com/casu-09.html
I also found that in my VBA editor that I needed to go to Tools, References and check Microsoft Office 14.0 Object Library.
I then modified my Order Form to remove the Attachment field input/delete button and then added a new Command button to open up my new Attachment Form, (Default View: Continuous Forms). This form has a couple of events:
· On Open it goes to a new record
· On Before Insert it triggers a macro to SetValue of the [OrderID] field to equal the [OrderID] field of the Order Form where this form was opened from. This links the hyperlink location and file with the OrderID.
· The form is based on a Query that uses as its only source the Attachments Table. The three fields from the Attachment Table are used in the query and the [OrderID] field has criteria that equals Forms![Customer Order Form]![OrderID] to make sure that only records for the customer in the form are shown. The form also has two Command buttons:
o Attach: It uses this code:
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library
Dim strButtonCaption As String, strDialogTitle As String
Dim strHyperlinkFile As String, strSelectedFile As String
'Define your own Captions if necessary
strButtonCaption = "Save Hyperlink"
strDialogTitle = "Select File to Create Hyperlink to"
With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add "All Files", "*.*" 'Allow ALL File types
'Test line so I can debug/compile the code
End With
'The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False 'Critical Line
.FilterIndex = 1 'Database files
.ButtonName = strButtonCaption
.InitialFileName = vbNullString
.InitialView = msoFileDialogViewDetails 'Detailed View
.Title = strDialogTitle
If .Show Then
For Each varItem In .SelectedItems 'There will only be 1
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
strSelectedFile = varItem
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
Me![FileLocX] = strHyperlinkFile
Next varItem
End If
End With
End Sub
o Delete: Simple Delete macro
Note: the above VBA code I found on a few different sites
I tested the functionality and once I was satisfied everything works, I deleted my [Scan] field from the [Order Table], compacted the database and went from 665MB to 4.6MB.
I hope this is helpful to all who need to reverse an internally stored attachment to saving it externally.
Chuck
I started doing research online to figure out how I could export all of the scanned documents from the Attachment field, save them to a folder, and then add their location to a new table and field so the customer could still access them. It’s important to note that each scanned contract is associated with a customer order and I would need to ensure that whenever my customer went to a customer’s order, they would see only those scanned contracts. I never found in one place a “How To Export Attachments” article so I decided to summarize here what I did in the hope that it helps others that need to reverse stored documents/pictures/etc. from an Attachment field and save them in a folder. Maintaining the relationship to the location in the database are now being saved was critical.
Before you begin, make sure you back up your Back End data as well as your Front End. Also, please hold the feedback on how I named my fields and/or tables. I’m comfortable with it and it works for me. Yes, I know sometimes I need to use brackets on my field/table names.
One sample code that I used was from the Microsoft Access 2010 Programmer’s Reference manual. I modified the code slightly and it is shown below:
Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsA As DAO.Recordset2
Dim rsB As String
Dim fld As DAO.Field2
Dim OrdID As DAO.Field2
Dim strFullPath As String
'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Order Table")
Set fld = rst("Scan")
Set OrdID = rst("OrderID")
'Navigate through the table
Do While Not rst.EOF
'Get the recordset for the Attachments field
Set rsA = fld.Value
rsB = OrdID.Value
'Save all attachments in the field
Do While Not rsA.EOF
If rsA("FileName") Like strPattern Then
'To Export the data, use the line below
strFullPath = strPath & "\" & rsA("FileName")
'Make sure the file does not exist and save
If Dir(strFullPath) = "" Then
rsA("FileData").SaveToFile strFullPath
End If
'Increment the number of files saved
SaveAttachmentsTest = SaveAttachmentsTest + 1
End If
'Next attachment
rsA.MoveNext
Loop
rsA.Close
'Next record
rst.MoveNext
Loop
rst.Close
dbs.Close
Set fld = Nothing
Set rsA = Nothing
Set rst = Nothing
Set dbs = Nothing
End Function
Some key points.
The table that contains the attachments field is [Order Table]. The field for each order is [OrderNbr]. The Attachment field’s name is [Scan]. My Attachment field when viewed in the Query By Entry, (QBE), shows:
· Scan.FileData
· Scan.FileName
· Scan.FileType
I created a temporary Form with two Command Buttons on it:
· Export Attachments
o This had a simple macro on the On Click event that was:
§ RunCode and the Function Name was: SaveAttachmentsTest(“R:\Attachments”) where “R:\Attachments” was the location of the folder where I was going to save the scanned contracts to. When the Function was executed, it exported each scanned document in the order they were input into the database to that folder. (Although I haven’t shown this, I modified the above code slightly to modify as the file name and show the [OrderNbr] just to be able to verify the order and the associations between the scanned contract file name and the [OrderID] were in the right order.)
· Run Append Query
o I created a new table that I called Attachments Table. It has three fields in it:
§ [AttachNbr] which is an AutoNumber field
§ [OrderID] which is a Number field that will correspond/establish the relationship back to the [OrderID] field in the [Order Table]
§ [FileN] which is a Hyperlink field to store the location where the file is saved
o The Append query used the following SQL:
INSERT INTO [Attachments Table] ( OrderID, FileN )
SELECT [Order Table].OrderID, [Scan].[FileName] & "#" & "R:\Attachments\" & [Scan].[FileName] & "#" AS FileN
FROM [Order Table] WHERE ((([Order Table].Scan.FileName) Is Not Null));
This Query appends to the [Attachments Table] the [OrderID] from the [Order Table], (to the [OrderID] field), and a string, “R:\Attachments\”, (see NOTE below), which is the folder location where I just exported the scanned contracts to, and concatenated to it is the file name from the [Scan].[FileName] part of the Attachments/[Scan] field. NOTE: In order to get the Hyperlink data to be correctly stored in the field, you must use the “#” symbols as show in the SQL string. A Hyperlink field contains three parts separated by pound signs “#”. The template is: Display Text # file name including the path # Any reference within the file, (i.e. a sheet name if you’re importing an Excel spreadsheet). I found a helpful reference to this at: http://allenbrowne.com/casu-09.html
I also found that in my VBA editor that I needed to go to Tools, References and check Microsoft Office 14.0 Object Library.
I then modified my Order Form to remove the Attachment field input/delete button and then added a new Command button to open up my new Attachment Form, (Default View: Continuous Forms). This form has a couple of events:
· On Open it goes to a new record
· On Before Insert it triggers a macro to SetValue of the [OrderID] field to equal the [OrderID] field of the Order Form where this form was opened from. This links the hyperlink location and file with the OrderID.
· The form is based on a Query that uses as its only source the Attachments Table. The three fields from the Attachment Table are used in the query and the [OrderID] field has criteria that equals Forms![Customer Order Form]![OrderID] to make sure that only records for the customer in the form are shown. The form also has two Command buttons:
o Attach: It uses this code:
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library
Dim strButtonCaption As String, strDialogTitle As String
Dim strHyperlinkFile As String, strSelectedFile As String
'Define your own Captions if necessary
strButtonCaption = "Save Hyperlink"
strDialogTitle = "Select File to Create Hyperlink to"
With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add "All Files", "*.*" 'Allow ALL File types
'Test line so I can debug/compile the code
End With
'The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False 'Critical Line
.FilterIndex = 1 'Database files
.ButtonName = strButtonCaption
.InitialFileName = vbNullString
.InitialView = msoFileDialogViewDetails 'Detailed View
.Title = strDialogTitle
If .Show Then
For Each varItem In .SelectedItems 'There will only be 1
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
strSelectedFile = varItem
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
Me![FileLocX] = strHyperlinkFile
Next varItem
End If
End With
End Sub
o Delete: Simple Delete macro
Note: the above VBA code I found on a few different sites
I tested the functionality and once I was satisfied everything works, I deleted my [Scan] field from the [Order Table], compacted the database and went from 665MB to 4.6MB.
I hope this is helpful to all who need to reverse an internally stored attachment to saving it externally.
Chuck
