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 <***@phillatwood.name>
Sent by: python-win32-***@python.org
03/12/2006 05:19 PM
To
python-***@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