Create new Access database and tables using Python

Question

I'm trying to create an Access database in Python and add two new tables to it. I'm using win32com and have managed to create the database but can't create the tables. All I get is unhelpful Windows errors. Can anyone help me?

The following code works fine:

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
db = Dispatch("Access.Application")
dbEngine = db.DBEngine
workspace = dbEngine.Workspaces(0)

dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

How do I add new tables to this database?

Mnemonics

What version of ms access? The docssuggest you are using obsolete way of opening database if ms access 2000 or after. – Norvell

Hi Schollii, I'm trying to create 2002 - 2003 mdb – Mnemonics

Answer 1

Because MS Access is both a GUI .exe application and a backend database, to create a database is a different process than creating database objects like Tables, Views (i.e., stored queries), and even Procedures.

As a comparison, the other file-level RDMS, SQLite, one must open its .exe shell to CREATE DATABASE. And the server-level RDMS's (SQL Server, MySQL, Postgres) one must log into the server instance to run the command. MS Access does not have the shell or instance facilities, just an application object.

Therefore, after creating a database with CreateDatabase method, consider running a DDL SQL statement to CREATE TABLE which you can do with the Execute() method.

Python COM Interface

from win32com.client import Dispatch

try:
    dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'
    accApp = Dispatch("Access.Application")
    dbEngine = accApp.DBEngine
    workspace = dbEngine.Workspaces(0)

    dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
    newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

    newdb.Execute("""CREATE TABLE Table1 (
                      ID autoincrement,
                      Col1 varchar(50),
                      Col2 double,
                      Col3 datetime);""")

except Exception as e:
    print(e)

finally:
    accApp.DoCmd.CloseDatabase
    accApp.Quit
    newdb = None
    workspace = None
    dbEngine = None
    accApp = None

Python DB API

While the CREATE DATABASE is not available in MS Access SQL you can run the above Execute with any Python ODBC API with a CREATE TABLE command only after database file is created:

import pypyodbc

dbname = r'C:/Users/Guest/Desktop/NewDB.mdb'     
constr = "DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};DBQ={0};".format(dbname)

dbconn = pypyodbc.connect(constr)

cur = dbconn.cursor()
cur.execute("""CREATE TABLE Table1 (
                 ID autoincrement,
                 Col1 varchar(50),
                 Col2 double,
                 Col3 datetime);""")
dbconn.commit()

Portcullis

Thanks Parfait, exactly what I'm after! Can't believe I struggled with that for so long!!! – Mnemonics

When I try to close the application with the following: newdb.Close() accApp.Application.Quit() del accApp the Access process remains running and a new Access window appears that refuses to close. Do you know why this happens? – Mnemonics

Try accApp.DoCmd.CloseDatabase; accApp.Quit; accApp = None. The last is to uninitialize the app or release from memory. – Portcullis

When I try this it still opens a new Access window at the end of the process which reappears every time I try to close it - I have to manually kill it in the task manager – Mnemonics

See updated COM code. You have to uninitialize all objects: workspace, dbEngine, newdb, and accApp. I also wrapped all in a try/except/finally block where code will always free resources from memory regardless if it runs into errors. – Portcullis

Amazing!! Thanks Parfait :) – Mnemonics

Answer 2

For the benefit of future readers, the sample code in the question uses win32com to create the new Access database file. A simpler alternative would be to use my (free) msaccessdb Python module, e.g.,

import msaccessdb
import pyodbc

db_file = r'C:\path\to\new.accdb'
msaccessdb.create(db_file)
cnxn_str = (
    'DRIVER={{Microsoft Access Driver (*.mdb, *.accdb)}};'
    'DBQ={}'.format(db_file)
)
cnxn = pyodbc.connect(cnxn_str)

Timoshenko

Interesting. Is this valid for both .mdb and .accdb formats? Does it create a specific version? (i.e. Access 2007 or whatever) – Leventis

The .mdb and .accdb files are generic Jet_4 (Access_2000+) and ACE_12 (Access_2007+) files, respectively. They are not configured for any specific version of Access. – Timoshenko

Answer 3

It looks like you would execute a SQL statement CREATE Table on the Database object i.e. on newdb, via its Execute() method.

But I don't recommend using COM on MS Access Application object. For an MS Access database, the recommended method of interacting with the DB is to directly open the DB through ODBC API, rather than through an Access.Application instance. You should only use the latter if you need features from the app like reports, printing, etc. For interacting with MS Access DB file via ODBC, I have had good experience with pyodbc and pypyodbc, which do not require COM. A lower level access through DAO API via win32com (but not Access.Application) seems quite good too but I have not tried. Check out http://www.icodeguru.com/WebServer/Python-Programming-on-Win32/ch13.htm for a good discussion on various connection API available and some code examples.

Other useful resources:

• ms access application automation API: the examples use vba but win32com provides same API.
• intro to the above with links for ADO-based tasks that therefore don't need the ms access application: links show c# but win32com provides same API.

Norvell

Unfortunately, MS Access unlike other DBMS does not have the CREATE DATABASE SQL command so a COM interface is needed initially but then a DB API like Python's pyodbc, pypyodbc, adodbapi can be used for other DML/DDL statements. Also, OP may not have the GUI .exe program. – Portcullis

@parfait The question is "How do I add new tables to this database", not "How do you create a database". Based on msdn.microsoft.com/en-us/library/bb177893(v=office.12).aspx, the CREATE TABLE statement is available in MS Access. – Norvell

Answer 4

newdb = workspace.CreateDatabase(dbname, dbLangGeneral, 64)

has already created a DAO.Database object named newdb, so you should be able to simply go ahead and use its Execute method to run a DDL statement like this

newdb.Execute("CREATE TABLE [Table1] ([ID] COUNTER PRIMARY KEY, [TextField] TEXT(50))")

Timoshenko