Solved

Store PDFs on Host and Catalogue with SQL Server

Posted on 2012-03-14
5
322 Views
Last Modified: 2012-03-20
I'm interested in creating an application that is an MS Access frontend connected to a remote SQL Server 2008 backend that "catalogues" PDF documents on a server. So the PDFs would reside in a simple Windows folder structure, and then SQL Server would store the file name and path, and some metadata about each file. The MS Access frontend would be used to view the metadata and upload/download files.

Anyone done something like this or know a good place to start?
Any help is appreciated!
Thanks,

MV
0
Comment
Question by:Michael Vasilevsky
  • 3
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37723171
Pleas clearly explain in detail what this means:

<The MS Access frontend would be used to view the metadata and upload/download files.>
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 37726128
Ok to better illustrate, please find the attached MS Access example: it has one form and one table with a document hyperlink. If I open the form and click the hyperlink it opens the PDF. I want to do the same thing but have the backend be a SQL Server database and the files reside on the remote server, instead of my C:\ drive.

Is this possible? Any examples?
Part two will be vba code to upload the file to the remote server, but that can wait for another question.
Thanks!

MV
Documents-Example.zip
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37740616
It can be done, just note that the Hyperlink Datatype does not exist outside of MS Access, so you will have to simulate it.

Basically everything will be the same except the Full path and File name will be stored as TEXT.
You will create the table in SQL Server and Link to in in Access.
Then create a form from this table.

Change the IsHyperlink Property of the textbox to: Yes
(...This should make the path "clickable")
(You may want to see here, if the cursor does not change to a "hand" when you hover over it: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21350826.html)


If the above does not work, then you can do something like this on the click event of the textbox:
Application.followhyperlink me.txtYourFullpathAndFileNameTextbox


To be even more comprehensive, you can use web browser control to display the PDF on the form directly (possibly eliminating the need to "Open it")

Finally the form allows you to find and store the Full path and file name

Sample attached...
Study it thoroughly...
I am sure you can adapt it to work in your database.

;-)

JeffCoachman
Access-BasicLinkFormOnlyPDFWebBr.mdb
0
 
LVL 10

Author Comment

by:Michael Vasilevsky
ID: 37743509
Very nice that will certainly get me going in the right direction!
Thanks,

MV
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743732
;-)
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now