<br><font size=2 face="sans-serif">Exactly.</font>
<br>
<br><font size=2 face="sans-serif">Once you set up the iDispatch to point
to the ADODB object library, you can then use the ADO methods and objects.</font>
<br>
<br><font size=2 face="sans-serif">One advantage of ADO is that it SHOULD
be installed as part of Windows (I think...I have never run into a case
where is wasn't, anyone on the list want to correct me on this????), so
things should work out of the box (so to speak).</font>
<br>
<br><font size=2 face="sans-serif">To get the dimensions of the recordset
you could use the Fields and Rows count count methods in ADO</font>
<br>
<br><font size=2><tt>Open Connection stuff...</tt></font>
<br><font size=2><tt>rs = win32com.client.Dispatch(r'ADODB.Recordset')</tt></font>
<br><font size=2><tt>rs.Open(sqlstatement, conn,1 ,3)</tt></font>
<br><font size=2><tt>FC = rs.Fields.Count</tt></font>
<br><font size=2><tt>RC = rs.Rows.Count</tt></font>
<br>
<br><font size=2><tt>Further for navigating between rows, you have:</tt></font>
<br>
<br><font size=2><tt>rs.MoveFirst</tt></font>
<br><font size=2><tt>rs.MoveLast</tt></font>
<br><font size=2><tt>rs.MoveNext</tt></font>
<br><font size=2><tt>rs.MovePrevious ???? (I think you can iterate backward).</tt></font>
<br>
<br><font size=2><tt>To get the value of a given field, descend through
the Recordset->Fields->Item() chain as follows:</tt></font>
<br>
<br><font size=2><tt>val = rs.Fields.Item(1).Value</tt></font>
<br>
<br><font size=2><tt>Where the iterator in item can be either an ineteger
or a fieldname:</tt></font>
<br>
<br><font size=2><tt>val = rs.Fields.Item("Fred").Value</tt></font>
<br>
<br>
<br><font size=2 face="sans-serif">ADO can also access/fire an existing
action query or return the records from a stored query.</font>
<br>
<br><font size=2 face="sans-serif">More info: </font>
<br>
<br><font size=2 face="sans-serif">http://www.w3schools.com/ado/default.asp</font>
<br><font size=2 face="sans-serif">http://www.mayukhbose.com/python/ado/ado-python.php</font>
<br>
<br><font size=2 face="sans-serif">HTH,</font>
<br>
<br><font size=2 face="sans-serif">Eric</font>
<br>
<br><font size=2 face="sans-serif">Eric B. Powell<br>
BSRI<br>
Electronic Aids<br>
(803)208-6207<br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>Phill Atwood <me@phillatwood.name></b>
</font>
<p><font size=1 face="sans-serif">03/13/2006 03:28 PM</font>
<td width=59%>
<table width=100%>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td><font size=1 face="sans-serif">eric.powell@srs.gov</font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td><font size=1 face="sans-serif">python-win32@python.org</font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td><font size=1 face="sans-serif">Re: [python-win32] driving MS Access
from python</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2><tt><br>
Eric,<br>
<br>
Thanks. This seems like a good idea. I noticed also ADOdb for
Python. <br>
Would this work do you know? Since I'm doing win/ms access I think
I'll <br>
need to use mxodbc but I don't know how to install that on Windows.<br>
<br>
In your example below it is not clear (to me) how to get the dimensions
<br>
of the recordset and navigate thru (eg. print) all the resultant <br>
fields. Is there something similar to the ADOdb for<br>
Python<br>
while not cursor.EOF:<br>
print cursor.fields<br>
cursor.MoveNext()<br>
cursor.close()<br>
<br>
<br>
<br>
eric.powell@srs.gov wrote:<br>
><br>
> I would suggest using ADO (Microsoft Active X Data Objects) for this.
<br>
> Much cleaner (and more flexible) than trying to use the Access <br>
> executable.<br>
><br>
> import win32com.client, string<br>
><br>
> #Establish the ADO DB Connection<br>
> class fields:<br>
> def __init__(self, dbpath, tblName):<br>
> self.dbpath = dbpath<br>
> self.tblName=tblName<br>
> def add(self, sqlstatement):<br>
> conn = win32com.client.Dispatch(r'ADODB.Connection')<br>
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=' + <br>
> self.dbpath + ';'<br>
> conn.Open(DSN)<br>
><br>
> #Query the recordset - should be in module
with establishing <br>
> connection stuff<br>
> rs = win32com.client.Dispatch(r'ADODB.Recordset')<br>
> print sqlstatement<br>
> try:<br>
> rs.Open(sqlstatement, conn,1
,3)<br>
> except:<br>
> print 'DB Error'<br>
><br>
> conn.Close()<br>
> #Update 1 record in the database. Fieldlist is a list
of tuples <br>
> consisting of (Name, Value)<br>
> def update(self, attdata, fieldlist, wherecl):<br>
> conn = win32com.client.Dispatch(r'ADODB.Connection')<br>
> DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=' + <br>
> self.dbpath + ';'<br>
> conn.Open(DSN)<br>
><br>
> rs = win32com.client.Dispatch(r'ADODB.Recordset')<br>
> rs_name = 'UPDATE ' + self.tblName + '
SET '<br>
><br>
> listlen = len(attdata)<br>
> print 'List is ' + str(listlen) + "
items long"<br>
> for index in range (0,listlen):<br>
> field = attdata[index]<br>
> ftype = fieldlist[index][1]<br>
> #Add function to de-capitalize
string,<br>
> if string.capitalize(ftype[0:7])
== 'Varchar':<br>
> try:<br>
>
data = '"' + str(field[1]) + '"'<br>
> except:<br>
>
data = '"' + field[1] + '"'<br>
> elif string.capitalize(ftype[0:4])
== "Date":<br>
> try:<br>
>
data = '#' + str(field[1]) + '#'<br>
> except:<br>
>
data = '#' + field[1] + '#'<br>
> else:<br>
> data = field[1]<br>
> <br>
> if index == 0:<br>
> rs_name =
rs_name + self.tblName+ '.' + field[0] + ' = <br>
> ' + data<br>
> else:<br>
> try:<br>
>
rs_name = rs_name + ', ' + self.tblName+ '.' + <br>
> field[0] + ' = ' + data<br>
> except:<br>
>
print 'Error in data type match, debug data <br>
> follows: '<br>
>
print rs_name<br>
>
print self.tblName<br>
>
print field[0]<br>
>
print data<br>
> rs_name = rs_name + ' ' + wherecl + ';'<br>
> rs.Open(rs_name, conn,1 ,3)<br>
> conn.Close()<br>
><br>
> This script:<br>
><br>
> 1) Opens a database conenction<br>
> 2) Allows execution of an append query (supplied as a SQL string)<br>
> 3) Allows exceution of an update query.<br>
><br>
> Other sources on info:<br>
><br>
> MSDN query on ADO<br>
><br>
> There is an ADO / Python page out there (check google) but I can't
<br>
> remember the URL.<br>
><br>
> HTH,<br>
><br>
> Eric<br>
><br>
> Eric B. Powell<br>
> BSRI<br>
> Electronic Aids<br>
> (803)208-6207<br>
><br>
><br>
><br>
> *Phill Atwood <me@phillatwood.name>*<br>
> Sent by: python-win32-bounces@python.org<br>
><br>
> 03/12/2006 05:19 PM<br>
><br>
> <br>
> To<br>
> python-win32@python.org<br>
> cc<br>
> <br>
> Subject<br>
> [python-win32]
driving MS Access from python<br>
><br>
><br>
><br>
> <br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
> Need to programatically reproduce some activities a user does in MS
<br>
> Access.<br>
> Need to open a mdb file. Run some queries. Look at some
tables.<br>
><br>
> So far<br>
><br>
> import win32com.client<br>
> a = win32com.client.Dispatch("Access.Application")<br>
> a.Visible = 1<br>
> db = a.OpenCurrentDatabase(filename)<br>
><br>
> Which seems to work so far. But now I need to run 2 of 3 named<br>
> queries. Then switch<br>
> to the Tables view and collect the info that has now populated the<br>
> tables and extract them<br>
> into python code. I having a hard time scaring up appropriate
docs on<br>
> the MS Access<br>
> win32com API for this. I've been looking for VBA style docs
or anything<br>
> but I'm still so<br>
> far just sniffing the corners...<br>
><br>
> Any help is appreciated.<br>
><br>
> Phill<br>
><br>
> _______________________________________________<br>
> Python-win32 mailing list<br>
> Python-win32@python.org<br>
> http://mail.python.org/mailman/listinfo/python-win32<br>
><br>
<br>
<br>
</tt></font>
<br>