Excel oledb connection to Ms-Access database lock access db

6
votes

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
3
excel
Do you how to accept answers? You should accept right answers, before asking new ones. - hgulyan
I didn't get any valid answer that solved problem - Kannan Karmegam
@hgulyan I would be glad of a link to the previous question. - Fionnuala
@Remou He has asked 7 questions in SO with no accepted answers. Is that ok for you? - hgulyan
@hgulyan 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. - Fionnuala

3 Answers

19
votes

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

in connection string

1
votes

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

1
votes

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>