Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Store PDFs on Host and Catalogue with SQL Server

Posted on 2012-03-14
Medium Priority
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

660 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