Solved

Store PDFs on Host and Catalogue with SQL Server

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

828 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