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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Connect to MS Access database with python script

MihaKlemencic

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 <SPAN class="operator token">=</SPAN> WScript<SPAN class="punctuation token">.</SPAN><SPAN class="token function">CreateObject</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"ADODB.Connection"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>&nbsp;&nbsp;&nbsp;&nbsp; 
appDB<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Open</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Provider=Microsoft.Jet.OLEDB.4.0; Data Source="</SPAN> <SPAN class="operator token">+</SPAN> Database<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="comment token">//path to database is already defined at the begining of code&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>
str <SPAN class="operator token">=</SPAN> <SPAN class="string token">"SELECT mun.id, mun.alias FROM mun WHERE (mun.alias)='"</SPAN> <SPAN class="operator token">+</SPAN> municipality <SPAN class="operator token">+</SPAN> <SPAN class="string token">"';"</SPAN><SPAN class="punctuation token">;</SPAN>
appRS <SPAN class="operator token">=</SPAN> appDB<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Execute</SPAN><SPAN class="punctuation token">(</SPAN>str<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
midOb <SPAN class="operator token">=</SPAN> <SPAN class="token function">appRS</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"id"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>Value<SPAN class="punctuation token">;</SPAN>
appRS<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">;</SPAN>
appDB<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">;</SPAN>


#WScript<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Echo</SPAN><SPAN class="punctuation token">(</SPAN>alias<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
#WScript<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Echo</SPAN><SPAN class="punctuation token">(</SPAN>idMun<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
#WScript<SPAN class="punctuation token">.</SPAN><SPAN class="token function">Echo</SPAN><SPAN class="punctuation token">(</SPAN>mun_code<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

ArcGIS version: 9.1-9.3. I hope you remember old times

Find more posts tagged with

Accepted answers

All comments

MihaKlemencic

It's working!

bixb0012

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.

MihaKlemencic

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.

TedKowal

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

bixb0012

Just use the first pyodbc link Randy Burton‌ provides.

RandyBurton

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.