Linking pdf files

[Guest]

I have a table with about 12,000 records. Each record has a hyperlink set up
to a path where pdf files reside -- although each record has the hyperlink
path set up, there isn't an actual pdf file for every single record --
eventually there will be. Right now, the user can see the link, but has no
idea if a file actually exists or not. He can figure it out once he clicks
on the link and the pdf file does not show up. However, I would like the
form to show him somehow if there is a pdf file for that particular file or
not -- without manually going in and setting a field separate to "yes/no." I
thought I could perhaps set up a bmp file that says "No PDF file Available,"
which would show up if there is no pdf file in the physical location of the
path. But, I am at a loss on how to do it. Any help at all would be
greatly appreciated. I do not know Visual Basic, but can cut and paste code
if given enough instruction. Thanks so much!

 

[Arvin Meyer [MVP]]

How about this? Add the following function to a standard module:

Public Function FileExists(strFileSpec As String) As Boolean
Dim intFileLength As Integer
On Error Resume Next

intFileLength = FileLen(strFileSpec)

If Err = 53 Then
FileExists = False
Else
FileExists = True
End If

End Function

Now use it to check whether the file exists before you open it. Use a
command button to follow the hyperlink:

Private Sub cmdHyperlink_Click()
Dim strPath As String
If Not IsNull(Me.txtBoxName) Then
If FileExists() = True
strPath = Me.txtBoxName
Me.cmdHyperlink.HyperlinkAddress = strPath
Else
MsgBox "File not found", vbOKOnly, "No PDF"
End If
End If
End Sub

 

[Guest]

Thanks for the reply, I tried to understand your suggestion, but couldn't.
Not because it didn't make sense -- I'm sure it does; but, because I do not
know code at all. I cut and paste it into a sample form, but I wasn't sure
if I needed to edit the code. I'm sorry.

 

[Guest]

Arvin - or anyone else that may be able to help. Is there possibly a way to
view the PDF files in the form? At least that way they know if they do not
see a pdf file, they know there is not one out there. I can actually get
the form to view the first PDF file, but I can't link the bound object frame
to the control that contains the path of the files so that it changes with
the records.

Or what about code that could be run every so often which looks for the
files per their paths in the table field, then for the records for which it
finds a pdf fileit updates a yes/no field in the table -- thus indicating
there is a file.

I need to get this to work somehow -- any and all help will be very much
appreciated!

 

[Arvin Meyer [MVP]]

OK.

FileExists is a Public function that goes into a standard module, If you put
it in a form module, it can only be used when that form is open. If you put
it in a standard module, it is much easier to use. I also just noticed my
code has a slight error, which is easily fixed.

So now lets look at this code and see what it says:

strFileSpec is a variable which takes the the of the file, so if you used
the Immediate Window to debug your code and entered:

?FileExists("C:\Windows\win.ini")

and hit the Enter key, the answer you'd get back would be: True

The code in the form event for a command button named: cmdHyperlink

should look like:

Private Sub cmdHyperlink_Click()
Dim strPath As String
strPath = Me.txtBoxName

If Not IsNull(strPath) Then
If FileExists(strPath) = True
Me.cmdHyperlink.HyperlinkAddress = strPath
Else
MsgBox "File not found", vbOKOnly, "No PDF"
End If
End If
End Sub

strPath is the path to the file and in this case will have the same value as
strFileSpec, and that value is the path: C:\Windows\win.ini, or in your case
the PDF file path on your hard drive or the server. So now, when you click
the button, it checks first is there a value to check in the textbox:

If Not IsNull(strPath) Then

If that's true, does the file's path in the text box actually exist?

If FileExists(strPath) = True

Now if it does, open it using that path as a hyperlink (note: hyperlinks are
merely a path with special properties, so just by calling the path a
hyperlink address, VBA (the code language) will treat it that way and open
the file associated with that file type and path.

If it doesn't exist, tell the use by showing a message box that says "File
not found"

Make sense now?
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thank you so much for explaining it more. So, in this case, the user would
still have to click on the hyperlink to see if there is a file, right? And,
if there isn't a file, a message box would appear stating "file not found."
Is there a way the user can see that there is or isn't a file there without
clicking on the hyperlink? What about a bound object frame that showed the
pdf file in zoom view? That way, if they see the object in the form, they
know there is a pdf file there. I tried to insert the bound object frame,
but could not get it to change from record to record. I can get something
similar to work with pictures using an image control -- but not pdfs.

Arvin, thank you for taking the time to answer my questions.

 

[Arvin Meyer [MVP]]

No. There really isn't a hyperlink, just a command button which I named
"cmdHyperlink" because it functions kind of like one.

You could use a bound object frame, but they are terribly inefficient and
quickly bloat a database. If you have more than a few dozen records it will
start getting verrrrrrrrrrrry sloooooow.

One of the things you can do to speed everything up is to ditch Adobe
Acrobat reader. It's a dog! I use a PDF reader named Foxit:

http://www.foxitsoftware.com/pdf/rd_intro.php

which is not only better than Acrobat, but much faster. It will open you 50
page PDF, before Adobe has finished the spalsh screen. Adobe used to have an
ActiveX control named PDF.ocx that worked more quickly and could be used in
an Access form, but they quit using that ActiveX control in version 4.05 and
all my PDF code broke. I'm not sure, but I think that Foxit may have an
ActiveX control, but it wouldn't be free. Only their reader is free.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

I will look into the Foxit software. Until then, how do I get the bound
object to change from record to record? Right now, my bound object remains
the same -- showing the PDF file linked to the first record only. How can I
get it to change when I change records?

 

[Arvin Meyer [MVP]]

Have you inserted the bound object to each and every record? Paths don't
work for Bound Objects, you need to insert each one separately. If you did,
you should find out very quickly that you will not have enough memory to
handle large PDF files as bound objects.

Let me repeat this one last time. You do not want to use Bound Object
frames. They will bloat the database. If nothing else, add a Boolean field
that you can update manually if there is no PDF, or check for the file
existence using FileExists() in the Current event of the form and use that
to update the bound Boolean checkbox, or even use it to load the PDF. I
don't recommend doing that because you will eventually run out of memory if
you keep loading PDFs that aren't needed as you scroll through the records.

You think you are doing everyone a favor by displaying a thumbnail or the
PDF itself as they scroll. When their machines start crashing or your
program runs out of memory, they won't think so. It takes less than 1/2
second on my machine to check for the file's existence and open a PDF or a
message box. It takes almost that same time to scroll through each of the
first few PDF bound records. As you begin to use up memory, it will take far
longer.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

I'm apologize for my lack of knowledge. I hope I am not irritating you with
all my questions.

No, I did not insert the bound object into each record. I have hyperlinks
in each record to paths containing the pdf files. I thought a bound object
frame could be linked to those paths and change with the records -- my bad.

Ok - so your suggestion of updating a boolean field sounds like my best
solution. I created the field -- PDF Available -- where if the file actually
exists, that field would need to be updated to "-1". I would like to try
FileExists() as you suggested. The field where the hyperlink paths are
located is called -- PDF Path. The table that both these fields (PDF
Available and PDF Path) is called -- INVENTORY CERTS. I think it would be
best if this runs when the database is opened rather than everytime the form
opened -- I am assuming it takes a little time to verify the existence of all
those files.

Can I use some of the code you gave me before and alter it so that it
updates the boolean field -- PDF Available? In using FileExists(), can I
insert the field name (PDF Path) since it contains the path?

I really appreciate all your help and patience with me.

 

[Arvin Meyer [MVP]]

A better way to update your pdf path table is to use my DocMgr sample
database:

http://www.datastrat.com/Download/DocMgr_2K.zip

Find the procedure named: ReadFolderInfo in the modPath_File module and
change:

Case "DOC", "TXT", "XLS"

to:

Case "PDF"

Then compile and save. It will then allow you to find every PDF in a folder,
including all subfolders. You can then copy and paste, import, and/or use an
update query to move that data to your own tables.

You will not need the overhead of hyperlink tables since the GetFile
function uses the hyperlink property of a text file to open the PDF. The
other advantage is that you can search a stored keyword to find the file you
want.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Arvin, you are a genius! I love it! My table was built in no time.

I have yet to try to use the GetFile function to open the pdf files -- but
will do so soon. Thank you so much for all your help.

I have one more Access/VB issue I will have to face in the near future,
don't know if you can help, but . . . . Is there a way, from Access, to
automatically email a report and attach some of these pdf files to the email?
A report might list 10 records, and of those records, where there is a pdf
file path, we would want it attached to the email. If there is a way to zip
them first, that would be great, but not necessary.

Thanks again for everything!

 

[Arvin Meyer [MVP]]

Never tried the zip, but I suspect it could be done. The email is a piece of
cake though. It involves 2 outside programs. I refuse to use Adobe Acrobat
because they're support is extremely poor to developers. Instead, I use
Win2PDF:

http://www.win2pdf.com

Not only is Win2PDF about 1/10th the price of Acrobat, but they offer a
better program that will create a 50 page Access report to PDF faster than
Acrobat can open its splash screen.

The other program is Steve Arbaugh's PDF & Mail class which is an Access
addin:

http://www.groupacg.com/

Steve's code, takes an Access report, and using a PDF maker, creates and
email's the PDF. I used it with Win2PDF to create a PDF Purchase Order,
merge it with another PDF, if another existed, and automatically email it. I
averaged 1200 PDF emails every 2 hours. The code was actually faster than
that but I had to put a delay in it because it was choking the mail servers.
The delay code is on my website:

http://www.datastrat.com/Code/Delay.txt

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Arvin, it looks as if the PDF & Mail would be a great solution, but I cannot
get in touch with anyone at groupacg.com. There is no contact information or
email address. I'm not sure if I buy it I would be able to get it to work
for me given my limited experience with code and add-ins. Will I need to
know any code to make the program work for me? Do you have a contact at ACG?

Thanks!

ps. I bet you thought you were done with all my questions, didn't you?

 

[Douglas J. Steele]

If you go to http://www.groupacg.com/, the last entry on the left-hand side
is "How to Contact ACG Soft"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

Doug, there is no email address provided or any other contact information on
the website under "How to Contact ACG Soft."

Have you purchased/used the PDF and Mail or any other products from ACG?

 

[Douglas J. Steele]

That's odd. I see an e-mail address when I look there.

75323.2112 <at> compuserve.com

 

[Guest]

Hi,
I'm getting a Syntex error on this line.

If FileExists(strPath) = True

What could be the reason for that?

 

[Guest]

I finally got in touch with someone at ACG. They said it would take quite a
bit of code to be able to use PDF and Mail to email an Access report along
with the related PDF files listed in the report. Does anyone have code that
performs a similar task that I can use with PDF and Mail to get me started?
Is there any chance a beginner like me can do this?