469,575 Members | 1,618 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,575 developers. It's quick & easy.

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

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
Jul 2 '08 #1
6 3287
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.SetOption dbmaxlocksperfile,15000
Really hurried answer:

<code>
import win32com.client

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

</code>
TJG
Jul 2 '08 #2
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.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.dbMaxLocksPerFil e, 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.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()

---

aTdHvAaNnKcSe,
Iain
Jul 2 '08 #3
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.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.dbMaxLocksPerFil e, 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.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
Jul 2 '08 #4
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.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.dbMaxLocksPerFil e, 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.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/pro...t/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.Database.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,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
Jul 2 '08 #5
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/Coo.../Recipe/572165

TJG
Jul 2 '08 #6
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/Coo.../Recipe/572165

TJG
On Jul 2, 6:30 pm, "M.-A. Lemburg" <m...@egenix.comwrote:
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._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
Jul 3 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Michael Rybak | last post: by
1 post views Thread by rdavis7408 | last post: by
2 posts views Thread by Keith Wilby | last post: by
14 posts views Thread by Steve Jorgensen | last post: by
17 posts views Thread by Peter Duniho | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.