Interfacing MS Access is too slow

[maurice]

Hello everyone

Has anyone experienced a very poor performance when using pyodbc to connect to a accdb database?

I think definitely my problem is with the Microsoft Access Driver, so this isn't the most likely forum to put this question up, but some of you might have dealt with this at some point.

For example, inserting 80k rows in a table takes about 5 minutes. Inside Access itself it is instant. 

I am using ODBC driver string to connect and wanted to use OLEDB provider for comparison but that throws an error "('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')" (It is installed but the script can not find it).

I don't know what else to try, so wanted to know if that's usually how pyodbc/ms access driver perform with an Access database. Thanks! 

[Big Stone]

hi,

You can prepare your 80k update in a csv file, then load it from Access.

Also, if your access database is on the network, it may not help.

http://stackoverflow.com/questions/11275356/vba-procedure-to-import-csv-file-into-access

You error "('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')" may come:

- from the use of ODBC instead of ADO,

- or from a 32 bit Driver vs Python-64 bit problem.

[pablo.ri...@gmail.com]

Hello! Forgive me, but I am on a previous step.  I am having difficulties to connect to Access. I have the drivers (PYODBC), etc, etc, but there is always an error, could you handle to me a sample code, so I can detect where do I screw up!.  Thanks very much...

[maurice]

hello,

pyodbc isnt really a driver but a python module. You would need a driver pre-installed.

You can get it here: https://www.microsoft.com/en-us/download/details.aspx?id=13255

To connect to the Access database you need to use a correct connection string, check https://www.connectionstrings.com/microsoft-access-accdb-odbc-driver/

About pyodbc: https://mkleehammer.github.io/pyodbc/

Maurice

[pablo.ri...@gmail.com]

Hello Maurice:

It did work!  I installed he drivers, and connected to database being able to fetch all data.   The links have been quite useful.

However, one more tip I would like to ask you, if you don`t mind.   I am trying to use some indications I got from online course at Datacamp.com.  They propose to use SQLAlchemy, and at some point you have to create an engine before the connection.  For same database I use:

from sqlalchemy import create_engine

engine = create_engine(r'access+pyodbc:///C:/Users/movpab01/Documents/INDICADORES.mdb?driver=Microsoft Access Driver (*.mdb, *.accdb)')

, and the error message is:

NoSuchModuleError: Can't load plugin: sqlalchemy.dialects:access.pyodbc

Any suggestion?

Thanks!