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

Do you how to accept answers? You should accept right answers, before asking new ones. — hgulyan
– hgulyan, Commented Aug 17, 2010 at 7:19
@hgulyan I would be glad of a link to the previous question. — Fionnuala
– Fionnuala, Commented Aug 18, 2010 at 11:45
@Remou He has asked 7 questions in SO with no accepted answers. Is that ok for you? — hgulyan
– hgulyan, Commented Aug 18, 2010 at 11:55
@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
– Fionnuala, Commented Aug 18, 2010 at 12:07

Curtis · Accepted Answer · 2012-01-20 16:52:14Z

19

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

in connection string

edited Jan 20, 2012 at 16:52

Curtis

104k69 gold badges280 silver badges359 bronze badges

answered Aug 19, 2010 at 4:53

Kannan Karmegam

6694 gold badges14 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Slai Over a year ago

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

Fionnuala · Accepted Answer · 2010-08-18 11:47:00Z

1

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

answered Aug 18, 2010 at 11:47

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

1 Comment

Kannan Karmegam Over a year ago

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

Chris Meurer · Accepted Answer · 2015-07-15 14:56:53Z

1

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>

edited Jul 15, 2015 at 14:56

answered Jul 15, 2015 at 14:42

Chris Meurer

4703 silver badges13 bronze badges

Comments

NRVulture · Accepted Answer · 2022-03-15 18:19:04Z

0

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.

answered Mar 15, 2022 at 18:19

NRVulture

304 bronze badges

Collectives™ on Stack Overflow

Excel oledb connection to Ms-Access database lock access db

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related