Excel OLE DB Connector -- File locking

[Former Member]

Hi

We have deployed xMII in production where it uses OLE DB connector for connecting to Excel spreadsheets.

We are observing the behaviour that though the Mode is set as "Read" in UDS connector, in some cases, users are unable to open the file being used by UDS connector in a read-write mode.

They are forced to open in read-only mode because they get the message "File already in use". This behaviour is not consistent either.

Some files open as read-write and some open as only read-only though they have same UDS configuration.

I verified the security setting around the file. The security on the file is being set to read/write for Everyone in the test environment where we observe this issue too.

This is affecting production environment since users are unable to work on the spreadsheet being used by xMII.

Any help would be appreciated

Thanks

Deepa

[Former Member]

Hi,

Can someone please tell me how to configure OLE DB connector for MS-Access ?

[Former Member]

I agree with Jeremy, Excel just isn't a good way to store data, I've seen several companies try to make this work, and it just doesn't. Ok, off my soapbox. If you REALLY need to view the content of excel files on the web, your best bet is probably a 3rd party software like BadBlue. But by far, your best solution is a database. Of course a lot of people love the excel interface, and don't want to have to replicate the interface for a database front end (even using Access) So, here are a couple of approaches to try if you wish to keep your data entry in excel and push the data to a database.

Microsoft DTS transaction on a schedule (or you can write an script to do it on demand) that imports your excel into MS SQL server;

Write some VBA code in excel that exports the data to a database of your choosing (can even use MySQL if cost is an issue)

I'm sure there are many other ways (probably even better than my suggestions) than these two, but thats my suggestion.

Edited by: Doug Holtke on Apr 4, 2008 9:26 PM

[Former Member]

I am pretty sure this is normal for a shared Excel file. First come first serve.

[Former Member]

But the issue is that here, the first one opens in a read-only mode (atleast that is what Mode setting in the OLE DB connector is for).

The data servers connect to excel spreadsheet at the time the data servers are started. (I can see UDS log files updated)

Also, when we connect using UDS as read-only, I would think it would let any one else opening it as read-write mode . But that is not the case

Thanks

[jcgood25]

Just because the OleDB connector allows you to bridge ODBC and connect to Microsoft Excel or Access, doesn't mean that you should. Use a database if you want reliable and concurrent access to the information.