0

I have an excel workbook set up with 25 sheets in it. Each sheet has a data connection to a query in MS Access. Each access query has one or more linked tables from an InterSystems Caché DB. Here is the connection string from one of them.

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\DIR\SUBDIR\XXX\Database\CAST\CAST_CLIENT_SETTINGS.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";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;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

Background refresh is not enabled and the command type is table.

The Access DB has 25 linked ODBC tables to a Cache DB. There are 25 queries in the access db that are each utilizing one or more of the linked tables. Record locks No and Recordset type Dynaset in each.

There is a button with some vba code in the excel workbook that kicks off a refresh of all 25 sheets to bring back the newest information. This works just fine other than taking a few minutes but the issue is that it locks up 25 licenses in Cache and keeps them locked until the workbook is closed.

The company only has 50 licenses so I can't use this many. Is there any setting I'm missing that would stop this from happening? Could I change the mode, the locking mode, the recordset type, background refresh, use a pass through? Could I write something into the vba to remove these locks after it refreshes? People use this utility to track changes they make in the application corresponding to the Cache DB in relatively real time.

Before it gets totally scrapped, I was hoping someone out there would have an idea that allowed it to use only one license if possible. Thanks for your help.

Community
  • 1
  • 1
45Spartacus
  • 1
  • 1

2 Answers2

0

You have to look at the code behind that button how it is doing. I guess it fires off all 25 queries at the same time. You'd probably have to change it to refresh just one and wait until that one is finished before refreshing the next.

Nybbe
  • 394
  • 2
  • 7
  • Thanks for the answer. I loop through all sheets in the book and do this - Selection.QueryTable.Refresh BackgroundQuery:=False. Is there a way to have the process wait until 1 query is updated before moving to the next or do I need to use some kind of sleep function? – 45Spartacus Mar 11 '14 at 21:13
  • This post might help you: [link to post](http://answers.microsoft.com/en-us/office/forum/office_2007-customize/using-vba-to-update-a-spreadsheet-with-a-data/ae9e55b2-1b40-42ef-98b3-289e643c08e4) – Nybbe Mar 11 '14 at 23:05
0

I know that this is a very old post, but by default Excel keeps OLEDB/ODBC connections open (thus locking the access database in use.)

No amount of configuration via connection string will stop Excel from doing this. Contrary to expectations, setting the mode to share deny none, or read only won't get you past the trouble. However, you can modify the properties of the connection via code, to prevent this behavior using the .MaintainConnection property. There is no setting in the UI to modify this property.

Try this code:

Function unlock_conns()
Dim conn

For Each conn In ActiveWorkbook.Connections
    Select Case conn.Type
        Case xlConnectionTypeOLEDB
            conn.OLEDBConnection.MaintainConnection = False
        Case xlConnectionTypeODBC
            conn.ODBCConnection.MaintainConnection = False
    End Select
Next conn

End Function
Chris Meurer
  • 459
  • 3
  • 13