0

I am trying to automate some Python/Access interactions using Python 3.5.2 and Canopy. I am currently trying to use tkinter to get a user defined file name. This is then used to create a new Python database (deleting an existing one if necessary). However, I am unable to connect to the existing database. 

import pyodbc #connect to Access
import os #allows us to do some directory changing and file work
import tkinter #lets us ask the user for a directory
from tkinter import filedialog
from comtypes.client import CreateObject
 #for creating the mdb
root = tkinter.Tk()

mdbPath = filedialog.asksaveasfilename(parent=root, title='Create a filename for your output database', defaultextension=".mdb")

mdbPath = mdbPath.replace("/","\\")
root.destroy()
if os.path.exists(mdbPath):
    os.remove(mdbPath)   
print('selected file')
DBEngine = CreateObject('DAO.DBEngine.120')

dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'
db = DBEngine.CreateDatabase(mdbPath, dbLangGeneral, 64)
db.Close()

connAccess = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ={'+mdbPath+'};'
    )

cnxnAccess = pyodbc.connect(connAccess)
cursAccess = cnxnAccess.cursor()

As far as I can tell, everything works fine until I try to call pyodbc.connect(connAccess), and I am unsure why. I've tried looking at some related questions (e.g., "General error Unable to open registry key Temporary (volatile) ..." from Access ODBC) but can't understand how they're related.

Based on Create new Access database and tables using Python my best guess is that I need to release the assets after creating db (previously newdb) in some way. (e.g., Create new Access database and tables using Python) However, I cannot seem to send any commands that actually "release" this database. Even using db = Null or del db doesn't appear to do it.

Edit: Adjusted code to my current working code. Changed tags to reflect my use of comtypes instead of com32. Edit 2: Slightly adjusted code. Added another line of explanation of what I've tried.

KDM
  • 81
  • 7
  • If you're only using `win32com` to create the database file then you might find it easier to just use my free [msaccessdb](https://github.com/gordthompson/msaccessdb) Python module to do that. – Gord Thompson Mar 16 '18 at 16:20
  • @GordThompson thanks for the suggestion. Unfortunately I need to distribute this code amongst users of a variety of skill levels, so I'm restricted (or restricting myself) to packages available through Canopy, which is the general python editor for the group. – KDM Mar 17 '18 at 21:19
  • Have you tried `newdb.Close` immediately after you call `CreateDatabase`? – Gord Thompson Mar 17 '18 at 23:36
  • @GordThompson Yes I've also added db.Quit(), setting the 4 db/access elements to none, and del'ing them. No change! Sorry for the delay - I had to run this on a different computer after updating Access for a reason I've yet to determine. – KDM Mar 23 '18 at 14:59

0 Answers0