Tag Archives: Access OLE Import

Updating embedded data in OLE Object fields

Do you need to update a batch of OLE Object fields, and you don’t want to have to update them individually using your application?  Access OLE Import can help you with such tasks.

We had a user that needed to update a few thousand rows in an Access table, specifically an OLE Object field that stores a zip archive.  He already had all the zip files ready, but did not want to have to update each record individually.

So in SQL Image Viewer, the existing records show up as zip archives embedded in OLE Object packages:

The user already has the new zip files he wants to update the records with.  Using Access OLE Import, he just needs to prepare an upload definition file providing the key field value (ID in this case) and the OLE Object column to update the contents for (the quotations field).

In Access OLE Import, we enter the table to update together with this data file.

And that’s it.  Access OLE Import will update the OLE Object fields with the new zip files accordingly.

Note that you can also update any of the other text or numeric fields in your tables using Access OLE Import.  For e.g. if the user had wanted to update the address field, he only needed to enter the new values in the data file e.g.

Learn more about Access OLE Import here, or download a 14-day trial now.  Access OLE Import can help you update batches of records easily, instead of having to update each record individually via your application.

See here on how you can update linked file locations using Access OLE Import.

Updating linked file locations in OLE Object fields

Have you ever needed to update the linked file locations in an OLE Object-type field in your database?  Access OLE Import can do all that for you and more.

Let’s say you have a table with an OLE Object column that contains links to a file.  In SQL Image Viewer, you may see the following:

So all the images are currently linked to files located in the F:\Customer files\ folder.  What if the files are no longer there, for e.g. when you share your database with other users?  Your users will not be able to access the images because they may not have that folder available to store the images.

Access OLE Import can help you update the details in the OLE Object field.  To do that, you just need to provide the key field value of the records to update, and their corresponding file location, in an Excel file.  So for e.g. let’s say all our images are now stored in the G:\Employee photos\ folder.  Our Excel spreadsheet will simply contain the following:

The ID column refers to the ID field in our table.  The keynumber definition tells Access OLE Import that this is an integer key value.

The photo column refers to the photo field in our table, and filecontent tells Access OLE Import we want to work with the contents of the files listed in this column.

We then tell Access OLE Import that we want to create links to the files.

And that’s all there is to it.  Access OLE Import will update the relevant records, and the links to the images are updated accordingly.

If you prefer to embed the image in the table, then select the embedded option instead.

Now Access OLE Import will embed the images directly in the table.

Learn more about Access OLE Import here, or download a 14-day trial now.  Access OLE Import can help you update batches of records easily, instead of having to update each record individually via your application.

See here for a more detailed example on how to update embedded OLE Object data.

Using Access OLE Import

Access OLE Import is a Windows application that uploads files into OLE Object-type fields in your databases.  Most of the time, we are uploading into Access databases, but it can also be  client server databases like SQL Server, PostgreSQL, MariaDB/MySQL because Access is popularly used as a front-end to these databases.

Backup, backup, backup

The first step is the most important – back up your database first.  Access OLE Import makes changes to your tables, and having a backup lets you revert to a working database if anything goes wrong.

Preparing the data file

The next step is to prepare a data file.  This is an Excel worksheet containing the data to upload.  If you only want to insert files into a table, your data file would contain a single column, where the column header defines the field name to upload into and the filecontent identifier so that Access OLE Import will upload the contents of the listed files.  E.g.

Here, we want to upload the contents of the listed files into the document field.

If we want to update existing rows, we will need to provide one or more key values e.g.

Here, we are providing the definition that documentID is an integer-type key value.

Note that you can also use Libre Office or Open Office to create the data file.  You just need to save the spreadsheet in the Excel format (xlsx or xls).

Run the Upload Wizard

Probably the easiest step.  Connect to your database, enter the name of the table you want to upload into, select the data file, upload type, object type, and you’re done.

See also:

Introducing Access OLE Import

Access OLE Import is our new product that helps you upload files into OLE Object-type fields in Access and most major database engines (SQL Server, PostgreSQL, MariaDB/MySQL etc), for use in the following scenarios:

  • you are migrating data to a database that uses OLE Object fields
    E.g. you have a new customer that require their data to be migrated to OLE Object fields in your database.  Access OLE Import can upload the files into those fields easily.
  • you need to modify the existing data then upload the changes back into the tables
    E.g. you want to extract images from your database, then resize/add watermarks, then upload these updated images.  Use SQL Image Viewer to extract the images, modify them, then use Access OLE Import to upload the files.
  • you need to update file links in the OLE Object fields
    E.g. you have existing file links that are no longer valid, and want to update those file links.  Access OLE Import can upload file links as well as embedded files.
  • you want to convert embedded content to file links in OLE Object fields
    Use Access OLE Import to easily upload file links to replace the existing embedded content.

You can read more about Access OLE Import on this page, or download a 14-day trial.

See also: