Using Python to manipulate an Access database protected with UserName and Password

Question

I have to work with a MS Access DB where I can insert and modify data manually via GUI. The GUI opens with execution of an .mde file which prompts for UserName and Password.

Of course, I have no DB admin rights. I also have no admin rights for my system. I am using 32-bit Python on 64-bit Windows 7.

Now my question:

Is there a possibility to access the .mdb file via command line in order to integrate data / change data automatically?

(Pythonic solutions would be appreciated.)

When you open the .mde file are you prompted for a username and password, or just a password? — Gord Thompson
– Gord Thompson, Commented Nov 10, 2015 at 13:44
Do you have a username/password combination that works? If so, does it have sufficient permissions to accomplish what you want to do? — Gord Thompson
– Gord Thompson, Commented Nov 10, 2015 at 13:50
Yes, pyodbc.Error: ('IM002', '[IM002] [ODBC][Driver Manager]Data source name not found, and no default driver specified ') as well as pypyodbc.Error: ('IM002', '[IM002] [ODBC][Driver Manager]Data source name not found, and no default driver specified ') similar to here, but there's as UNIX solution given — corinna
– corinna, Commented Nov 10, 2015 at 14:49

Gord Thompson · Accepted Answer · 2015-11-13 15:45:12Z

2

Since you are using 32-bit Python on Windows you can simply use pypyodbc or pyodbc and the Microsoft Jet ODBC driver. The connection string you will need to use will be of the form

connStr = (
    r"Driver={Microsoft Access Driver (*.mdb)};"
    r"Dbq=C:\whatever\mydatabase.mdb;"
    r"SystemDB=C:\whatever\mydatabase.mdw;"
    r"UID=yourUserName;"
    r"PWD=yourPassword;"
    )

Notes:

For 64-bit Python you would have to use 64-bit version of the newer Microsoft Access Database Engine (a.k.a. "ACE") and Driver={Microsoft Access Driver (*.mdb, *.accdb)}.
The SystemDB parameter could be omitted if the database in question uses the default System Workgroup to manage UserNames and Passwords. The default System Workgroup file is named "system.mdw" and is located in either the "%APPDATA%\Microsoft\Access\" or "%windir%\system32\" folder.

edited Nov 13, 2015 at 15:45

answered Nov 12, 2015 at 15:50

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

corinna Over a year ago

Thanks a lot for answering and editing. However, it's still not working due to the fact that I'm unable to install ACE or ODBC driver on my system, but of course that's not your fault. ...will try ucanaccess now. Nevertehless, I've learned something: I didn't realize before that ODBC or ACE are required additionally to pyodbc/pypyodcb.

Gord Thompson Over a year ago

@corinna - The Microsoft "Jet" ODBC driver (as in the code sample above) is installed as part of Windows, and it is all you should need if you are indeed running 32-bit Python.

Collectives™ on Stack Overflow

Using Python to manipulate an Access database protected with UserName and Password

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related