views:

207

answers:

1

I would like to create a mdb database file in windows with Python and can't seem to figure it out with the Python Docs. Everything I read about is related to making a connection and what to do with the cursor.

Any thoughts? Thanks...

+3  A: 

My experience with the comtypes module has been fairly good. You'll probably want to have an Access DAO/ADO/VBA reference handy for the methods that are used, however, as the comtypes module generates COM library wrappers dynamically, so there's no built-in documentation.

Here's a brief example of how it works. (Go ahead and test it out yourself.)

from comtypes.client import CreateObject

access = CreateObject('Access.Application')

from comtypes.gen import Access

DBEngine = access.DBEngine
db = DBEngine.CreateDatabase('test.mdb', Access.DB_LANG_GENERAL)
      # For me, test.mdb was created in my My Documents folder when I ran the script 

db.BeginTrans()

db.Execute("CREATE TABLE test (ID Text, numapples Integer)")
db.Execute("INSERT INTO test VALUES ('ABC', 3)")

db.CommitTrans()
db.Close()

(Moved the second import statement after the CreateObject line for cases where the Python wrapper module for the typelibrary didn't previously exist.)

JAB
If you can create a DBEngine variable then you could also use all the DAO table, field, index and relationships objects. These give you much more detailed control of the objects created. See the TempTables.MDB page at my website which illustrates how to use a temporary MDB in your app. http://www.granite.ab.ca/access/temptables.htm
Tony Toews
Sorry to be so new to this, but I installed the comtypes and tried to run the code, but then I got the following exception: "ImportError: cannot import name Access". Any ideas?
GuidoS
@Tony Toews: Yes, well... it may have been because I didn't actually insert data into the table I created in that manner, but when I created a table and added a field using DAO, it didn't show up in the database when I opened it in Access afterwards, and since this was just meant as an example of using `comtypes` to create an MDB file I didn't feel like spending more time figuring it out.
JAB
@GuidoS: You could use the `GetModule` method of `comtypes.client` in an interpreter to generate the proper Python modules if you know how to refer to the Access typelibrary; if you don't know the specifics of that, then you can just open up an interpreter and type `from comtypes.client import CreateObject`, then `CreateObject('Access.Application')`, and it should automatically generate the Python wrapper module if it doesn't already exist. ...Of course, this is all moot if you don't actually have Microsoft Access installed on the computer you're running the script from in the first place.
JAB
Also modified my answer with what is probably a simpler solution.
JAB
@JAB thanks, this works now. I will go learn about comtypes a little more so I can understand this better in the future.
GuidoS