How to import a files like PDF MP3 DOC XLS into a MS SQL Server Datatable field using VBA code in Access2007
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.
3 Solutions collect form web for “How to import a files like PDF MP3 DOC XLS into a MS SQL Server Datatable field using VBA code in Access2007”
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).
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
Best of luck!
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.