2

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

Programmatically managing Microsoft Access Attachment-typed field with .NET

Thanks for your help guys.

Community
  • 1
  • 1
Gpx
  • 653
  • 9
  • 17
  • If all else fails, you could always encode the data yourself and store it as a string. You could parse your bytes into Hex, delimit them for storage, and then pass them into a method to undelimit them upoin retreval. It would be a very hacked, bloated solution, but it would work. On second thought, don't do this. =) – George Johnston Aug 03 '10 at 16:15
  • Thanks JonH, but there are a couple of reasons for me to have just one file with all information, so e.g. one has to send just this single file to other people. – Gpx Aug 03 '10 at 16:19
  • Love your comment George! Thought about that, but as you say - won´t do it. – Gpx Aug 03 '10 at 16:20
  • I wouldn't discount George's comment. Use a memo field and treat it like a BLOB. Within VBA, you have GetChunk and WriteChunk for working with the data, and from what I can tell, it's the recommended way to store binary data in Jet/ACE files. I wouldn't know as I avoid the issue and store the data in the file system. The last time I did otherwise I regretted it (c. 1998). – David-W-Fenton Aug 03 '10 at 19:59
  • if you want to mail one file, you can .zip the db and any other dependent files together – Beth Aug 04 '10 at 17:17

1 Answers1

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
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • OLE fields have a wrapper that is arbitrary for each kind of OLE server and makes it somewhat difficult to pull data back out. I would recommend against using them. – David-W-Fenton Aug 03 '10 at 19:58
  • Pretty nearly all the references I have found for storing binary data mention the OLE Object type. Val Mazur (MVP) points to a link that mentions the OLE object type and Microsoft itself also suggests this type. – Fionnuala Aug 03 '10 at 22:21
  • That all the references point to it does not mean it's the best way to do it. Jet/ACE OLE fields were designed to be manipulated through the Access UI, and programmatic support for doing so is very, very poor. It's particularly difficult to pull data out of them if the files are of mixed type (say, Word documents and Excel spreadsheets, even multiple versions of Word can be problematic since the OLE wrappers are different). Been there, done that, won't do it again. – David-W-Fenton Aug 04 '10 at 18:36