How to import files like PDF MP3 DOC XLS into MS SQL Server Datatable field using VBA code in Access2007
Does anyone know how to save and delete files in MS SQL-Server 2000? I think image datatype can be used as 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 interface, so I am looking for VBA code.
Thanks in Advance.
I advise you (really!) Not to try (never!) To save files as data in a database. You will quickly run into critical space issues.
Consider creating folders to store your files. They will be used to save / archive your files. Folders for folders can be stored in one of your tables (for example Tbl_Folder
). Then you can store the filenames in a table (for example Tbl_File
, where you would have a field " filename
"). You will be able to open it with a method Access.followHyperlink
and manipulate it (copy / delete / move) with a File Scripting Object (FSO).
source to share
You can do this with GetChunk and AppendChunk.
From this post you may find this link helpful!
Beware:
When using certain providers, most notably ODBC for SQL Server and other databases, you might have to take special care when retrieving BLOB data, for example, placing BLOB columns at the end of the field list and linking all non-BLOB fields to access Columns BLOB.
Good luck!
source to share
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 that contains about 40,000 documents, images, etc. Never had a problem with it for 3 years.
source to share