Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    29

    Unanswered: Export BMP image to a file

    I need to migrate the contents of a table from Access Jet or Ace to SQL Server.

    The table has 2 fields, an integer and a BMP image ex

    Field1 Field2
    1 BMPimage
    2 BMPimage
    3 BMPimage
    4 BMPimage

    If I manage to export the images to files like 1.bmp, 2.bmp, 3.bmp, 4.bmp etc I can then import them into SQL Server.

    Any ideas would be appreciated.
    John

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can always open a file for binary output:
    Code:
    Sub ExportBinary(Image As Variant, FileName As String)
    
        Dim intHandle As Integer
        
        intHandle = FreeFile
        Open FileName For Binary Access Write As #intHandle
        Put #intHandle, , Image
        Close #intHandle
        
    End Sub
    However, why don't you export the images directly to SQL Server without using an intermediate file?
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    29
    Sinndho many thanks for the reply,

    I used the following. It creates the file but I cannot read the file. See attachment when I try to read the file It says that the file may be corrupted or damaged.

    Code:
    Dim intHandle As Integer
    Dim FileName As String
    Dim Image As Variant
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "tblBMPs", CurrentProject.connection, adOpenKeyset, adLockPessimistic
    rst.MoveFirst
    Image = rst!oleBMP
    FileName = "c:\BMPMigration\123.bmp"
        intHandle = FreeFile
        Open FileName For Binary Access Write As #intHandle
        Put #intHandle, , Image
        Close #intHandle
    Attached Thumbnails Attached Thumbnails BMP.png  

  4. #4
    Join Date
    Sep 2011
    Posts
    29
    Also, is there an easier way to export the images from Access to SQL?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Writing a Variant directly to the file is perhaps not the right way to do it. I never tried that before and the binary data of the image must perhaps be formatted before being written to the file (possibly a binary array?), there are many possibilities and options for handling binary data.

    In any case, if you create a SQL Server table that has the same data type (or a corresponding data type) than the one used to store the images in Access, you can then attach (though temporarily) this table in Access then use a query to insert the rows from the Access table to the attached SQL Server table. You can also use the Upsizing wizard in Access or the SQL Server Import and Export Data wizard in SSMS.

    Note: Storing images in the database is not the recommended way. Usually, you store the image file in a dedicated folder and you store the path to the file (the link) in the database.
    Have a nice day!

  6. #6
    Join Date
    Sep 2011
    Posts
    29
    Sinndo thanks for your reply,

    I ended up using OLEtoDisk. A bit kludgy but it works.

    The specific bitmaps need to be in the database, because they are used for the software operation. If I had user created doc or pics then it may make sense to have them outside the db.

    There is no one-to-one correspondence between an Access ole and SQL Server, so in the interest of being practical I opted for the interim solution of exporting the .bmp and reimporting it in to SQL.

    John

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thanks for the feedback.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •