Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-10-21
11
Medium Priority
?
204 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:MCinOH
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37009047
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
 

Author Comment

by:MCinOH
ID: 37009133
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
 

Author Comment

by:MCinOH
ID: 37009147
Just realized what you were asking.

Forms:  Preestablished PDF documents which just need to be recalled and printed.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37009182
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37009188
...OK

Our posts are crossing...
;-)
 You can store a PDF "In" the DB if you use an "Attachment" datatype field.
0
 

Author Comment

by:MCinOH
ID: 37009264
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1500 total points
ID: 37009313
<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
 

Author Comment

by:MCinOH
ID: 37018134
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37019626
<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
 

Author Closing Comment

by:MCinOH
ID: 37024003
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37024822
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question