none
can't find binary data stored in MS access 2000 database RRS feed

  • Question

  • Hi everyone,

     

    First please let me explain.  I am attempting to store pdf files in an MS Access DB (2000) and I have written a subroutine to do this. My code seems to work perfectly (see code below).

     

    Imports System.IO

    Imports System.Data.OleDb

     

    Sub StoreFile(ByVal Field As String, ByVal FileName As String, ByVal Condition As String)

         Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Table WHERE " & Condition, strConn)

         Dim cb As New OleDb.OleDbCommandBuilder(da)

         Dim strConn As String = "Provider=microsoft.jet.oledb.4.0; data source=" & "C:\data.mdb"

         Dim dbConnection As New OleDbConnection(strConn)

         Dim ds As New DataSet()

         Dim arr() As Byte 'Create array (check the data type - Byte)

     

         dbConnection.Open()

     

    '****************Read file into the binary array*************************************************************************************

         da.Fill(ds)

     

         ReDim arr(FileLen(FileName) - 1)           'Resize array to the same size as length of file

         FileOpen(1, FileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared)       'Open the file for reading

         FileGet(1, arr)                      'Read the file into array (just one line...)

         FileClose(1)                         'Close file

    '**************************************************************************************************************************************

    '*************Save array data to database*****************************************************************************************

         ds.Tables(0).Rows(0).Item(Field) = arr 'Store element in array into column(BinaryData) of the row(0).

         da.Update(ds) 'Update database

    '***************************************************************************************************************************************

         dbConnection.Close()

    End Sub

     

    I then use the following code to load a directory of pdf files into the DB.

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

         Dim dir_info As New DirectoryInfo("C:\X")

         Dim file_infos() As FileInfo

         Dim i As Integer = 1

         Dim file_info As FileInfo

     

         file_infos = dir_info.GetFiles("*.PDF")

     

         For Each file_info In dir_info.GetFiles("*.PDF")

              StoreFile("BinaryData", "C:\X", "PN = 'part" & "i")

              i = i + 1

         Next

    End Sub

     

    The problem is that it only works for the very first file.  When I look in the DB I see "Long Binary Data" in the correct field for the first record but the same field for all the other records are empty.  The field data type is OLE Object.

     

    The odd thing is that the DB file size will grow to almost the same size as the folder of pdf files that I am trying to store in the DB (700MB).

     

     If I use the [Compact and Repair] function in access the file goes back to its original size (3.16MB).

     

    What am I doing wrong?  Is this an Access problem?  Any and all help is welcome.  Much thanks.

    Sunday, June 3, 2007 2:18 AM

Answers

  • OK, I figured this one out myself.

     

    Just to let everyone know I am not usually a complete idiot but in this case I was.  All I need to do was demension da within the Sub StoreFile.  I know that's exactly what I have in the code sample but its not what I had in my project. 

     

    The reason only the first field was filled with data is that it kept writting over what was already there.  This is also why the size of the access database became so large.  Access like to keep unused junk.  This explains why the database size returned to normal after doing a "repair and compact"

     

    So I hope someone will benifit off of my dumb *** mistake, LOL.

     

    below is the code that works.

     

     

    Imports System.IO

    Imports System.Data.OleDb

     

    Public Class Form1

    Dim strConn As String = "Provider=microsoft.jet.oledb.4.0; data source= K:\My Documents\Visual Studio 2005\Projects\Customer Operations Application\Customer Operations Application\Data.mdb"

    Public dbConnection As New OleDbConnection(strConn)

    Dim ds As New DataSet()

    Dim arr() As Byte 'Create array (check the data type - Byte)

     

    Sub StoreFile(ByVal Field As String, ByVal FileName As String, ByVal PN As String, ByVal Rev As String)

    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tblPages WHERE PN = '" & PN & "' AND Rev = '" & Rev & "'", strConn)

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    Dim dsSF As New DataSet

    '****************Read file into the binary array*************************************************************************************

    da.Fill(dsSF)

    My.Application.DoEvents()

     

    ReDim arr(FileLen(FileName) - 1) 'Dynamically resize array to the same size as length of file

    FileOpen(1, FileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared) 'Open the file for reading

    FileGet(1, arr) 'Read the file into array (just one line...)

    My.Application.DoEvents()

    FileClose(1) 'If you open a file, you MUST close it

    '********************************************************************************************************************************

    '*************Save array data to database*****************************************************************************************

    dsSF.Tables(0).Rows(0).Item(Field) = arr 'Store element in array into column(BinaryData) of the row(0).

    da.Update(dsSF) 'Update database

    '********************************************************************************************************************************

    My.Application.DoEvents()

    End Sub

     

    I then use the following code to load a directory of pdf files into the DB.

     

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim dir_info As New DirectoryInfo("C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All")

    Dim file_infos() As FileInfo

    Dim i As Integer = 1

    Dim file_info As FileInfo

    Dim DirSource As String = "C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All\"

     

    file_infos = dir_info.GetFiles("*.PDF")

    For Each file_info In dir_info.GetFiles("*.PDF")

    Dim FileSource As String = DirSource & file_info.Name

    Dim PN As String = file_info.Name.Substring(0, 9)

    Dim Rev As String = file_info.Name.Substring(11, file_info.Name.IndexOf(".") - 11)

    StoreFile("BinaryData", FileSource, PN, Rev)

    Me.Label1.Text = i - UBound(dir_info.GetFiles("*.PDF"))

    i = i + 1

    Next

    End Sub

    Sunday, June 10, 2007 8:04 PM

All replies

  • OK, I figured this one out myself.

     

    Just to let everyone know I am not usually a complete idiot but in this case I was.  All I need to do was demension da within the Sub StoreFile.  I know that's exactly what I have in the code sample but its not what I had in my project. 

     

    The reason only the first field was filled with data is that it kept writting over what was already there.  This is also why the size of the access database became so large.  Access like to keep unused junk.  This explains why the database size returned to normal after doing a "repair and compact"

     

    So I hope someone will benifit off of my dumb *** mistake, LOL.

     

    below is the code that works.

     

     

    Imports System.IO

    Imports System.Data.OleDb

     

    Public Class Form1

    Dim strConn As String = "Provider=microsoft.jet.oledb.4.0; data source= K:\My Documents\Visual Studio 2005\Projects\Customer Operations Application\Customer Operations Application\Data.mdb"

    Public dbConnection As New OleDbConnection(strConn)

    Dim ds As New DataSet()

    Dim arr() As Byte 'Create array (check the data type - Byte)

     

    Sub StoreFile(ByVal Field As String, ByVal FileName As String, ByVal PN As String, ByVal Rev As String)

    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tblPages WHERE PN = '" & PN & "' AND Rev = '" & Rev & "'", strConn)

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    Dim dsSF As New DataSet

    '****************Read file into the binary array*************************************************************************************

    da.Fill(dsSF)

    My.Application.DoEvents()

     

    ReDim arr(FileLen(FileName) - 1) 'Dynamically resize array to the same size as length of file

    FileOpen(1, FileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared) 'Open the file for reading

    FileGet(1, arr) 'Read the file into array (just one line...)

    My.Application.DoEvents()

    FileClose(1) 'If you open a file, you MUST close it

    '********************************************************************************************************************************

    '*************Save array data to database*****************************************************************************************

    dsSF.Tables(0).Rows(0).Item(Field) = arr 'Store element in array into column(BinaryData) of the row(0).

    da.Update(dsSF) 'Update database

    '********************************************************************************************************************************

    My.Application.DoEvents()

    End Sub

     

    I then use the following code to load a directory of pdf files into the DB.

     

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim dir_info As New DirectoryInfo("C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All")

    Dim file_infos() As FileInfo

    Dim i As Integer = 1

    Dim file_info As FileInfo

    Dim DirSource As String = "C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All\"

     

    file_infos = dir_info.GetFiles("*.PDF")

    For Each file_info In dir_info.GetFiles("*.PDF")

    Dim FileSource As String = DirSource & file_info.Name

    Dim PN As String = file_info.Name.Substring(0, 9)

    Dim Rev As String = file_info.Name.Substring(11, file_info.Name.IndexOf(".") - 11)

    StoreFile("BinaryData", FileSource, PN, Rev)

    Me.Label1.Text = i - UBound(dir_info.GetFiles("*.PDF"))

    i = i + 1

    Next

    End Sub

    Sunday, June 10, 2007 8:04 PM
  • OK, I figured this one out myself.

     

    Just to let everyone know I am not usually a complete idiot but in this case I was.  All I need to do was demension da within the Sub StoreFile.  I know that's exactly what I have in the code sample but its not what I had in my project. 

     

    The reason only the first field was filled with data is that it kept writting over what was already there.  This is also why the size of the access database became so large.  Access like to keep unused junk.  This explains why the database size returned to normal after doing a "repair and compact"

     

    So I hope someone will benifit off of my dumb *** mistake, LOL.

     

    below is the code that works.

     

     

    Imports System.IO

    Imports System.Data.OleDb

     

    Public Class Form1

    Dim strConn As String = "Provider=microsoft.jet.oledb.4.0; data source= K:\My Documents\Visual Studio 2005\Projects\Customer Operations Application\Customer Operations Application\Data.mdb"

    Public dbConnection As New OleDbConnection(strConn)

    Dim ds As New DataSet()

    Dim arr() As Byte 'Create array (check the data type - Byte)

     

    Sub StoreFile(ByVal Field As String, ByVal FileName As String, ByVal PN As String, ByVal Rev As String)

    Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM tblPages WHERE PN = '" & PN & "' AND Rev = '" & Rev & "'", strConn)

    Dim cb As New OleDb.OleDbCommandBuilder(da)

    Dim dsSF As New DataSet

    '****************Read file into the binary array*************************************************************************************

    da.Fill(dsSF)

    My.Application.DoEvents()

     

    ReDim arr(FileLen(FileName) - 1) 'Dynamically resize array to the same size as length of file

    FileOpen(1, FileName, OpenMode.Binary, OpenAccess.Read, OpenShare.Shared) 'Open the file for reading

    FileGet(1, arr) 'Read the file into array (just one line...)

    My.Application.DoEvents()

    FileClose(1) 'If you open a file, you MUST close it

    '********************************************************************************************************************************

    '*************Save array data to database*****************************************************************************************

    dsSF.Tables(0).Rows(0).Item(Field) = arr 'Store element in array into column(BinaryData) of the row(0).

    da.Update(dsSF) 'Update database

    '********************************************************************************************************************************

    My.Application.DoEvents()

    End Sub

     

    I then use the following code to load a directory of pdf files into the DB.

     

     

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim dir_info As New DirectoryInfo("C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All")

    Dim file_infos() As FileInfo

    Dim i As Integer = 1

    Dim file_info As FileInfo

    Dim DirSource As String = "C:\Documents and Settings\Robert Nevins\Desktop\Help Form\All\"

     

    file_infos = dir_info.GetFiles("*.PDF")

    For Each file_info In dir_info.GetFiles("*.PDF")

    Dim FileSource As String = DirSource & file_info.Name

    Dim PN As String = file_info.Name.Substring(0, 9)

    Dim Rev As String = file_info.Name.Substring(11, file_info.Name.IndexOf(".") - 11)

    StoreFile("BinaryData", FileSource, PN, Rev)

    Me.Label1.Text = i - UBound(dir_info.GetFiles("*.PDF"))

    i = i + 1

    Next

    End Sub

    Sunday, June 10, 2007 8:04 PM