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

[Iain King]

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.SetOption dbmaxlocksperfile,15000

Can I do this in win32com? I've been using ADO, not DAO, but I have
to confess to not knowing exactly what the difference is. I set up my
recordset thusly:

rs = win32com.client.Dispatch(r'ADODB.Recordset')

can I jigger it to increase it's max locks?

Iain

 

[Tim Golden]

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.SetOption dbmaxlocksperfile,15000

Really hurried answer:

<code>
import win32com.client

dao = win32com.client.gencache.EnsureDispatch ("DAO.DBEngine.36")
dao.SetOption (Option=win32com.client.constants.dbMaxLocksPerFile, Value=15000)

</code>
TJG

 

[Iain King]

Really hurried answer:

<code>
import win32com.client

dao = win32com.client.gencache.EnsureDispatch ("DAO.DBEngine.36")
dao.SetOption (Option=win32com.client.constants.dbMaxLocksPerFile, Value=15000)

</code>
TJG

Thanks. I found this: http://blogs.msdn.com/michkap/archive/2007/07/13/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.GetOption and
DBEngine.SetOption, 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._connection = win32com.client.Dispatch(r'ADODB.Connection')
self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE='+dbfile+';'

self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
query = 'SELECT '+field+' FROM '+self.__TABLE
rs.Open(query, self._connection, 1, 3)
while not rs.EOF:
v = function(rs.Fields.Item(0).Value)
if v != RETAIN_VALUE:
rs.Fields.Item(0).Value = v
rs.MoveNext()
rs.Close()

 

[Tim Golden]

Thanks. I found this: http://blogs.msdn.com/michkap/archive/2007/07/13/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.GetOption and
DBEngine.SetOption, 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]

Thanks. I found this: http://blogs.msdn.com/michkap/archive/2007/07/13/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.GetOption and
DBEngine.SetOption, 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)________________________________________________________________________
2008-07-07: EuroPython 2008, Vilnius, Lithuania 4 days to go

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,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]

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]

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

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/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._connection.Open(self._DSN)
if MAX_LOCKS != None:
self._connection.Properties("Jet OLEDB:Max Locks Per File").Value
= MAX_LOCKS
rs = win32com.client.Dispatch(r'ADODB.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