Month: July 2017

Excel, Microsoft Technologies, Power BI, SSIS

Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

Meagan Longoria

I recently got a new laptop and had to go through the ritual of reinstalling all my programs and drivers. I sometimes work with SSIS locally to import data from Excel and occasionally do demos with Power BI where I read from an Access database so I needed to install the ACE OLE DB provider.  If you try to do either of those things without the ACE OLE DB provider you will get a message like the one below.

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.

I needed both the 32-bit and the 64-bit adapters to cover my various use cases. I started by installing the 64-bit Microsoft Access Database Engine 2010 Redistributable.  If you have ever attempted this, you know there is a secret. You cannot install the 2010 Access database engine with a different processing architecture (bittedness) from your Office install (or install both versions of the 2010 Access database engine) without a workaround.  If you try, you will get an error message.

ACE engine bittedness error

There are two options for getting the other version installed.

Option A: Install the 2007 Office System Driver: Data Connectivity Components

The 2007 Access database engine can read the same format, but didn’t have a 64-bit install. It doesn’t have the check to see what other products are on your machine and can co-exist peacefully with the 64-bit 2010 Access engine.

Option B: Install from the Command Line and Use the Passive Switch

  1. Open the command prompt.
  2. Type the file path and file name for your Access Database Engine install file.
  3. Follow it with a space and then “/passive” and then hit Enter.
  4. Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths

Note: A helpful commenter has informed me that for Access 2016 the switch is /quiet rather than /passive.

ACE install cmd

I wrote up a document for this a while back for a client, but I can never remember where I put it. So I’m posting this on my blog to make this info easier to find for me as well as you.

Microsoft Flow, Microsoft Power Apps, Microsoft Technologies

Updating a SharePoint List Item With Flow When You Don’t Have The ID

Meagan Longoria

I recently worked on a project that used Flow to update a SharePoint list each time an item was updated in the Power Apps Common Data Service. In order to update a SharePoint list item, you must have the unique ID, even if there are other fields that are unique to the item. I spent a while searching through forums to find out how to accomplish this, so I’m documenting it here in the hopes it helps someone else.

We know we need a starting point from which the updates originate. For me, that was when a record is updated in an entity in the Common Data Service, but this could be anything (an email arriving, an update occurring in another SharePoint list, etc.). This source doesn’t contain the unique ID for the list item, since that ID is unique to SharePoint. In my example below I have a CDS entity called Tactic. Tactic has three fields that are submitted to the SharePoint list upon creation: Tactic Name, ID, and Tactic ID. My SharePoint list is called Meagan Test Flow List and contains the corresponding fields Title, TacticID, and RecordID.

The next part is the trick that makes this work. You’ll need to add a new action and choose SharePoint – Get items. Be sure that you choose SharePoint Get Items (items is plural). There is another choice called SharePoint – Get Item, which is not the same and will not work for this purpose. In the Get Items action, populate the site address and list name. Then expand the advanced options and add a filter. This filter should use a field in your source that uniquely identifies a record. For my example, the ID field in the CDS entity has unique values for each record. My filter condition is RecordID eq and then the CDS field. RecordID is an integer. If the value on which you are filtering is a string, you’ll want to put single quotes around it.

Once you have completed your filter condition, you can add the final action: SharePoint – Update Item. Flow will automatically add a for each container as you begin to fill in the required fields – this is fine. Even though you are only updating a single record, the Get Items action could potentially get multiple records. As long as you are using a field that uniquely identifies the single record you want to change, this will work.

You should end up with something resembling the image below. 

You should populate the ID field with the ID from the Get Items result. You want to make sure you have mapped the fields from your source to any field that should be updated. The only field I was updating in my list was Title. The TacticID and RecordID do not change after creation.

That’s all there is to it. May the Flow be with you.