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.
