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.

  • ROW_NUMBER() shows unexpected values
  • When a table in SQL Server has a large-ish nvarchar (say nvarchar(2000)) when the row is stored in the page, do all 4000 bytes get stored?
  • SQL to match the content of a nested XML node
  • Compare Id from a Comma separated value in Sql Server
  • Error connecting to SQL server through string, not recognising user name or password
  • Update column1 case Date between @Date1 AND @Date2
  • 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.LoadFromFile "c:\myfile.pdf"
    rs.Fields("blobfield").Value = mstream.Read

    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.