Store Byte[] in Access 2010

Question

Such a simple task: How to store a Byte[] in Access 2010? (Searching the web all day long about this.)

I have to use a "Attachment Field" in access 2010 because as far as i can see there is no other possible (varBinary, Image,..) field available.

I tried: (ConvertImageToByte returns a Byte[])

 Cmd.CommandText = "UPDATE Clubs SET Field1 = @File WHERE Name = @Name";
 OleDbParameter para = new OleDbParameter("@File", OleDbType.VarBinary);
 para.Value = ConvertImageToByte(Logo);
 Cmd.ExecuteNonQuery();

Exception: "An UPDATE or DELETE query cannot contain a multi-valued field."

I tried:

 DBEngine dbe = new DBEngine();
 Database db = dbe.OpenDatabase("database.accdb", false, false, "");
 String Command = "SELECT * FROM Clubs";
 Recordset rs = db.OpenRecordset(Command, RecordsetTypeEnum.dbOpenDynaset, 0, LockTypeEnum.dbOptimistic);
 rs.MoveFirst();
 rs.Edit();
 Recordset2 rs2 = (Recordset2)rs.Fields["Field1"].Value;
 rs2.AddNew();

 Field2 f2 = (Field2)rs2.Fields["FileData"];


 f2.LoadFromFile("file.png");
 rs2._30_Update();
 rs2.Close();

 rs._30_Update();
 rs.Close();

This works but the file is in the first row of the tabel all the time and i can´t figure out how to get the right row. If i try to add a WHERE clause to the SELECT statement ill get a " Too few parameters. Expected 2." exception.

If anyone knows a way to story my Byte[] (or a image) into the database an get it out again please let me know!

Please don´t give me links to:

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx#BM4.6

http://www.mikesdotnetting.com/Article/123/Storing-Files-and-Images-in-Access-with-ASP.NET

http://www.sitepoint.com/forums/showthread.php?t=666928

http://www.eggheadcafe.com/software/aspnet/35103540/multivalued-fields-in-access-2007-with-c-ado.aspx

http://stackoverflow.com/questions/779211/programmatically-managing-microsoft-access-attachment-typed-field-with-c-net

Thanks for your help guys.

Answer 1

You can use an OLE Object field, it is the best choice for varbinary(max) match up.

Some notes:

''Reference: Microsoft ActiveX Data Object x.x Library
Dim strSQL As String
Dim strCN As String
Dim rs As dao.Recordset
Dim mstream As ADODB.Stream


strSQL = "SELECT Pix FROM Table1"
Set rs = CurrentDb.OpenRecordset(strSQL)

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "c:\docs\project.jpg" ''FileName & FullPath

rs.AddNew
rs.Fields("Pix").Value = mstream.Read
rs.Update

rs.Close

EDIT

To copy back to disk, you can again use the Stream:

Dim strSQL As String
Dim cn As New ADODB.Connection
Dim mstream As New ADODB.Stream


strSQL = "SELECT Pix FROM Table1"
Set rs = CurrentDb.OpenRecordset(strSQL)

mstream.Type = adTypeBinary
mstream.Open
mstream.Write rs!Pix
mstream.SaveToFile "c:\docs\pixout.jpg", adSaveCreateOverWrite