Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Store PDFs on Host and Catalogue with SQL Server

Posted on 2012-03-14
5
Medium Priority
?
338 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 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: 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

877 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