[python-win32] driving MS Access from python

[eric.powell at srs.gov]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/python-win32/attachments/20060313/d9784512/attachment.html