Microsoft.ACE.OLEDB.12.0 reads saved data from file instead of live data from memory

In my previous blog post, I have demonstrated how to use Microsoft.ACE.OLEDB.12.0 to join two worksheets. Today, I am going to reproduce an issue where the data provider reads saved data from file instead of live data from memory and show you a way to work around it.

Let’s reuse the Excel file we’ve created in the previous blog post. I will start by going through the normal behaviour.

1. Open the workbook, if there is data already in Sheet3, clear the content first.

2. Run macro to populate Sheet3.

3. You should see something like the screen shot below.

4. Let’s change LastName in Sheet2 from Bao to Bad and rerun the macro.
Everything is so far so good. The data provider is reading live data as expected.

5. Now we will do something to change the behaviour. Save the workbook, and set the file attribute to Read-only.

6. Open the workbook again and change LastName in Sheet2 back to Bao.

7. Clear Sheet3 and run macro.
Depending on your environment, you may or may not get the prompt. If you are prompted with the above dialog box, change Open Mode to DB_MODE_READ.

8. You should be able to see something like the screen shot below.
We have successfully reproduced the issue. The data provider failed to read the most recent change from Sheet2. The read-only property on the file has a profound impact on the behaviour of the data provider.

The observed behaviour sort of makes sense as the data source was not allowed to be modified. However, if left unhandled, unsuspecting users could be presented with misleading results.

One way of working around the issue is to save the current workbook to a temp folder and make the data provider read from the temp file. Let me show you how.

1. Add the additional code to save to a temp file and read from it:
a) Construct the temp file name.
b) Save the workbook first.
c) Read from the temp file.

2. Now you should be able to change the data in either Sheet1 and Sheet2 and display the newly changed values in Sheet3 using the macro.