[python-win32] driving MS Access from python

[Phill Atwood]

Eric,

Thanks.  This seems like a good idea.  I noticed also ADOdb for Python. 
Would this work do you know?  Since I'm doing win/ms access I think I'll 
need to use mxodbc but I don't know how to install that on Windows.

In your example below it is not clear (to me) how to get the dimensions 
of the recordset and navigate thru (eg. print) all the resultant 
fields.  Is there something similar to the ADOdb for
Python
    while not cursor.EOF:
        print cursor.fields
        cursor.MoveNext()
    cursor.close()



eric.powell at srs.gov wrote:
>
> I would suggest using ADO (Microsoft Active X Data Objects) for this. 
> Much cleaner (and more flexible) than trying to use the Access 
> executable.
>
> import win32com.client, string
>
> #Establish the ADO DB Connection
> class fields:
>     def __init__(self, dbpath, tblName):
>         self.dbpath = dbpath
>         self.tblName=tblName
>     def add(self, sqlstatement):
>         conn = win32com.client.Dispatch(r'ADODB.Connection')
>         DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' + 
> self.dbpath + ';'
>         conn.Open(DSN)
>
>         #Query the recordset - should be in module with establishing 
> connection stuff
>         rs = win32com.client.Dispatch(r'ADODB.Recordset')
>         print sqlstatement
>         try:
>             rs.Open(sqlstatement, conn,1 ,3)
>         except:
>             print 'DB Error'
>
>         conn.Close()
>     #Update 1 record in the database. Fieldlist is a list of tuples 
> consisting of (Name, Value)
>     def update(self, attdata, fieldlist, wherecl):
>         conn = win32com.client.Dispatch(r'ADODB.Connection')
>         DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=' + 
> self.dbpath + ';'
>         conn.Open(DSN)
>
>         rs = win32com.client.Dispatch(r'ADODB.Recordset')
>         rs_name = 'UPDATE ' + self.tblName + ' SET '
>
>         listlen = len(attdata)
>         print 'List is ' + str(listlen) + " items long"
>         for index in range (0,listlen):
>             field  = attdata[index]
>             ftype = fieldlist[index][1]
>             #Add function to de-capitalize string,
>             if string.capitalize(ftype[0:7]) == 'Varchar':
>                 try:
>                     data = '"' + str(field[1]) + '"'
>                 except:
>                     data = '"' + field[1] + '"'
>             elif string.capitalize(ftype[0:4]) == "Date":
>                 try:
>                     data = '#' + str(field[1]) + '#'
>                 except:
>                     data = '#' + field[1] + '#'
>             else:
>                 data = field[1]
>            
>             if index == 0:
>                 rs_name = rs_name + self.tblName+ '.' + field[0] + ' = 
> ' + data
>             else:
>                 try:
>                     rs_name = rs_name + ', ' + self.tblName+ '.' + 
> field[0] + ' = ' + data
>                 except:
>                     print 'Error in data type match, debug data 
> follows: '
>                     print rs_name
>                     print self.tblName
>                     print field[0]
>                     print data
>         rs_name = rs_name + ' ' + wherecl + ';'
>         rs.Open(rs_name, conn,1 ,3)
>         conn.Close()
>
> This script:
>
> 1) Opens a database conenction
> 2) Allows execution of an append query (supplied as a SQL string)
> 3) Allows exceution of an update query.
>
> Other sources on info:
>
> MSDN query on ADO
>
> There is an ADO / Python page out there (check google) but I can't 
> remember the URL.
>
> HTH,
>
> Eric
>
> Eric B. Powell
> BSRI
> Electronic Aids
> (803)208-6207
>
>
>
> *Phill Atwood <me at phillatwood.name>*
> Sent by: python-win32-bounces at python.org
>
> 03/12/2006 05:19 PM
>
> 	
> To
> 	python-win32 at python.org
> cc
> 	
> Subject
> 	[python-win32] driving MS Access from python
>
>
>
> 	
>
>
>
>
>
>
>
> Need to programatically reproduce some activities a user does in MS 
> Access.
> Need to open a mdb file.  Run some queries.  Look at some tables.
>
> So far
>
> import win32com.client
> a = win32com.client.Dispatch("Access.Application")
> a.Visible = 1
> db = a.OpenCurrentDatabase(filename)
>
> Which seems to work so far.  But now I need to run 2 of 3 named
> queries.  Then switch
> to the Tables view and collect the info that has now populated the
> tables and extract them
> into python code.  I having a hard time scaring up appropriate docs on
> the MS Access
> win32com API for this.  I've been looking for VBA style docs or anything
> but I'm still so
> far just sniffing the corners...
>
> Any help is appreciated.
>
> Phill
>
> _______________________________________________
> Python-win32 mailing list
> Python-win32 at python.org
> http://mail.python.org/mailman/listinfo/python-win32
>