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

Question

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.

Answer 1

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!

Answer 2

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).

Answer 3

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.