SQL 2000/Access 2K

[Mike Shea]

I have an application that currently uses Access 2000 as a back end. I want to recreate this application using an SQL Server.

The applications core functionality is a document management system. I am copying PDF files to a server location and changing their attributes in an attempt to hide them or make it more difficult to accidentally delete. Ideally, to make this a more grown-up application is to store these PDF's in the SQL Server itself. I'm not really fluent in SQL Server and was wondering if anyone can offer me some advice on how this can be done. I am assuming this is possible, but I don't know what data type to use in the field definition when I create it, and I am not exactly sure how to persist the file.

And if any moderators are watching, is this the appropriate place to post a SQL Server question - or is this topic supported in any lounge forum?

[jscher2000]

Some thoughts...

(1) I liked the parts of this book that I skimmed. Good way to bridge the two worlds.
Microsoft Access Developer's Guide to SQL Server by Mary Chipman, Andy Baron

(2) Think about why you want to store a blob of mostly unintelligible, unstandardized data in a database field - is there any database feature you want that requires it? If not, I think you're better off not doing it. PDFs can be very big, and there's no reason to tie up your database server dishing out stored files.

If you look at the marketing materials for iManage vs. GroupWise, you will see this debated over and over: GroupWise stores all the documents as blobs in the database, iManage uses the database to manage access to files. Back and forth they go...

And... if you don't get lively traffic to your thread on this board, try the Access forum.

[Mike Shea]

Part of the reason is that it is a medical system application that requires security. Currently my method of storing the docs in a network folder and changing the attributes is a little hap hazard (sp). Leveraging the security of SQL I think would alleviate this issue.

I will look at the book you recommended.

[jscher2000]

You could encrypt the files and store a key in the record... not sure if it's practical to generate so many keys, and it's scary to think about losing a bunch of them if the database has a bad day. Hmmm...

[Mike Shea]

Interesting idea. I would rather not use a file/directory system to store the PDF documents if I can avoid it. We aren't talking about a huge amount of docs anyway (not to say it wont grow to huge in a few years). Did you agree that IF I were to run with the SQL storage method that it would be a BLOB data type that I need to look at? And would it be a stream object to persist the document to the database somehow?

[jscher2000]

Sorry, I have no clue on that. I have not looked at storing anything in MSDE/SQL Server other than words and numbers.