0

I have an Access DB on a network folder. I have an Excel file that connects to the DB using vba code. Problem is whenever I'm opening the DB through Access, other users, who have read-only permission to the folder, are getting "data link properties" dialog and can't go on.

How can I solve it?

here's the vba code in excel:

"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;
Password="""";
User ID=Admin;
Data Source=\\file.accdb;
Persist Security Info=False;
Mode=Read;
Extended Properties="""";
Jet OLEDB:System database="""";
Jet OLEDB:Registry Path="""";
Jet OLEDB:Database Password="""";
Jet OLEDB:Engine Type=6;
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;
Jet OLEDB:Support Complex Data=False;
Jet OLEDB:Bypass UserInfo Validation=False;
Jet OLEDB:Limited DB Caching=False;
Jet OLEDB:Bypass ChoiceField Validation=False" _

Thanks, Udi

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
udigold
  • 161
  • 7
  • 16
  • Possible duplicate of [Excel Data Connection Locks Access DB, Prevents Second Connection's Refresh](https://stackoverflow.com/questions/19139202/excel-data-connection-locks-access-db-prevents-second-connections-refresh) – Foxfire And Burns And Burns Feb 05 '19 at 09:51
  • not a duplicate. I'm removing the connections at the end of the code. It only happens when a user with a write permission is opening the DB and a user with a read-only tries to access it as well at the same time. If the user with write permission doesn't use it, than the read-only doesn't have any problem. – udigold Feb 05 '19 at 10:06
  • @udigold That's not a VBA code that's just a string. Please see how to build a [mcve]. – Pᴇʜ Feb 05 '19 at 12:10
  • @Pᴇʜ That's the connection string inside a VBA code and the reason I've put just that is to not overload the post, there's nothing wrong with my VBA code, and tha same problems occurs when I try to connect using the connection wizard in excel. I'm trying to figure out why a read-only user can't open the DB when a user with write permission is already in it. – udigold Feb 05 '19 at 12:50
  • Well, but if it occurs also with the connection wizard then this is an important information that you didn't tell us. Because then it is not a programming question in first place, because it has nothing to do with your code and it is more a *general computing hardware and software* question and you might have more luck asking it at https://dba.stackexchange.com or https://superuser.com because Stack Overflow is for programming related questions only, which means you might have asked the wrong experts. – Pᴇʜ Feb 05 '19 at 13:12

0 Answers0