I am writing a python script. In certain part of the script I would like to create a connection to MS Access database. Based on argument the script would read ID of the municipality and use it for other operations.
Below is the part of JS script which connects and reads data from MS Access Database and I would like to use it in python script.
appDB = WScript.CreateObject("ADODB.Connection"); appDB.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Database);//path to database is already defined at the begining of code str = "SELECT mun.id, mun.alias FROM mun WHERE (mun.alias)='" + municipality + "';"; appRS = appDB.Execute(str); midOb = appRS("id").Value; appRS.close; appDB.close; #WScript.Echo(alias); #WScript.Echo(idMun); #WScript.Echo(mun_code);
ArcGIS version: 9.1-9.3. I hope you remember old times
Another thread was asking a similar question for Pro: Connecting MS Access to ArcGIS Pro but there was no solution provided.
Perhaps the pyodbc module will provide the connection you require. See:
As an alternative (using Desktop/Catalog not Pro), I have created a new personal geodatabase (which uses the .mdb Access extension). In Access, I can then copy tables from newer .accdb databases into the .mdb version, usually without loss of data. Then the table can be accessed in the personal geodatabase using the usual arcpy tools that are used to process file geodatabases.
It is also possible to copy features/tables from a file geodatabase into the personal geodatabase using Catalog. Then using Access, the table can be copied from the .mdb version to the newer .accdb version.
Installing Python packages is quite easy. Are you not able to install one (locked down machine) or have you just never done it? If the latter, I encourage you to install pyodbc and work with it.
If you do go with exporting to DBF and using ArcPy cursors, use the What is the data access module?—Help | ArcGIS Desktop cursors, not the older cursors.