topic Connect to MS Access database with python script in Python Questions

Connect to MS Access database with python script

MihaKlemencic — Sat, 11 Dec 2021 20:04:09 GMT

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

Re: Connect to MS Access database with python script

RandyBurton — Thu, 10 May 2018 19:05:04 GMT

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:

How to connect MS Access to Python using pyodbc,

Connection modules for Microsoft SQL databases and

Step 1: Configure development environment for pyodbc Python development

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.

Re: Connect to MS Access database with python script

JoshuaBixby — Thu, 10 May 2018 19:33:40 GMT

Just use the first pyodbc link Randy Burton‌ provides.

Re: Connect to MS Access database with python script

TedKowal — Fri, 11 May 2018 16:06:48 GMT

This video help me the first time I used pyodbc... Python connection to Access Database using pypyodbc - YouTube

Re: Connect to MS Access database with python script

MihaKlemencic — Sat, 12 May 2018 12:00:36 GMT

Thank you for answers

I checked and pyodbc is not installed.

Alternative way is to export MS Access table to .dbf, use arcpy.SearchCursor and get certain row value.

Re: Connect to MS Access database with python script

JoshuaBixby — Sat, 12 May 2018 13:34:35 GMT

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.

Re: Connect to MS Access database with python script

MihaKlemencic — Mon, 14 May 2018 15:37:04 GMT

It's working!