Pulling files out of microsoft access

"Please leave a message at the beep, we will get back to you when your support contract expires."

Moderators: phlip, Moderators General, Prelates

kiklion
Posts: 511
Joined: Fri Mar 14, 2008 5:02 am UTC

Pulling files out of microsoft access

Postby kiklion » Wed Jan 02, 2013 3:44 pm UTC

I inherited a microsoft access database at my job. The previous developer created a process where images were to be imported into the database as an OLEObject. Now my client needs to get those images out of the database. I have tried OLEToDisk from here, http://www.lebans.com/oletodisk.htm but the object is stored as an OT_Linked type and so it doesn't attempt to export them. I was under the impression that an OT_Linked type would need to exist on disk somewhere, yet all copies of it have been deleted and the images still work when that field from the table is referenced within a microsoft access form. I contacted technet as my company has a support contract with them and they said they would look into it but that they would probably be unable to assist.

Does anyone know of a way to export images of unknown file types (probably .bmp or .jpg but not definitely) from an OLEObject field in an access database?

cphite
Posts: 1104
Joined: Wed Mar 30, 2011 5:27 pm UTC

Re: Pulling files out of microsoft access

Postby cphite » Wed Jan 02, 2013 8:02 pm UTC

kiklion wrote:I inherited a microsoft access database at my job. The previous developer created a process where images were to be imported into the database as an OLEObject. Now my client needs to get those images out of the database. I have tried OLEToDisk from here, http://www.lebans.com/oletodisk.htm but the object is stored as an OT_Linked type and so it doesn't attempt to export them. I was under the impression that an OT_Linked type would need to exist on disk somewhere, yet all copies of it have been deleted and the images still work when that field from the table is referenced within a microsoft access form. I contacted technet as my company has a support contract with them and they said they would look into it but that they would probably be unable to assist.

Does anyone know of a way to export images of unknown file types (probably .bmp or .jpg but not definitely) from an OLEObject field in an access database?


The images are stored as BLOB data in Access. I don't recall any built-in feature of Access that will allow you to export these in bulk; you'll probably have to do something like this: http://bytes.com/topic/access/answers/680306-how-export-access-blobs-images-files

Of course, you'll need to edit their code to replace the names they are using with your own.

As an aside, if you have access to SQL Server with Integration Services, there is a far easier way... just build an SSIS package where the source is your Access database (OLE DB or ODBC) and the destination is "Export Column" - it'll do exactly what you are asking. One caveat is that you'll need to add a column to your table in Access that shows the file path for each image.

kiklion
Posts: 511
Joined: Fri Mar 14, 2008 5:02 am UTC

Re: Pulling files out of microsoft access

Postby kiklion » Wed Jan 02, 2013 9:31 pm UTC

I will try both of those, but first,

http://bytes.com/topic/access/answers/6 ... ages-files

I'm pretty not sure that content of an OLE field is the same as that of file you've inserted to the field, unless you've inserted it with AppendChunk method.
Common sence. Access needs to store OLE wrapper together with raw data to open a record properly.


This method seems to simply export the bytes stored within the field to a file. I have done this before, but I will try again as previously I used a third party tool not my own code so it may have had some other failings. Yet, since access must store ole wrapper data with the data of the file stored in the OLE field, the file will not be recognized as it's original type. For instance, if I stored a BMP within the OLE field, then exported it in this way, I am fairly sure it will export a file of bytes OLE_Wrapper_Header + FileData + OLE_Wrapper_Footer (if there is a footer).

My searches have not found any information regarding OLE_Wrapper byte size. One person on another site thought it was 25 bytes every time, but I cannot find that post at the moment. I created an application to iterate through all files in a directory (which I had exported from the OLE field) and strip the first 25 bytes then re-write it and still none of them were recognized by photoshop as a BMP, or JPG.

As an aside, if you have access to SQL Server with Integration Services, there is a far easier way... just build an SSIS package where the source is your Access database (OLE DB or ODBC) and the destination is "Export Column" - it'll do exactly what you are asking. One caveat is that you'll need to add a column to your table in Access that shows the file path for each image.


This sounds more likely as SQL may be able to ignore the OLE wrapper. What do you mean that I will need to add a column to the access table that shows the file path for each image? That is the file path that I want the image to be exported to?

cphite
Posts: 1104
Joined: Wed Mar 30, 2011 5:27 pm UTC

Re: Pulling files out of microsoft access

Postby cphite » Wed Jan 02, 2013 9:38 pm UTC

kiklion wrote:This sounds more likely as SQL may be able to ignore the OLE wrapper. What do you mean that I will need to add a column to the access table that shows the file path for each image? That is the file path that I want the image to be exported to?


Yes, the path and name that you want the image to be stored as... you may be able to just make a view in your Access DB that has a column that is the image name (or some other identifier) concatenated onto a path.

However, if you're finding that the files you export are not being read properly with your first method, there is no guarantee that they'll work via SQL either. I don't know for sure that SQL will ignore the OLE wrapper; but the overall process is certainly easier to setup (at least for me).

monty241070
Posts: 1
Joined: Sat Feb 06, 2016 1:48 pm UTC

Re: Pulling files out of microsoft access

Postby monty241070 » Sat Feb 06, 2016 1:49 pm UTC

You can export files stored as OLE to a folder using the free Invantive Query Tool (I'm involved with it) as follows:

Code: Select all

select olecolumn
from table
   
local export documents in olecolumn to "<DIRECTORY>" filename (automatic|column <COLUMN-NAME>) actions onerrorcontinue, extractole1


Please note that there are various OLE flavours around. This only works for OLE1 format. When exporting more than a few GB of OLE files in one batch, please ensure sufficient physical memory present.


Return to “The Help Desk”

Who is online

Users browsing this forum: No registered users and 7 guests