开发者

Excel oledb connection to Ms-Access database lock access db

开发者 https://www.devze.com 2023-01-12 08:56 出处:网络
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

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:Glo开发者_C百科bal 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


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

in connection string


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


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>


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消