Excel oledb connection with access to database lock Ms-Access db

Question

I have an Excel spreadsheet that connects to an MS-Access 2003 database using an ole db connection. When I update the data in the spreadsheet and open the MS-access database, it says that the database is read-only. If I close the spreadsheet and then open MS-Access DB, it will open in write mode. The following is the connection string used in an 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 

Answer 1

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

in the connection string

Answer 2

Have you verified that the Excel user has full permissions in the folder containing the Access file?

Answer 3

Excel background refresh option is enabled. Disable it or use the query studio to create a query as a 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>