Prev: Trouble using pinckle
Next: Win32.client, DAO.DBEngine and exceeding the file sharing countlock
From: Iain King on 2 Jul 2008 10:11
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
From: Iain King on 2 Jul 2008 10:54
On Jul 2, 3:29 pm, Tim Golden <m...(a)timgolden.me.uk> wrote:
> 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.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()

---

aTdHvAaNnKcSe,
Iain
From: Iain King on 3 Jul 2008 05:01
On Jul 2, 8:13 pm, Tim Golden <m...(a)timgolden.me.uk> wrote:
> 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...(a)egenix.com> wrote:
> 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
 | 
Pages: 1
Prev: Trouble using pinckle
Next: Win32.client, DAO.DBEngine and exceeding the file sharing countlock