3

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.

Marsroverr
  • 556
  • 1
  • 6
  • 23
Ricardo Vilaça
  • 846
  • 1
  • 7
  • 18
  • 2
    Does this work when executed from your ODBC connection? `ALTER DATABASE PASSWORD '' 'oldpassword'` – HansUp May 22 '20 at 14:27
  • @ErikA Unfortunately I have only been able to make it work in an Access session from `CurrentProject.Connection`. If you can show him to do what he needs from Python, please do so. BTW, this is the one which worked best for me: `ALTER DATABASE PASSWORD Null oldpassword` – HansUp May 22 '20 at 16:50
  • 1
    @HansUp Thanks! I've added it to my answer. – Erik A May 22 '20 at 19:35

1 Answers1

4

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks! :D To be honest i actually already saw both answers like 3 or 4 hours ago, but i'm still stuck trying your method because i found it an interesting approach ahaha. HansUp's method works fine ;) Thanks for putting it in the answer aswell cause i didn't even knew i had to use "ExtendedAnsiSQL" and "Exclusive" methods. Regarding your own solution it's very interesting because i'm not sure why it isn't working. – Ricardo Vilaça May 22 '20 at 21:11
  • db = DBEngine.CompactDatabase(path_to_db, path_to_copy, ';pwd=NewPass', Options = ';PWD=MyPassword') - gives the following error: _ctypes.COMError: (-2146824867, None, ('Data type conversion error.', 'DAO.DbEngine', 'jeterr40.chm', 5003421, None)) – Ricardo Vilaça May 22 '20 at 21:18
  • I changed the parameters you gave me, because apparently password isn't a supported parameter anymore. – Ricardo Vilaça May 22 '20 at 21:21
  • @RicardoVilaça Microsoft sort-of messes up what it deprecates, at one point DAO was deprecated entirely but that's no longer the case. The password parameter is essential, because it's needed to compact an encrypted database. Anyway, I discovered Python messes up arguments by name for COM, fixed that by passing 0 as options, and also swapped comtypes for win32com as comtypes has issues on the latest Python version. The COM approach should now work fine. The conversion error was because you were passing a string as the options argument, while that should be an integer – Erik A May 23 '20 at 06:19