0

I want to connect Python to MS-Access but can't install new software (programs/drivers) and don't have admin rights as I'm working on a company PC. Are there any workarounds?

I could get the pyodbc package but since my Python is 64-bit and MS-Access is 32-bit, I'm expecting that this is the source for errors. Indeed, there are no 64-bit MS-Access Drivers and just an SQL server:

import pyodbc
print(pyodbc.drivers.())
>>> ['SQL Server']

The resulting error is the following:

import pyodbc
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' 
                      r'DBQ=path\file.accdb;')

>>> InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source 
    name not found and no default driver specified (0) (SQLDriverConnect)')

As mentioned before, I can neither install 32-bit Python nor the 64-bit drivers (at least it seems that I can't). I tried setting up a new environment in order to install 32-bit Python according to this answer by Mike Müller but it resulted in a "CondaHTTPError", which I wasn't able to fix - the answers here didn't work for me.

Is connecting pyodbc to the 32-bit drivers an option? From swashek's answer, this might work

%windir%\SysWOW64\odbcad32.exe

but I'm not sure where or how to apply it in Python.

After connecting Python to MS-Access, my ultimate goal is to extract Access Queries in order to duplicate tables in Python, which were created in Access based on Access data. Is there any completely different way or approach?

Further info: I'm working with Spyder and Anaconda 64-bit.

Clapham
  • 351
  • 2
  • 9

2 Answers2

1

64-bit applications like 64-bit Python can not use 32-bit drivers, and 32-bit applications can not use 64-bit drivers. It is simply impossible.

You have Microsoft Office Professional Plus 2013 on your machine and the Access driver does not appear when you do pyodbc.drivers() from 64-bit Python, so we can assume that you have the 32-bit version of Office.

So the most straightforward solution would be to use 32-bit Python with the 32-bit ACE driver (Microsoft Access Driver (*.mdb, *.accdb)) that Office installed.

Alternatively, you could switch from 32-bit Office 2013 to 64-bit Office 2013 and then your 64-bit Python could use the 64-bit ACE driver.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you for the clarification. I will then ask the IT department for permission to install either of these programs. Since this might take longer or might be rejected, is there an option which doesn't require a download from the internet? And is it generally the necessary way to go in order to extract Access Queries? – Clapham Jul 03 '19 at 17:34
  • Well, you *could* use the Access UI to dump the tables out to CSV and then use Python's built-in SQLite support to **(1)** write CREATE TABLE statements to create SQLite tables and import the data, **(2)** write CREATE VIEW statements to create SQLite views corresponding to the Access saved SELECT queries, (NB: Access SQL has quite a few quirks, so translating the SQL from Access to SQLite could be non-trivial) and then **(3)** run the queries from your Python app against the SQLite database instead of the Access database. – Gord Thompson Jul 03 '19 at 18:36
  • Thanks for this suggestion. I'll give it a try! – Clapham Jul 04 '19 at 07:51
0

I encountered a similar case where the machine was configured restrictively and is inside a corporate firewall. We cannot install or modify any applications that is already configured for the corporate setup. You also mentioned you encountered a "CondaHTTPError". This might mean that your applications are blocked to connect to the internet. I also assume your workstation is has Anaconda installed as part of your company's You may need to find out your company's proxy server where your IE connects to so you can make conda use it as well.

If for some reason your IT cannot provide it to you and if you can access the internet via Internet Explorer, chances are it has an automatic configuration script or proxy server set in it. Check out Tools -> Internet Options in your IE browser. Then on the Connections tab, click the LAN Settings button. Either:

  • you should see an address in the textbox under "Use automatic configuration script
  • or see an address under Proxy Server.

In case those textboxes are disabled and the address is too long to read, check if you can open your machine's Registry Editor (Win+r -> regedit).

If IE has Automatic Configuration Script:

  • navigate to HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings and look for AutoConfigURL.
  • Copy that address and paste it in your browser. It either downloads or lets you open a file. Open it in Notepad.
  • Depending on the contents of the file, you may be able to locate URLs or IP addresses that is used to redirect traffic to that particular address. Take note of those addresses.

If you now have your proxy server URL, you can execute the answer you found:

http_proxy=<url>
https_proxy=<url>
set CONDA_FORCE_32BIT=1
conda create -n py27_32 python=2.7

I was also able to use this to install libraries from pip that I cannot previously install.

pip --proxy <url> install xlwings
Czerald
  • 11
  • 3