Re: conecting with a MsAcces DB by dao

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

.

Relevant Pages

  • Re: Using field name for IN text string in query criteria
    ... the query to pull the NetworkID's instead? ... I tried this before and the compiler does not accept it. ... literal string delimiter is actually the apostrophe: ... It returns an empty recordset. ...
    (microsoft.public.access.formscoding)
  • Re: Parameters in INSERT query problem
    ... > parameters in the query... ... an implicit conversion is going on to make ... why are you creating a recordset from the Execute method of the Command ... You'll only get an empty recordset, ...
    (microsoft.public.data.ado)
  • Re: Query and recordsource
    ... Steve, Thanks for the info. ... This works OK but......When the query returns a empty recordset I get an ... hourglass for a few seconds and then it exits access. ... and a cmdbutton that sets the forms recordsource to the query. ...
    (microsoft.public.access.queries)
  • Re: Total records in a query
    ... >>I would like to retrieve the total records in a query. ... >>MS said to use the MoveLast command, but I have to install an error handle ... > You can check for an empty recordset by checking its ...
    (microsoft.public.access.formscoding)
  • Re: conecting with a MsAcces DB by dao
    ... , for example, no problem, Access can retrieve a not empty recordset ... But if the Access's query includes a LIKE clause, ... Access can retrieves a not empty recordset but my python code ... retrieves a empty recordset. ...
    (comp.lang.python)