Win32.client, DAO.DBEngine and exceeding the file sharing count lock

**Tim Golden** · Jul 2 '08, 02:35 PM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

Iain King wrote:

Hi. I'm using the win32 module to access an Access database, but I'm
running into the File Sharing lock count as in http://support.microsoft.com/kb/815281
The solution I'd like to use is the one where you can temporarily
override the setting using (if we were in VB):
>
DAO.DBEngine.Se tOption dbmaxlocksperfi le,15000

Really hurried answer:

<code>
import win32com.client

dao = win32com.client .gencache.Ensur eDispatch ("DAO.DBEngine. 36")
dao.SetOption (Option=win32co m.client.consta nts.dbMaxLocksP erFile, Value=15000)

</code>
TJG

**Iain King** · Jul 2 '08, 02:55 PM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

On Jul 2, 3:29 pm, Tim Golden <m...@timgolden .me.ukwrote:

Iain King wrote:

Hi. I'm using the win32 module to access an Access database, but I'm
running into the File Sharing lock count as inhttp://support.microso ft.com/kb/815281
The solution I'd like to use is the one where you can temporarily
override the setting using (if we were in VB):

>

DAO.DBEngine.Se tOption dbmaxlocksperfi le,15000

>
Really hurried answer:
>
<code>
import win32com.client
>
dao = win32com.client .gencache.Ensur eDispatch ("DAO.DBEngine. 36")
dao.SetOption (Option=win32co m.client.consta nts.dbMaxLocksP erFile, Value=15000)
>
</code>
TJG

Thanks. I found this: http://blogs.msdn.com/michkap/archiv...3/3849288.aspx
which outlines some difference between DAO and ADO, including:
"Capability to set and change Jet options without making registry
changes (works in DAO through DBEngine.GetOpt ion and
DBEngine.SetOpt ion, fails in ADO, which has no such analogue)."

Now, I'm pretty sure I tried to use DAO before and failed to get it to
work, but maybe you could look at my code and suggest the DAO
equivalent?

---

self._connectio n = win32com.client .Dispatch(r'ADO DB.Connection')
self._DSN = 'PROVIDER=Micro soft.Jet.OLEDB. 4.0;DATA SOURCE='+dbfile +';'

self._connectio n.Open(self._DS N)
rs = win32com.client .Dispatch(r'ADO DB.Recordset')
query = 'SELECT '+field+' FROM '+self.__TABLE
rs.Open(query, self._connectio n, 1, 3)
while not rs.EOF:
v = function(rs.Fie lds.Item(0).Val ue)
if v != RETAIN_VALUE:
rs.Fields.Item( 0).Value = v
rs.MoveNext()
rs.Close()

---

aTdHvAaNnKcSe,
Iain

**Tim Golden** · Jul 2 '08, 04:25 PM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

Iain King wrote:

On Jul 2, 3:29 pm, Tim Golden <m...@timgolden .me.ukwrote:

>Iain King wrote:

>>Hi. I'm using the win32 module to access an Access database, but I'm
>>running into the File Sharing lock count as inhttp://support.microso ft.com/kb/815281
>>The solution I'd like to use is the one where you can temporarily
>>override the setting using (if we were in VB):
>>DAO.DBEngine. SetOption dbmaxlocksperfi le,15000

>Really hurried answer:
>>
><code>
>import win32com.client
>>
>dao = win32com.client .gencache.Ensur eDispatch ("DAO.DBEngine. 36")
>dao.SetOptio n (Option=win32co m.client.consta nts.dbMaxLocksP erFile, Value=15000)
>>
></code>
>TJG

>
Thanks. I found this: http://blogs.msdn.com/michkap/archiv...3/3849288.aspx
which outlines some difference between DAO and ADO, including:
"Capability to set and change Jet options without making registry
changes (works in DAO through DBEngine.GetOpt ion and
DBEngine.SetOpt ion, fails in ADO, which has no such analogue)."
>
Now, I'm pretty sure I tried to use DAO before and failed to get it to
work, but maybe you could look at my code and suggest the DAO
equivalent?

I'm afraid I don't have the relevant experience myself. I'm
sure I could muddle through a few web pages and work
it out (but I'm sure you could, too!)

Sorry

TJG

aTdHvAaNnKcSe,

PS - Like the .sig

**M.-A. Lemburg** · Jul 2 '08, 05:35 PM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

On 2008-07-02 16:54, Iain King wrote:

On Jul 2, 3:29 pm, Tim Golden <m...@timgolden .me.ukwrote:

>Iain King wrote:

>>Hi. I'm using the win32 module to access an Access database, but I'm
>>running into the File Sharing lock count as inhttp://support.microso ft.com/kb/815281
>>The solution I'd like to use is the one where you can temporarily
>>override the setting using (if we were in VB):
>>DAO.DBEngine. SetOption dbmaxlocksperfi le,15000

>Really hurried answer:
>>
><code>
>import win32com.client
>>
>dao = win32com.client .gencache.Ensur eDispatch ("DAO.DBEngine. 36")
>dao.SetOptio n (Option=win32co m.client.consta nts.dbMaxLocksP erFile, Value=15000)
>>
></code>
>TJG

>
Thanks. I found this: http://blogs.msdn.com/michkap/archiv...3/3849288.aspx
which outlines some difference between DAO and ADO, including:
"Capability to set and change Jet options without making registry
changes (works in DAO through DBEngine.GetOpt ion and
DBEngine.SetOpt ion, fails in ADO, which has no such analogue)."

You could try to use the Access ODBC driver and access the database
that way via mxODBC. That would be faster as well:

http://www.microsoft.com/technet/prodtechnol/windows2000serv/technologies/iis/reskit/iischp7.mspx

(scroll down to table 7.1)

Apart from that option and if you are really in need for larger
transactions, I'd suggest that you move to SQL Server for processing
(if you can). Access is not really made for heavy-lifting and big
transactions.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 02 2008)

>>Python/Zope Consulting and Support ... http://www.egenix.com/
>>mxODBC.Zope.D atabase.Adapter ... http://zope.egenix.com/
>>mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

_______________ _______________ _______________ _______________ ____________
2008-07-07: EuroPython 2008, Vilnius, Lithuania 4 days to go

:::: Try mxODBC.Zope.DA for Windows,Linux,S olaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611

**Tim Golden** · Jul 2 '08, 07:15 PM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

In case it helps, there's a recipe just shown up
on the Python Cookbook which at least illustrates
DAO use:

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/572165

TJG

**Iain King** · Jul 3 '08, 09:05 AM

Re: Win32.client, DAO.DBEngine and exceeding the file sharing countlock

On Jul 2, 8:13 pm, Tim Golden <m...@timgolden .me.ukwrote:

In case it helps, there's a recipe just shown up
on the Python Cookbook which at least illustrates
DAO use:
>

http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/572165

>
TJG

On Jul 2, 6:30 pm, "M.-A. Lemburg" <m...@egenix.co mwrote:

You could try to use the Access ODBC driver and access the database
that way via mxODBC. That would be faster as well:
>
http://www.microsoft.com/technet/pro...0serv/technolo...
(scroll down to table 7.1)
>
Apart from that option and if you are really in need for larger
transactions, I'd suggest that you move to SQL Server for processing
(if you can). Access is not really made for heavy-lifting and big
transactions.

Thanks for the help. I'll check those out, in case there's a
performance gain to be had, but I found that ADO can in fact do
exactly what I want; on the comments of the page I previously linked
to which said ADO couldn't was a posting which refuted some of the
OP's claims; ADO can set some options on it's open connection,
including Max Locks Per Record. My code now looks like this:

self._connectio n.Open(self._DS N)
if MAX_LOCKS != None:
self._connectio n.Properties("J et OLEDB:Max Locks Per File").Value
= MAX_LOCKS
rs = win32com.client .Dispatch(r'ADO DB.Recordset')

N.B. I'm writing tools software for a 3rd party app which uses an
Access db as it's output format, so I'm locked in. No way to switch
to SQL server.

Thanks both!
Iain

Win32.client, DAO.DBEngine and exceeding the file sharing count lock

Win32.client, DAO.DBEngine and exceeding the file sharing count lock

Comment

Comment

Comment

Comment

Comment

Comment