0

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.)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
corinna
  • 629
  • 7
  • 18
  • When you open the .mde file are you prompted for a username and password, or just a password? – Gord Thompson Nov 10 '15 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 Nov 10 '15 at 13:50
  • yes and yes. I just wonder how to circumvent the GUI. – corinna Nov 10 '15 at 13:54
  • 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](http://stackoverflow.com/questions/23900858/pyodbc-error-im002-im002-unixodbcdriver-managerdata-source-name-not), but there's as UNIX solution given – corinna Nov 10 '15 at 14:49
  • Are you running 64-bit Python? – Gord Thompson Nov 10 '15 at 14:56
  • No, 32-bit Python on 64-bit system. Might this cause the problem? – corinna Nov 10 '15 at 15:01
  • No, your 32-bit Python is good. It seems I gave you a bad link above, sorry. Your connection string should be like this: `Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb; SystemDB=C:\mydatabase.mdw;UID=yourUserName;PWD=yourPassword` – Gord Thompson Nov 10 '15 at 15:05
  • Thanks a lot so far. Actually, I didn't use the SystemDB argument. However, I wonder if there exists a .mdw file at all. For sure, it's not located where the .mdb file is. [This] (http://stackoverflow.com/questions/31388811/how-to-connect-to-ms-access-97-with-workgroup-mdw-database-using-pyodbc) suggests that the .mdw file is located in the same directory as the MsAccess installation, but can not find it there. – corinna Nov 12 '15 at 10:28

1 Answers1

2

Since you are using 32-bit Python on Windows you can simply use or 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:

  1. 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)}.

  2. 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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 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. – corinna Nov 13 '15 at 15:40
  • @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. – Gord Thompson Nov 13 '15 at 15:43