CODEWITHSUNDEEP
pythonms-accesstableau-api
Ricardo Vilaça
Ricardo Vilaça

Reputation: 1006

Permanently changing the password of an Microsoft Access DB (via Python)

I have a password protected Microsoft Access file, and i need to run a Tableau Prep Flow which directly connects to that Access file. (I need to run it daily, for example)

Tableau Prep files do not store connection information such as passwords.

I can run Tableau Prep from the command line and give it a JSON file with the credentials, but so far from what i've been learning it doesn't support Access.

So my idea/solution so far would be (in a python script): 1-

  1. Remove the password from the Access file (how?)
  2. Run the batch file to play Tableau Prep from the command line
  3. Restore the password

My main issue here is not knowing how to remove the password of an Access DB using a Python script, so i can fully automate the process of running a Tableau Prep file.

Upvotes: 3

Views: 869

Answers (1)

Erik A
Erik A

Reputation: 32682

You can use pyodbc and the ALTER DATABASE PASSWORD command (thanks to HansUp to pointing that out!).

This only works through an exclusive connection, with extended ANSI SQL enabled.

You can use the following code:

import pyodbc
constr = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Path\To\Db.accdb;Exclusive=1;Pwd=SuperSecurePassword;ExtendedAnsiSQL=1;' 
#Exclusive connection, with password and extended ANSI SQL
cnxn = pyodbc.connect(constr)
crsr = cnxn.cursor()
#Decrypt
crsr.execute('ALTER DATABASE PASSWORD NULL SuperSecurePassword')
cnxn.commit()
cnxn.close()

#Perform operations here

constr = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Path\To\Db.accdb;Exclusive=1;ExtendedAnsiSQL=1;' 
#Exclusive connection, extended ANSI SQL, no password
cnxn = pyodbc.connect(constr)
crsr = cnxn.cursor()
#Encrypt
crsr.execute('ALTER DATABASE PASSWORD SuperSecurePassword NULL')
cnxn.commit()
cnxn.close()

Alternately, you can use COM to encrypt/dencrypt your database.

import win32com.client as comclient
import os
DBEngine = comclient.Dispatch("DAO.DBEngine.120")
dbLangGeneral = ';LANGID=0x0409;CP=1252;COUNTRY=0'

#Create a copy without a password
DBEngine.CompactDatabase('Path_to_database', 'Path_to_copy', dbLangGeneral + ';PWD=', 0, ';PWD=MySuperSecretPass')

#Do stuff with copy

#Delete original
os.remove('Path_to_database')

#Create encrypted copy at location of original
DBEngine.CompactDatabase('Path_to_copy', 'Path_to_database', dbLangGeneral + ';PWD=MySuperSecretPass')
os.remove('Path_to_copy')

This makes a compacted unencrypted copy of the database, then performs the operations, and then compacts it again.

This code assumes you're using dbLangGeneral as your collation constant, you can switch that with a different collation when desired.

Upvotes: 4

Related Questions