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?
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()