¤ Hi: Im trying to store a byte array on an access database, I selected "Ole
¤ object" as the column type, but it seems not to be the correct way.. Is
¤ there any way to store long byte arrays in a table on an access database?
¤
Yes. You should use the OLE Object field to do this. Just remember it is stored as binary data but
without OLE headers:
Sub WriteBlobToAccess()
Dim SourceFilePath As String
SourceFilePath = "e:\My Documents\Greenstone.bmp"
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("UPDATE Table1 SET OLEField=? WHERE [record id] = 1",
AccessConnection)
Dim FileStreamObject As New System.IO.FileStream(SourceFilePath, IO.FileMode.Open,
IO.FileAccess.Read)
Dim PictureByteArray(CType(FileStreamObject.Length() - 1, Integer)) As Byte
FileStreamObject.Read(PictureByteArray, 0, PictureByteArray.Length)
FileStreamObject.Close()
Dim QueryParameter As New OleDbParameter("@Picture", OleDbType.LongVarBinary,
PictureByteArray.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current,
PictureByteArray)
AccessCommand.Parameters.Add(QueryParameter)
AccessConnection.Open()
AccessCommand.ExecuteNonQuery()
AccessConnection.Close()
End Sub
Sub ReadBlobFromAccess()
Dim DestFilePath As String
Dim RecordId As String
Dim RetVal As Long
Dim FieldLen As Int32
DestFilePath = "e:\My Documents\GreenstoneFromAccess.bmp"
Dim PictureCol As Integer = 0 ' the column # of the BLOB field
Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb")
Dim AccessCommand As New OleDbCommand("SELECT [record ID], OLEField FROM Table1 WHERE
[record id] = 1", AccessConnection)
AccessConnection.Open()
Dim AccessDataReader As OleDbDataReader =
AccessCommand.ExecuteReader(CommandBehavior.SequentialAccess)
AccessDataReader.Read()
FieldLen = AccessDataReader.Item(1).Length
Dim PictureByteArray(FieldLen - 1) As Byte
Dim startIndex As Integer = 0
RetVal = AccessDataReader.GetBytes(1, startIndex, PictureByteArray, 0,
PictureByteArray.Length)
AccessDataReader.Close()
AccessConnection.Close()
Dim FileStreamObject As New System.IO.FileStream(DestFilePath, IO.FileMode.Create,
IO.FileAccess.Write)
FileStreamObject.Write(PictureByteArray, 0, PictureByteArray.Length)
FileStreamObject.Close()
End Sub
Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
