conecting with a MsAcces DB by dao

[luis]

Hi
I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
(reading first http://starship.python.net/crew/bwilk/access.html)
I have writed the following code

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I'm using it for reading tables or queries in a mdb file.
With some mdb it works fine and return a no empty recordset, but with
others mdb files, the recordsets are void (opening the tables or
recorsets with Ms Access are not void).
Some help is welcome,
Thanks in advance
Luis

[Iain King]

luis wrote:

Hi
I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
(reading first http://starship.python.net/crew/bwilk/access.html)
I have writed the following code

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I'm using it for reading tables or queries in a mdb file.
With some mdb it works fine and return a no empty recordset, but with
others mdb files, the recordsets are void (opening the tables or
recorsets with Ms Access are not void).
Some help is welcome,
Thanks in advance
Luis

I don't know if it's the problem your asking about, but your
rs.MoveNext() should be inside the while loop, no?

Iain

[luis]

Iain King ha escrito:

luis wrote:

Hi
I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
(reading first http://starship.python.net/crew/bwilk/access.html)
I have writed the following code

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I'm using it for reading tables or queries in a mdb file.
With some mdb it works fine and return a no empty recordset, but with
others mdb files, the recordsets are void (opening the tables or
recorsets with Ms Access are not void).
Some help is welcome,
Thanks in advance
Luis
I don't know if it's the problem your asking about, but your
rs.MoveNext() should be inside the while loop, no?

Yes, is inside
Iain

[Iain King]

luis wrote:

Iain King ha escrito:

luis wrote:

while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

I don't know if it's the problem your asking about, but your
rs.MoveNext() should be inside the while loop, no?

Yes, is inside

You mean, it is inside the while loop in your code, but you made a
mistake copying it into your post? In the code you posted it is not
inside the while loop - it would have to be indented one more level for
that.

Iain

[luis]

Iain King ha escrito:

luis wrote:

Iain King ha escrito:

luis wrote:
> while not rs.EOF:
> id=rs.Fields(colName.Value) #colName, valid column name
> ...
> rs.MoveNext()
> rs.Close()
> conn.Close()

I don't know if it's the problem your asking about, but your
rs.MoveNext() should be inside the while loop, no?

Yes, is inside

You mean, it is inside the while loop in your code, but you made a
mistake copying it into your post? In the code you posted it is not
inside the while loop - it would have to be indented one more level for
that.

Iain

this is te correct identation

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I think my problem must be with ado and dao.
Now I have run makepy utility and select Microsoft ActiveX Data Objects
2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
and write
win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
Do you know is it possible ?
luis

[Iain King]

luis wrote:

Iain King ha escrito:

luis wrote:

Iain King ha escrito:

> luis wrote:
> > while not rs.EOF:
> > id=rs.Fields(colName.Value) #colName, valid column name
> > ...
> > rs.MoveNext()
> > rs.Close()
> > conn.Close()
>
> I don't know if it's the problem your asking about, but your
> rs.MoveNext() should be inside the while loop, no?
Yes, is inside
>

You mean, it is inside the while loop in your code, but you made a
mistake copying it into your post? In the code you posted it is not
inside the while loop - it would have to be indented one more level for
that.

Iain

this is te correct identation

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I think my problem must be with ado and dao.
Now I have run makepy utility and select Microsoft ActiveX Data Objects
2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
and write
win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
Do you know is it possible ?
luis

Well, without being able to test on your system I don't think I can
give you any real advice. This is the module I use to interface with
Access:

Access.py
---------------
import win32com.client
from win32com.client import constants

def isWriteable(field):
"""Is given Field writeable?"""
return field.Attributes & 4
class Access(object):
def __init__(self, filename, password=""):
self._filename = filename
self._connection = win32com.client.Dispatch(r'ADODB.Connection')
if password:
self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;Jet
OLEDB:Database Password=%s;' % (filename, password)
else:
self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;' %
(filename)

def Query(self, query):
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
fields = []
for x in xrange(rs.Fields.Count):
fields.append(rs.Fields(x).Name)
if rs.EOF:
data = []
else:
data = rs.GetRows()
rs.Close()
self._connection.Close()
return fields, data
def Add(self, table, records):
"""Adds records to table."""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(table, self._connection, 1, 3)
unwriteables = []
for record in records:
rs.AddNew()
unwriteable = []
for i in xrange(len(record)):
if isWriteable(rs.Fields(i)):
rs.Fields(i).Value = record[i]
else:
unwriteable.append(rs.Fields(i).Value)
unwriteables.append(unwriteable)
rs.Update()
rs.Close()
self._connection.Close()
return unwriteables
def Update(self, query, function):
"""Updates all records found in query with function(record)"""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
columns = rs.Fields.Count
while not rs.EOF:
record = []
for i in xrange(columns):
record.append(rs.Fields(i).Value)
newRecord = function(record[:])
for i in xrange(columns):
if isWriteable(rs.Fields(i)):
rs.Fields(i).Value = newRecord[i]
rs.MoveNext()
rs.Close()
self._connection.Close()
def Delete(self, query):
"""Deletes all records found in query"""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
while not rs.EOF:
rs.Delete()
rs.MoveNext()
rs.Close()
self._connection.Close()
We only have Access 2000+, so I don't know if it'll work with prior
versions.

Iain

[luis]

Iain King ha escrito:

luis wrote:

Iain King ha escrito:

luis wrote:
Iain King ha escrito:

luis wrote:
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()
>
I don't know if it's the problem your asking about, but your
rs.MoveNext() should be inside the while loop, no?
Yes, is inside
>
>
You mean, it is inside the while loop in your code, but you made a
mistake copying it into your post? In the code you posted it is not
inside the while loop - it would have to be indented one more level for
that.
>
Iain

this is te correct identation

def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:/Afile.mdb;"
conn.Open(DSN)
except Exception, inst:
...
try:
sql_statement='SELECT * FROM Mytable'
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(sql_statement, conn, 1, 3)
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()
rs.Close()
conn.Close()

except Exception, inst:
...

I think my problem must be with ado and dao.
Now I have run makepy utility and select Microsoft ActiveX Data Objects
2.5 Library, perhaps I must also select Microsoft DAO3.5 Object Library
and write
win32com.client.Dispatch("DAO.DBEngine.35") for Access 97 or
win32com.client.Dispatch(r'ADODB.Connection') for Acess 2000
Do you know is it possible ?
luis

Well, without being able to test on your system I don't think I can
give you any real advice. This is the module I use to interface with
Access:

Access.py
---------------
import win32com.client
from win32com.client import constants

def isWriteable(field):
"""Is given Field writeable?"""
return field.Attributes & 4
class Access(object):
def __init__(self, filename, password=""):
self._filename = filename
self._connection = win32com.client.Dispatch(r'ADODB.Connection')
if password:
self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;Jet
OLEDB:Database Password=%s;' % (filename, password)
else:
self._DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;' %
(filename)

def Query(self, query):
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
fields = []
for x in xrange(rs.Fields.Count):
fields.append(rs.Fields(x).Name)
if rs.EOF:
data = []
else:
data = rs.GetRows()
rs.Close()
self._connection.Close()
return fields, data
def Add(self, table, records):
"""Adds records to table."""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(table, self._connection, 1, 3)
unwriteables = []
for record in records:
rs.AddNew()
unwriteable = []
for i in xrange(len(record)):
if isWriteable(rs.Fields(i)):
rs.Fields(i).Value = record[i]
else:
unwriteable.append(rs.Fields(i).Value)
unwriteables.append(unwriteable)
rs.Update()
rs.Close()
self._connection.Close()
return unwriteables
def Update(self, query, function):
"""Updates all records found in query with function(record)"""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
columns = rs.Fields.Count
while not rs.EOF:
record = []
for i in xrange(columns):
record.append(rs.Fields(i).Value)
newRecord = function(record[:])
for i in xrange(columns):
if isWriteable(rs.Fields(i)):
rs.Fields(i).Value = newRecord[i]
rs.MoveNext()
rs.Close()
self._connection.Close()
def Delete(self, query):
"""Deletes all records found in query"""
self._connection.Open(self._DSN)
rs = win32com.client.Dispatch(r'ADODB.Recordset')
rs.Open(query, self._connection, 1, 3)
while not rs.EOF:
rs.Delete()
rs.MoveNext()
rs.Close()
self._connection.Close()
We only have Access 2000+, so I don't know if it'll work with prior
versions.

Iain

Thanks, for your code

My problem was opening a query (not a table) on mdb file

1) If the query includes a where clause type
field1="edf" and field2=3
, for example, no problem, Access can retrieve a not empty recordset
and my python code too.

2) But if the Access's query includes a LIKE clause, for example
field1="e*"
, Access can retrieves a not empty recordset but my python code
retrieves a empty recordset.

If I write the WHERE clause inside my python code, the recorset returns
an non empty recordset. Perhaps the problem was the different use of *
and % in Access and SQL, I don't know.

Luis

[Scott David Daniels]

luis wrote:

My problem was opening a query (not a table) on mdb file

1) If the query includes a where clause type
field1="edf" and field2=3
, for example, no problem, Access can retrieve a not empty recordset
and my python code too.

2) But if the Access's query includes a LIKE clause, for example
field1="e*"
, Access can retrieves a not empty recordset but my python code
retrieves a empty recordset.

Which is exactly what it _should_ return on that query, unless you have
records with "e*" in column field1.

The proper SQL clause is:

... WHERE field1 LIKE 'e*' ...

But, you did not include that part of the program, so you wound up
crippling those who were willing to try to help you, because you
thought you kinda-sorta knew what was going on (but not enough to
fix it). Find "smart questions" and read it a couple of times.
--
--Scott David Daniels
sc***********@acm.org

[Tim Chase]

>, Access can retrieves a not empty recordset but my python code

>retrieves a empty recordset.

Which is exactly what it _should_ return on that query, unless you have
records with "e*" in column field1.

The proper SQL clause is:

... WHERE field1 LIKE 'e*' ...

which would also return an empty recordset unless the OP has
records with "e*" in column field1...

the proper SQL clause would be

... WHERE field1 LIKE 'e%' ...

(in SQL, the "%" and "_" are used as wildcards, not asterisk and
question-mark respectively as they are in most shell expansions)

:)

-tkc