MS Access file size limit when embedding OLE Object

An Access file really isn't very handy for this.

Most recommendations suggest to save such large objects or documents in discrete files and, in the database, only store the links to these.

Take a look at my article :Defeating-the-device-independent-bitmap-dib-format

Its for images but the code that reads the image as file should handle your file.

EDIT: just tried it with a 68 Mb image and it was read just fine

As Gustav mentioned Access is not at its best for handling large size files

Your Options are 2 :

For Local storing (your files are just for 1 user) SQLite is best

If its for Multi User storage then Firebird is probably the best

All SQL Engines are capable of storing files but the 2 above are best suited.

Actually, there is another option.

Rather than using the built-in functions, you can store the BLOB data yourself in JET with AppendChunk() and GetChunck().  The only issue is that when you want to view it, you need to write it out to disk before you can do anything with it.

So a better strategy is to store the doc on disk and just link to it rather than bringing it into the DB at all.    No reason to either with no real security.   A SQL back end would be a different story.

Jim.

Actually everything is going into a MySQL backend, longblob field, and the my.ini file sets the max_allowed_packet=1G for MySQL, does that open up any other options?

John, I downloaded and tried to use the application as is with a word document, but got an error message.  I looked through the code, and while I can't say I fully comprehend it, I'm not sure this is going to work as an OLE replacement for other file types that there would need to be some mechanism to open the application that can read out the data, since it might be Word, Excel, a .pdf, etc.  If it can, I would need someone's help with reconstructing this code to work in that way as I'm not seeing anything familiar enough to work with myself.

I will make it simple for you David :

A table with a field OLE Object lets name it FileData

On the click event of a form

Dim rst As DAO.Recordset
Dim filePath as String
filePath = "C:\Temp\A_Big_Doc.doc"
Set rst = CurrentDb.OpenRecordset("TableFiles", dbOpenDynaset)
With rst
.AddNew
.Fields("FileData")=FileToBlob(filePath)
.Update
rst.Close
Set rst = Nothing
End With

Select allOpen in new window

Function FileToBlob(FilePath As String) As Byte()
Dim B() As Byte
    Open FilePath For Binary As #1
    ReDim B(LOF(1) - 1)
    Get #1, , B
    Close
FileToBlob =B
End Function

Select allOpen in new window

EDIT : Tested and Working ...see sample

David.accdb

I changed the path to an existing file and the code errors out on line 4 below:

Function FileToBlob(FilePath As String) As Byte()
Dim B() As Byte
    Open FilePath For Binary As #1
    ReDim B(LOF(1) - 1)
    Get #1, , B
    Close
FileToBlob = B
End Function

Select allOpen in new window

send your database...have you checked the filepath ?

That was it, path was to a .doc file but it was actually a .docx file.

OK, that loads the file in the table, but that is only be useful if I can now read it back out using the application that created it.

I'll have to set up a test of that to see whether the built in OLE functionality reads it back out normally, or if the file size restriction gets applied to the file when a read attempt is made.

I'll let you know the results when I get them.

Probably you will need to reverse the process and write to a folder of your choice

So far it looks like in principle the only way to do this is to take the file out of the database, write it to disc, then the operating system can decide how to open it.  Then the UI is going to have to put it back into the database and delete the temporary file so that we don't eventually eat up the users hard drive.  Since none of this is needed for files below the OLE limitation, now I'm wondering if there is a way to read the file size as part of the file selection to let the UI decide which method is needed.  Although I believe the OLE wrapper needs to read it into the database, so the information about what application needs to read it back out is there.  I don't think OLE can read it out, if OLE didn't put it in, so OLE will have to go altogether, unless having already been embedded via OLE, reading out without OLE becomes a problem.

Thanks for everyone's help, now I have to see if I can make this work with enough reliability for a document control system.