Excel connection to access- connection mode keeps changing

I have several excel spreadsheets, that link to an access database to retrieve data. This works great, except that excel keeps automatically changing the connection string, specifically the mode, which then locks access to the database.

Here's the connection string (with the file path simplified)
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=W:\Reports\ReportingDB.accdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";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

Open in new window

Whenever anyone (including myself) refreshes the datatable, excel changes the mode part of the connection string from mode=Read to mode=Share Deny Write which seems to be the default, and also locks the database from editing the entire time the excel file is open, whereas "Read" does not.

I've tried changing the connectionstring using VBA, which does change, but as soon as the connection refreshes, excel changes it back. Here's an example of how I changed it:
Dim objconnection As WorkbookConnection

For Each objconnection In ThisWorkbook.Connections
        'Set Connection string mode=read
        objconnection.OLEDBConnection.Connection = connstring
        objconnection.Refresh
        'Connection string changed automatically to mode=share deny write
Next

Open in new window


Any suggestions? This is becoming a big problem, as I have I have scheduled tasks to run queries at specific times of day to automatically update data, and if only 1 person has a single report open, then the updates won't occur.
LVL 2
Andy CownieImplementation SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Everyone needs to have their OWN copy of the workbook, at least any sheets with linked data.  This is happening because more than one person has the same workbook open at the same time.
Andy CownieImplementation SpecialistAuthor Commented:
Only one person has each workbook open at a time, as they are reports made specifically for each manager.

I've found the answer, by changing the property "MaintainConnection" to False. It appears that this can only be changed in code, and that it defaults to True. With it set to false, the connection refreshes and then closes the connection, and there are no more locked database issues.

New Code:
Dim objconnection As WorkbookConnection
For Each objconnection In ThisWorkbook.Connections
        objconnection.OLEDBConnection.MaintainConnection = False
        objconnection.Refresh
Next

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
thanks for sharing your solution ~
Andy CownieImplementation SpecialistAuthor Commented:
No user submitted answer, i found the solution myself.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.