How to import a files like PDF MP3 DOC XLS into a MS SQL Server Datatable field using VBA code in Access2007

Hi,

does anybody know how to save and retieve files in MS SQL-Server 2000? I guess the image data type could be used as a container.

I want to import/export the following file types: DOC, XLS, PDF, BMP, TIFF, etc.

Due to resource issues we are using MS-Access2007 as the front end, so I am looking for VBA code.

Thanks in Advance.

From stackoverflow

• You can do this using GetChunk and AppendChunk.

  From this post you might find this link helpful!

  One thing to watch out for:

  When using certain providers, most notably ODBC to SQL Server and other databases, you may have to take special care in retrieving BLOB data, such as placing BLOB columns at the end of the field list and referencing all non-BLOB fields prior to access BLOB columns.

  Best of luck!

• I advise you (really!) not to try (ever!) to save files as data in a database. You will quickly face critical space problems.

  Please think about creating folders for file storage. These will be used to save/archive your files. Folders paths can be stored in one of your tables (Tbl_Folder, for example). You can then store your file names in a table (such as Tbl_File, where you'll have a 'filename' field). You will be able to open it with the Access.followHyperlink method and to manage it (copy/delete/move) with the File Scripting Object (FSO).

  David-W-Fenton : While I agree with your main point about avoiding storing files in the database, there's one exception: if you are using replication. In that case, storing the files in the database is the easiest way to keep multiple locations synchronized with the same data in the files.

  Philippe Grondier : But the cost of storing files in the database (in terms of disk space and volume of exchange) is so huge that we developped a "home-made" solution by combining data replication and files & folders replication.

  Mike Shepard : I think there are probably several techniques for synchronizing file systems that are probably a lot less problematic than db replication. I vote for not storing files in the db.

• You can do this with streams. This code should help you with the first steps:

  Set rs = New ADODB.Recordset
  rs.Open "select * from YourTable", Connection, adOpenKeyset, adLockOptimistic
  
  Set mstream = New ADODB.Stream
  mstream.Type = adTypeBinary
  mstream.Open
  mstream.LoadFromFile "c:\myfile.pdf"
  rs.Fields("blobfield").Value = mstream.Read
  rs.Update
  

  There is nothing dangerous about storing files in a database. We have a SQL Server database of about 20 GB which contains about 40.000 documents, images, etc. Never had a problem with it in over 3 years.