Topics Search

How I Exported an Attachment fields data to a folder

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

Commas in CSV file

Commas in CSV file Icon
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.
 

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
 

automatic attachment file

automatic attachment file Icon
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.
 

Export word file to folder from Access form

Export word file to folder from Access form Icon
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
 

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?