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.

  • How to get SQL Server DateTime field in ODBC native client
  • How to find missing rows?
  • Listing all indexes
  • SqlDataAdapter.Fill(DataTable) not filling DataTable
  • How to display arabic characters in web page using php and sql server
  • SQL Agent Job - Connection may not be configured correctly or you may not have the right permissions on this connection?
  • 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
    BLOB columns.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.