Write to MS Access table, python win32com

Question

I'm playing around with win32com.client for python to try to write/insert a row to a MS Access table. I've found an example of how to connect and query an Access table here. Basically, their code slightly modified for my own use is:

import win32com.client

connection = win32com.client.Dispatch(r'ADODB.Connection')
DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\testdb.mdb;'
connection.Open(DSN)
recordset = win32com.client.Dispatch(r'ADODB.Recordset')
recordset.Open('SELECT * FROM Table1', connection, 1, 3)
fields_dict = {}
for x in range(recordset.Fields.Count):
    fields_dict[x] = recordset.Fields.Item(x).Name
    print fields_dict[x], recordset.Fields.Item(x).Value

So this tells me how to execute a select statement on the Access table. I'd like to be able to write rows and data to the table. When using win32com for MS Office products, I tend to dive into the MSDN pages and try to interpret the VBA code for python code, but this one has me a bit handcuffed. Couple that with no examples found on the internet after lengthy searches has made me second guess whether or not this is possible? hopefully someone out there has played with this before and has a suggestion.

If you can execute a SELECT statement with win32com then you can execute an INSERT statement too, although specifying the parameters for an ADODB.Command object will be somewhat verbose. A more common approach is to use pyodbc (or perhaps pypyodbc) with the Access ODBC driver to do such straightforward CRUD operations, and save win32com for performing lower-level manipulations of the database using Access DAO, e.g., to make structural changes that Access DDL cannot handle. — Gord Thompson
– Gord Thompson, Commented Nov 7, 2016 at 23:02
Thanks Gord. I'd like to stick with win32com if I can. I had a look at pyodbc in my travels through my searches. It's where I will go next if this doesn't work out. — Mike
– Mike, Commented Nov 7, 2016 at 23:07
To piggyback @GordThompson's comment, MS Access is both a backend database and a GUI .exe application. When intending to run SQL statements like inserting rows consider db api connections (e.g., odbc) like you would with other RDMS: SQLite, SQL Server, MySQL, etc. When needing to adjust forms/reports/macros/modules, run Access app methods like DoCmd.*, or other application layers then use COM interface. Remember too VBA is an external component to Access.exe and COM-connects like you are with Python! — Parfait
– Parfait, Commented Nov 8, 2016 at 0:18

Gord Thompson · Accepted Answer · 2016-11-07 23:54:23Z

3

As I mentioned in my comment to the question, using pyodbc (or pypyodbc) and the Access ODBC driver is a more common way of doing CRUD operations, but if you really want to use win32com and OLEDB then you could do an UPDATE like this:

import win32com.client

# ADODB constants
adVarWChar = 202
adInteger = 3
adParamInput = 1

connection = win32com.client.Dispatch(r'ADODB.Connection')
DSN = (
    r'PROVIDER=Microsoft.Jet.OLEDB.4.0;'
    r'DATA SOURCE=C:\Users\Public\mdbTest.mdb;'
    )
connection.Open(DSN)
cmd = win32com.client.Dispatch(r'ADODB.Command')
cmd.ActiveConnection = connection
cmd.CommandText = "UPDATE Donors SET LastName = ? WHERE ID = ?"
cmd.Parameters.Append(cmd.CreateParameter("?", adVarWChar, adParamInput, 255))
cmd.Parameters.Append(cmd.CreateParameter("?", adInteger, adParamInput))
cmd.Parameters(0).Value = "Thompson"
cmd.Parameters(1).Value = 10
cmd.Execute()
connection.Close()

edited Nov 7, 2016 at 23:54

answered Nov 7, 2016 at 23:45

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Thanks Gord. I made a few modifications for my own use and got your script to work for me. I'm also looking a little closer at pyodbc. You've been a good help. Cheers.

Collectives™ on Stack Overflow

Write to MS Access table, python win32com

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related