Storing, recalling, and printing various file types in MS Access 2010 data base as OLE or Binary

I'm currently workling in MS Access 2010 to develop a rather simple datebase to collect information on manufacturing approvals and inspections.

I have my basic DB and forms, however I want to use the DB to store some common forms which would print based on a check box selection on a form.

I undrstand the OLE takes up quite a bit of space, however I only have 8-10 common forms.  The bigger pricture however is we may need to store a number of prints in PDF format.  This task would be large and have hundreds of documents.

I can figure out how to put an OLE object into a table, however how do I then recall it and print it?  Preferable as a hidden task.

Also, I saw a refernce on teh MS website for BLOB read and write functions, however it was for Access V1 or V2.  Anyone have any current code or sample db to do something similar?
MCinOHAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
<I did think about the "attachment" data field, however I'd want to limit this to one document per record.>
...Then make a record for each PDF...

< In additon, how does one recall the atachement programatically?>>
Here is one way:

Dim varApp As Variant
varApp = Shell("Explorer" & " " & "C:\Yourfolder\" & Me.Att_FileName, vbNormalFocus)

See the attached sample, the form named frmAttachment.

Obviously, this can be modified for your specific needs
(and there are probably half a dozen techniques for doing the same thing...)

;-)

JeffCoachman



Database51.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Please be clear, as the term "Form" has many distinct definitions, depending on the the exact situation.

In Access 2010 you can store documents *In* the DB in an "Attachment" datatype field.
ut again, how you would "Print" it is not clear...


IMHO, the simplest thing to do is to keep the "Forms" as images, then simply insert the image on a report.
(size, and scale it properly)
Then simply print the report based on your checkbox.
0
 
MCinOHAuthor Commented:
My Form is an Access DB form accessing tables for the customer, parts, and the user checks which inspections are required.  Based on the user selections, we could need from 1 - 10 different inspection forms to print.

As for Print, either send it to the screen as print preview and have the user send it to the local printer, or we have the option to keep all the forms as 8.5" x 11" Letter, portrait orientation and have the forms hit the default printer directly without user intervention.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MCinOHAuthor Commented:
Just realized what you were asking.

Forms:  Preestablished PDF documents which just need to be recalled and printed.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Again, lets be clear,....
Although you can print a form, a report is typically used.
Then what you are asking for is ridiculously simple...


"My Form is an Access DB form"
Then I'm confused, ...if this is a form in Access, then why are you asking to "Store" it in the db again...
"I want to use the DB to store some common forms"
Am I missing something here?


JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
...OK

Our posts are crossing...
;-)
 You can store a PDF "In" the DB if you use an "Attachment" datatype field.
0
 
MCinOHAuthor Commented:
I did think about the "attachment" data field, however I'd want to limit this to one document per record.  In additon, how does one recall the atachement programatically?
0
 
MCinOHAuthor Commented:
I need to maintain the data in the database therefore, pointing to an outside folder isn't optimal right now.  At some point, I will have to find away to hold larger amounts of information in a binary format in the database, but I haven't found any good code for Access 2010 that works.


Your sample database frmAttachments shows the attachment contained in the table, however it is limited to the first attachement in the list?  I added a second to the container in record 1 and I can't see it.  What is limiting this?

The Open Attachement button calls for an attachment, but has no reference to the attachment in the current record. Shouldn't an Open Attachement button direct to the attachement(s) in the current record?

Related to the Attachment field, where does Access place attachments by default?

Thanks for all your efforts here.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I need to maintain the data in the database therefore, pointing to an outside folder isn't optimal right now. >
In my scenario the File IS store IN the database, the reference to Explorer is just needed to open the file (which is what you asked for)

<Your sample database frmAttachments shows the attachment contained in the table, however it is limited to the first attachement in the list?  I added a second to the container in record 1 and I can't see it.  What is limiting this?>
LOL, ...oddly enough, I just ran into this a few weeks ago,
...Yes Access will (strangely enough) only display the *first* image
You can dispaly the other images, but the only technique I could see was kinda "Hokey"
(I'll see if I can dig up the link)
That is why I suggested one image per record (if this is possible)

<The Open Attachement button calls for an attachment, but has no reference to the attachment in the current record. Shouldn't an Open Attachement button direct to the attachement(s) in the current record?>
My experience is that the Open attachment button IS associated with the current record...

<Related to the Attachment field, where does Access place attachments by default?>
It makes a copy of the file and inserts this copy "Into" the database file.
0
 
MCinOHAuthor Commented:
Closing this question for the moment.  I want to revisit this again when I have a better idea of the functionality I need and how I can accurately discuss it.  Thank you for your efforts.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK

Just a few notes on storing files "In" the DB.
1. It can be confusing when trying to create a system to "Update" these files.
2. It is a bit complex getting the files "Out" of the DB.
3. Size is still an issue, storing Hundreds of files (especially hi-rez images) will quickly "bloat" the db.
4. If the db gets corrupt, you may lose all of these file (if you don't have a solid back up plan)
5. There is no direct equivalent SQL Server field, so when it comes time to upgrade, you will lose this functionality...

JeffCoachman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.