conecting with a MsAcces DB by dao

[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