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 = "">
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 <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 03/12/2006 05:19 PM |
|
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@python.org
http://mail.python.org/mailman/listinfo/python-win32
_______________________________________________ Python-win32 mailing list Python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32
