conecting with a MsAcces DB by dao

[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