Excel oledb connection to Ms-Access database lock access db

Question

I have an excel spreadsheet that connects to MS-Access 2003 database using ole db connection. When I refresh the data in spreadsheet and open my ms-access database it says database is read-only. If I close the spreadsheet and then open MS-Access DB then it opens in Write mode. The following is the connection string used in Excel spreadsheet.

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;
Data Source=D:\Dev\Reports.mdb;Mode=Share Deny Write;Extended Properties="";
Jet OLEDB:System database="";Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False

Spud

Do you how to accept answers? You should accept right answers, before asking new ones. – Weinberger

I didn't get any valid answer that solved problem – Spud

@Weinberger I would be glad of a link to the previous question. – Loblolly

@Remou He has asked 7 questions in SO with no accepted answers. Is that ok for you? – Weinberger

@Weinberger I just wanted to check that I was not giving the same answer as you gave. @Kannabiran does not seem to be working in the spirit of SO. – Loblolly

@Remou, That's why I tried to figure out if he knows the rules. He should know why mostly so's doesn't answer to this kind of questions. – Weinberger

@Remou, @Weinberger - could you please tell me the rules of SO? I have posted some questions and I didnt get a suitable answer... So what should I do. Do I need to mark the answer as "not useful" – Spud

Answer 1

Change "Mode=Share Deny Write" to "Mode=Read"

in connection string

Spud

For a list of all modes msdn.microsoft.com/en-us/library/ee210191 – Apocrine

Answer 2

Have you checked that the Excel user has full permissions on the folder containing the Access file?

Loblolly

I have found the solution... we need to set mode=read; in the connection string... that solved the problem – Spud

Answer 3

Excel's background refresh option is enabled. Disable it, or use the query studio to design the query as DBQ.

<xml id=msodc><odc:OfficeDataConnection xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
  <odc:Connection odc:Type="ODBC">
    <odc:ConnectionString>
    DBQ=<database path and filename>;DefaultDir=<database path>;
    Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;
    FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;
    ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;
    </odc:ConnectionString>
  <odc:CommandText>SELECT ... FROM ... WHERE ... </odc:CommandText>
  </odc:Connection>
</odc:OfficeDataConnection></xml>

Tycho

Answer 4

For those who are using older versions of Excel (like me with Excel 2003), which don't have the GUI connection string option:

First save the query as .dqy, then open the saved file with your beloved text editor. Add the Mode key alongside the others (the line above the actual SQL query string, and don't forget the ;)

You have to use the numerical value instead of the string value:

Numerical	String
Mode=0x1	Mode=Read
Mode=0x2	Mode=Write
Mode=0x3	Mode=ReadWrite
Mode=0x4	Mode=Share Deny Read
Mode=0x8	Mode=Share Deny Write
Mode=0xC	Mode=Share Exclusive
Mode=0x10	Mode=Share Deny None

Credit to @Slai who provided the link to the documentation of the numeric values.

Deidredeific