Readonly connection string to an access database

Question

I'm trying to connect to an Access database file using a System.Data.OleDb.OleDbConnection. I need to connect in readonly mode because another application uses it at the same time. I can connect to the database in read/write no problem but can't seem to find anywhere the correct string for readonly.

I've tried:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Mode=Read

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Extended Properties="ReadOnly=true;"

Thanks.

EDIT:

(I should have put more information in the original question.)

I could connect successfully to the access database when it was on the local machine, but when I tried connecting to the access database on a remote machine with the connection string

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Mode=Read

I would get the following error:

System.Data.OleDb.OleDbException (0x80004005): The Microsoft Office Access database engine cannot open or write to the file '{0}'. It is already opened exclusively by another user, or you need permission to view and write its data.

My application is running in a windows service under the local system account.

Try setting Mode to "adModeRead" (without quotes). [See MSDN Reference](http://msdn.microsoft.com/en-us/library/ms676693(v=vs.85).aspx). (Not making this an answer as I cannot validate if it will work or not, just going off MSDN data.) — Brad Butner, May 09 '11 at 22:09
I tried setting Mode to 'adModeRead' but it throws a 'System.Data.OleDb.OleDbException (0x80040E73): Format of the initialization string does not conform to the OLE DB specification.' Thanks for the suggestion. — Smiffy, May 26 '11 at 12:22

score 3 · Accepted Answer · answered May 16 '11 at 20:15

3

I think that has to be handled either by user permissions that the DB admin would control, or with different cursor types for your recordsets, which you would control. I don't think the connection string specifies access mode, it just gets you there. ;)

answered May 16 '11 at 20:15

Brett Rossier

3,420
3
27
36

1

You are correct. It is a permissions issue. If I'd put a bit more information with the original question it would have been more obvious I think. After I ran my windows service with a user that had elevated rights (rights to read the remote access database), it started working with the connection string: `Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;Mode=Read` It just didn't click that my service wasn't running with the same rights that my user had. – Smiffy May 26 '11 at 12:39

score 2 · Answer 2 · edited Oct 11 '12 at 06:10

The real problem is that Excel leaves the connection open until the file is closed.

In Excel 2007+, the MaintainConnection setting is set to true by default. You need to go into the vb editor and use code to turn it to false. I haven't seen a way to do this through the visual interface. Even if you set the connection string to readonly, it will lock an access database (from my experience).

For a pivottable connection:

Sheets("sheet1").PivotTables("pivottable1").PivotCache.MaintainConnection = False

For QueryTable:

Range("A2").Select
Selection.ListObject.QueryTable.MaintainConnection = False
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

By setting it to false, the table will connect, run the command, then disconnect, releasing the lock.

Readonly connection string to an access database

2 Answers2

Linked