Topics Search

How I Exported an Attachment fields data to a folder

How I Exported an Attachment fields data to a folder
Views: 60
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:

Similar posts...


Updating records with attachment fields

Updating records with attachment fields Icon
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

Excel Macro to exported ACCESS files

Excel Macro to exported ACCESS files Icon
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.

Attachments (fields)

Attachments (fields) Icon
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?

hyperlink to (non-network file) to email

hyperlink to (non-network file) to email Icon
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?

Output File to a different path

Output File to a different path Icon
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

Attachment file location change

Attachment file location change Icon
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

import excel into form with combobox fields

import excel into form with combobox fields Icon
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?

Exporting invisible fields on a form to Excel

Exporting invisible fields on a form to Excel Icon
I'm using Access 2010 and I have a form that will be used to select or filter records which will then be exported to Excel. However, I only want to show the critical fields that will be used to make the decision on which records to export on the form but export a far larger set of fields.

The data set of the form is actually a query that contains all the fields required for the export.

If I place all the fields on the (continuous) form but make the ones the user doesn't need to see invisible they are not exported using the standard external data->export/Excel. I can think of a couple of ways round this like making all the fields visible but transparent/white font etc or placing a button on the form and generating the export by dynamically creatinga query based on any filtering the user may have chosen.

However, I was wondering if there is a tidier way to achieve the export of the required fields?

Attachment Field problems

Attachment Field problems Icon
I have created a small simple database with fields for first and last names and an attachment field for pictures. This is for a photo directory of members of an organization. I tested the program using family pictures until I had everything working.

I deleted data and then started entering member data for the directory. Now for the problem. an old family picture shows up until I add a new picture.

2010 and unable to add attachment.

2010 and unable to add attachment. Icon
I keep getting message"operation is not supported for this type of object". I checked under Design View and the attachment is not listed under the data type nor is the attachment button enabled in the datasheet view.

I would like to add an image to individual data files.