Python Create Access database using win32com

Question

I want to create an Access database (*.accdb) from within a Python script. Using win32com and Dispatch I can call the application. However, I cant find anything on how to create a new database.

access = win32com.client.Dispatch('Access.Application')

At that point I have no need to put data into the database and I would do this using pyodbc - I simply need to create an empty database.

Does somebody has an example on how to do this?

Cheers Thomas

Answer 1

You have an Access application object. Use its DBEngine.CreateDatabase method to create your db file.

This sample worked from Python 2.7 to create an MDB format database file. To create an ACCDB, use 128 (dbVersion120) for dbVersion.

import win32com.client
oAccess = win32com.client.Dispatch('Access.Application')
DbFile = r'C:\Users\hans\Documents\NewDb.mdb'
dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
# dbVersion40 64
dbVersion = 64
oAccess.DBEngine.CreateDatabase(DbFile, dbLangGeneral, dbVersion)
oAccess.Quit()
del oAccess

Answer 2

To create a new, empty .accdb file, the following Python code should do the trick:

import win32com.client
f = 'C:\\Users\\Gord\\Desktop\\pyTest.accdb'
c = win32com.client.Dispatch('ADOX.Catalog')
c.Create('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + f + ';')
c = None
print '"' + f + '" created.'

[Edit 1]

A comment to a blog posting here suggests that if the .Create call generates a "Class not registered" error you may need to use regsvr32.exe to re-register msadox.dll. Be aware of "bitness" when you attempt this: There are 32-bit and 64-bit versions of both of those files:

64-bit
C:\Windows\System32\regsvr32.exe
C:\Program Files\Common Files\System\ado\msadox.dll

32-bit
C:\Windows\SysWOW64\regsvr32.exe
C:\Program Files (x86)\Common Files\System\ado\msadox.dll

Also, be aware that you could be running 32-bit Python on a 64-bit machine.

[Edit 2]

I've done a few tests and have reached the conclusion that this approach did not work in this particular case because the Python script was running as 64-bit, but the 64-bit Access Database Engine was not installed. (32-bit Office only installs the 32-bit version of ACE.)

The error message was perhaps a bit misleading. It wasn't the ADOX component that was missing (not registered), it was the 64-bit version of the ACE engine itself that couldn't be found.

Furthermore, on a 64-bit machine with 32-bit Access installed, the 64-bit version of ACE will never be available because it cannot be installed

This could very well have implications when you try to manipulate data within the .accdb file from a 64-bit Python script. I didn't have Python available on my "32-bit Office on 64-bit Windows" test machine, but when I tried the following VBScript...

Option Explicit
Dim con, rst
Set con = CreateObject("ADODB.Connection")
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\Users\Gord\Desktop\adoTest.accdb;"
Set rst = CreateObject("ADODB.Recordset")
rst.Open "SELECT Field1 FROM Table1", con
Wscript.Echo rst(0).Value
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing

...the results were as follows:

C:\__tmp>C:\Windows\System32\cscript.exe /nologo dataAccessTest.vbs
C:\__tmp\dataAccessTest.vbs(4, 1) ADODB.Connection: Provider cannot be found. 
It may not be properly installed.

C:\__tmp>C:\Windows\SysWOW64\cscript.exe /nologo dataAccessTest.vbs
This is Table1 data in Access.

The script failed when run as 64-bit, but it worked when run as 32-bit.

Recommendation: If your machine has 32-bit Access installed you'll probably be better off running your Python scripts as 32-bit too.

Answer 3

import win32com.client
ConFileName = r'c:\mydb\myaccess.mdb'
try:
    Catalog = win32com.client.Dispatch('ADOX.Catalog')
    Catalog.Create('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + ConFileName + ';')
    Catalog = None
except:
    Exception as e:
        print("Database generation failed, Error="+str(e))
print("NewAccessDB.mdb created successfully")

Then you can connect to access database :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()

To insert in to the access table :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' +ConFileName+ ';')
cursor = conn.cursor()
for ta in TableArray:
    Sql_insert_query = "INSERT INTO Table1(ID, Value1,Value2,Value3,Value4,Value5,Value6) " \
                         "VALUES ('{a}','{b}','{c}','{d}','{e}','{f}','{g}')".format(a=str(ta[0]),b=str(ta[1]),c=str(ta[2]),d=str(ta[3]),e=str(ta[4]),f=str(ta[5]),g=str(ta[6]))
    cursor.execute(Sql_insert_query)
    conn.commit()
cursor.close()

Please follow this link for more information :

https://elvand.com/python-and-ms-access/