How I Exported an Attachment fields data to a folder

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 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. it is over 660MB in size and growing with every scanned document.

I started doing research online to figure out how I could export 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.

Maintaining the relationship to thelocation 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:

Sponsored Links:

Related Topics

Output File to a different path
I would like to use a macro to export tables in Excel. There are 14 spreadhseets every two weeks that will be exported. I know how to export them with a macro, but is there a way to use a form or something to change the folder that these files will be exported to? Basically, every two weeks, I want these files to go to a new folder but I don't want to have to go to each lne in the macro and update the folder name. Can a form be used to enter the folder name before the macro is ran


Excel Macro to exported ACCESS files
I have read a few available functions similar to my concerns. And I have noticed some were not answered for a long time. when a query or a table has been exported to Excel from Access.

It does not open with the Excel Macro in order to run. I was able to transfer the data to Excel and I was able to open the Macro Excel but when it runs, it runs on itself not on the exported file.

As of right now all of my Transferspreadsheets are in ACCESS Macro, not in VBA and they are being exported as expected. I just need to add a RunCode using Module that will coincide with the Transferspreadsheets as it gets exported to the desired folder and run it and save.

I am pretty confident that this could beaccomplished but needs to make sure the Excel Macro is working on the exported file not on itself.


hyperlink to (non-network file) to email
But I’ve been searching for about a week to see if the following is possible.

On a form, I can see a record’s information, including two hyperlink attachments (one links to a folder, the other to another folder or file on the local computer). I click the link and each opens, no issues.

What I would like to do:
-Click a command button that opens an email (I have that down),
-The coding would then look at the current record, identify the two attachment links, pull the file from the attachment and add it to the email from my local machine (the crazy part).
-To add, I’d like the files to be zipped to the attachment (the crazier part).
-For example, the file is stored at C:\Users\Steve21nj\Desktop\ Items\Email\MGA-12-60000

The reasoning: We will need to send audit reports based on the pdf’s in the folder (the attachments). I’d like to eliminate user error by not attaching the correct file/folder.

I have seen the ability to push the hyperlink itself on the email, but not the hyperlink’s data (file) into the email. Also, I have read about the hatred for hyperlink fields, is there abetter alternative for the above?


Creating Viewport into a folder
I would like to create a page that has a viewport into a folder (showing all the subfolders with in that folder). See Attachment;

I would like to be able to double click on the subfolder and open them up as if I had gone to the directory/folder.


Attachment file location change
I have a large database that contains employee pictures as an attachment. All pictures reside in the same folder. Is there a way to change the name of that folder globally


Commas in CSV file
I have some fields in a record that are being exported to another type of program using a CSV file. Everything was working fine until one time a comma was used in one of the exported fields. To prevent this problem in the future I would like to not permit commas in these fields that will be exported at the time the data is being inputted. I have seen that in the field would prevent this but unless there is a method to easily hide this in the background without any user input I dont mind just not allowing commas.


Updating records with attachment fields
I maintain a 9 year old database which I recently converted to Access 2007. While doing so, an attachment field was added to one of the tables.
My problem: I recently gathered some data in the attachment fields in the development copy of the database and wanted to transfer the additions to the production table. The update query came back with a message to the effect that you can't use an attachment field in an update query.
Has anyone else encountered this problem? If so, what is the workaround? VB or manual re-entry of the data


Updating records with attachment fields
I maintain a 9 year old database which I recently converted to Access 2007. While doing so, My problem: I recently gathered some data in the attachment fields in the development copy of the database and wanted to transfer the additions to the production table. The update querycame back with a message to the effect that you can't use an attachment field in an update query.
If so, what is the workaround? VB or manual re-entry of the data?


Export word file to folder from Access form
Perhaps I am pushing my luck to far but some time ago I did ask this question and did not found a solution. Since you have been great until now, I am submittin a sample zip file of my db so that you can better understand what is my project.
Data from my access forms are normally exported to word templates. Once I click on a cmdbutton, the data is exported using bookmarks and opens the word file. Users then can choose if to save it and where to save it.
I would like to avoid this step. I would like to place a second cmd button which directly exports data the word file and saves it into a specific folder (let's say it could be C:\myforms\myname

Can anyone help with this? I believe the procedure will need some input box so that users can assign the name to the file (as they are templates) before saving it


Attachments (fields)
I would like to create an attachment field in a table that I can use in a form. When I try to create a attachment field I am going to datasheet tab/ data type & formating/ data type clickon the drop down list and no option for attachment in design or data sheet view is their a setting or something I need to setup?


automatic attachment file
I'm using Access 2007 and the attachment data type on a form, but it would be nice not to have to browse to the file I want each time. Is there a way to automatically retrieve the file I want for the attachment based on the file name being written in a different field on that form? For example, for each record, there is a .jpg file to be attached. All of the .jpgs are stored in the same folder, so that path would always be the same except for the file name itself, which would be the contents of another field with .jpg added on the end. I still want to be able to edit attachments or add other attachments to each record, but the .jpg attachment will always be there.


How to change default export locaton for reports ?
When I export a Report to txt file, by default it is exported to 'User/My Documents' folder on C drive. For my application, I want the file to be exported to the same folder which contains the database. Is this possible ? I guess (and hope) it would be as simple as executing some code for relative path before the export; just don't know the


Search for an attachment in a mailbox
Search for an attachment in a mailbox
Is there sample VBA code I can use to resolve the following:
Access a mail server and loop through the mailbox and search for a specific attachment name, if found download it to a folder.


how to copy query results to outlook public folder
I need to send consolidated data in spreadsheet from access database to group of people everyday. What I am thinking is, instead of sending an attachment everyday, copy spreadsheet to public folder and send them an email announcing new data on the public folder. And I don't need old copy of spreadsheet so just want to overwrite the old one


Access 2007: How can I open all attachments in a table at once?
This should be really really easy, but I (and google) just cant get it. I have this huge table of hundred rows, and each row has an attachment. Now I can easily open an attachment on a row, but I need to open all of them. No way am I going to go that line by line.

Is there any way to just highlight the attachment column and save all to a selected folder?


import excel into form with combobox fields
We built a database (MS Access 2007) with several tables. The master table was built correctly with FKs from the other tables in order to have auto populating fields in a form. (combo boxes were used). It worked well.

We 'exported' data from the FORM. This exported data into a single excel spreadsheet with columns which included fields from several of the access tables. (because of the combo boxes).

We made changes to the single excel spreadsheet (but no new or changed names of columns/fields - only typing errors of data).

Then we import BACK into the DB - but it wants to import into a TABLE not a FORM. There is not a single TABLE that has all the values because the FORM was made up of several TABLES.

So how do we get the exported data, imported back in? Should this be easy?


Auto Naming an exported Access Table to a text (or) excel file
I have tried a few modules and macro's but without a deep-seated knowledge of VBA I'm a little stuck.

All I need to do is to export my table data [GOODS IN] every evening (by pressing a command button).
This exports the table to a specified folder.
However I need it to auto name the exported file by date and time (at the point at which the command is started


attachment fields
Previously using Access 2003 now using 2007 for the first time - was using a text field for paths to images for displaying on forms, now I see there are attachment fields that seem to be for images. Question is how do I convert the text field into an attachment? Or should I even be trying


Attachment option
I don't have the attachment option on the data type drop down list for table fields. How do I get it included


Manage attachments
I have a form in which the fields are enabled or disabled based on user permissions. If a user has only 'Read' permission then all the fields are disabled else the fields are enabled. Now I also have an attachment field in my form. If I disable the attachment field, then the user is unable to view the attachments. I want the users with 'Read' permission to view the attachments but not manipulate it. Like, a user with 'Read' permission must not be able to 'Add' or 'Remove' an attachment, but he must be able to 'Open' the attachment and 'Save' the attachment.

P.S: I have used the default 'attachment' which comes with MS-Access, that is I have added a field with the datatype 'attachment' and made my textbox a bound field in the form