Store PDFs on Host and Catalogue with SQL Server

Posted on 2012-03-14
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!

Question by:Michael Vasilevsky
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
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.>
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.

LVL 74

Accepted Solution

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:

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.


LVL 10

Author Comment

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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743732

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

621 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