hindzeit
hindzeit

Reputation: 21

I can no longer use SQLAlchemy to connect to Access DSN, what is causing this error?

First time poster, so please go easy on me :)

I run a daily script that references tables in an MS Access database. A week ago it stopped working,

I got a strange error, which I have isolated to the below code. MyDSN is a 64-bit MS Access data source. The error results in a fatal exception, but I'm not sure where to start.

import pandas as pd
df_date = pd.read_sql_table('DIM Date','access+pyodbc://MyDSN')

The error output is shown in the below section.

At one point along the process, I got a dialog box error message, something to do with dll's. I'm not sure if it's a related message or not. Error Dialog Box Screenshot

I tried downloading the missing file from www.dll-files.com (I only did a cursory legitimacy check). I registered the file using Run and regsvr32, but the result was unchanged.

I've also tried creating a new environment to run this code, and the output was unchanged. Here is the conda code setting up the new environment. I'm running Python 3.10.8 64-bit. I installed these all using the default directory: C:\WINDOWS\system32.

conda create --name bound2
conda activate bound2
conda install spyder
conda install pandas
conda install sqlalchemy
conda install pip
pip install sqlalchemy-access
Windows fatal exception: code 0xc0000138


Main thread:
Current thread 0x00002610 (most recent call first):
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 88 in _GetGoodDispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 106 in _GetGoodDispatchAndUserName
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\__init__.py", line 117 in Dispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy_access\base.py", line 791 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 55 in cache
  File "<string>", line 2 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 528 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 921 in _reflect_pk
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 791 in reflect_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 721 in _autoload
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 686 in _init
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 611 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\util\deprecations.py", line 309 in warned
  File "<string>", line 2 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1770 in get_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 808 in __init__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1459 in read_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 286 in read_sql_table
  File "c:\users\willi\onedrive - cooperfamilyoffice.com.au\will folder\scripts\_debug\scrape xero bank troubleshoot.py", line 11 in <module>
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\py3compat.py", line 356 in compat_exec
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 469 in exec_code
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 611 in _exec_file
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 524 in runfile
  File "C:\Users\willi\AppData\Local\Temp\ipykernel_5564\993795846.py", line 1 in <module>


Main thread:
Current thread 0x00002610 (most recent call first):
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 88 in _GetGoodDispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 106 in _GetGoodDispatchAndUserName
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\__init__.py", line 117 in Dispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy_access\base.py", line 791 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 55 in cache
  File "<string>", line 2 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 528 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 921 in _reflect_pk
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 791 in reflect_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 721 in _autoload
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 686 in _init
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 611 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\util\deprecations.py", line 309 in warned
  File "<string>", line 2 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1770 in get_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 808 in __init__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1459 in read_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 286 in read_sql_table
  File "c:\users\willi\onedrive - cooperfamilyoffice.com.au\will folder\scripts\_debug\scrape xero bank troubleshoot.py", line 11 in <module>
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\py3compat.py", line 356 in compat_exec
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 469 in exec_code
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 611 in _exec_file
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 524 in runfile
  File "C:\Users\willi\AppData\Local\Temp\ipykernel_5564\993795846.py", line 1 in <module>


Main thread:
Current thread 0x00002610 (most recent call first):
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 88 in _GetGoodDispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\dynamic.py", line 106 in _GetGoodDispatchAndUserName
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\win32com\client\__init__.py", line 117 in Dispatch
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy_access\base.py", line 791 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 55 in cache
  File "<string>", line 2 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 528 in get_pk_constraint
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 921 in _reflect_pk
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\engine\reflection.py", line 791 in reflect_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 721 in _autoload
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 686 in _init
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\sql\schema.py", line 611 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\sqlalchemy\util\deprecations.py", line 309 in warned
  File "<string>", line 2 in __new__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1770 in get_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 808 in __init__
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 1459 in read_table
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\pandas\io\sql.py", line 286 in read_sql_table
  File "c:\users\willi\onedrive - cooperfamilyoffice.com.au\will folder\scripts\_debug\scrape xero bank troubleshoot.py", line 11 in <module>
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\py3compat.py", line 356 in compat_exec
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 469 in exec_code
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 611 in _exec_file
  File "C:\ProgramData\Anaconda3\envs\bound2\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 524 in runfile
  File "C:\Users\willi\AppData\Local\Temp\ipykernel_5564\993795846.py", line 1 in <module>


Restarting kernel...

Upvotes: 0

Views: 213

Answers (1)

hindzeit
hindzeit

Reputation: 21

Resolved, seconds after I hit post.

I tried repairing the installation of the "Microsoft Access Driver" for DSNs via the Microsoft website. You can download an installer for the "Microsoft Access Database Engine 2016 Redistributable" and repair the installation.

It worked, apologies for the false alarm.

Upvotes: 2

Related Questions