How To Store Attachments

[David92595]

Hello,
Basics: I'm running a SQL server 2008 R2 back end with an Access 2007 Front end.
I need to find a way to attach documents via my access front end. I've done some research and there seems to be quite the debate about how this should be done.
I don't have a full understanding about the subject, so any help as to the best means to accomplish my goal would be greatly appreciated.
Thank you,
David92595

 

[mdlueck]

Well, there are pros and cons about using any database as a file system. Perhaps a better direction would be a NoSQL product if you are able to implement one, such as Apache Hadoop http://projects.apache.org/projects/hadoop.html

If you insist on using a SQL DB for a file system, then I suppose...

You would want to use a varbinary(MAX) type column to store your attached files.

Definitely use ADO type objects, adjust the Parameter datatype (I had to with VarChar MAX so assume you will need to do the same)

Some useful links for your task:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259

And I have yet to code successfully reading a BLOB back out of the DB and writing it to disk. Be careful with your binary data.

 

[ByteMyzer]

Check out the following link:

Microsoft KB 194975: How To Read and Write BLOBs Using GetChunk and AppendChunk