conecting with a MsAcces DB by dao

L

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
 
I

Iain King

luis said:
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
 
I

Iain King

luis said:
Iain King ha escrito:

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
 
L

luis

Iain King ha escrito:
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
 
I

Iain King

luis said:
Iain King ha escrito:


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
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
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
 
D

Dennis Lee Bieber

Hi
I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002

Technically, you are using M$ JET -- Access is really just a GUI
form/report generator/front-end to a database engine. The "native"
engine being Jet -- but "Access Data Projects" use M$ SQL Server as the
engine.
while not rs.EOF:
id=rs.Fields(colName.Value) #colName, valid column name
...
rs.MoveNext()

Is that indentation correct? It looks very much as if the "move
next" is outside the while loop.

Unfortunately, between DAO, ADO, and ODBC -- I've never managed to
develop a firm basis for how to manipulate Jet data... Heck, one of the
few things I'd done in VBA (internal to Access) failed on the next
computer I obtained -- because Access changed from DAO to ADO as the
default, and I had a DAO-only feature <G>
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
L

luis

Iain King ha escrito:
luis said:
Iain King ha escrito:


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
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
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
 
S

Scott David Daniels

luis said:
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.
 
T

Tim Chase

, Access can retrieves a not empty recordset but my python code
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
 

Members online

No members online now.

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,830
Latest member
ZADIva7383

Latest Threads

Top