Unanswered: Issues with MS Access 2010 ODBC connections on Windows 7 64 bit OS
I have an MS Access 2010 accde application in 60 + counties. This project has been ongoing for about 10 years. Each user has the FE accde on their desktop. I update the users from the web. The back end data is in Sql Server 2008 Express and the FE connects via a DSN-less ODBC connection I create in my code.
My problem is with Windows 7 64 bit. A search across 100,000 + records that takes 2 seconds in Windows 7 32 bit can take up to 4 or 5 minutes on Windows 7 64 bit.
I can put the FE on Windows XP and it runs quickly. It seems as if Windows 7 64 bit is stomping on the ODBC connection somehow.
I have used both the 32 and the 64 bit ODBC administrator tool to create ODBCs without any improvement. I dropped the DSN-less connection code and linked the tables via the 32 and 64 bit ODBC files with the Linked Table Manager. No improvement. The files compile. I have decompiled without improvement. The behavior is universally slow, not localized to a couple of forms or tables.
My MS Access 2010 is Version 14.0.5128.5000 (32 bit).
Any suggestions, guidance, pleas to a higher deity, anything would be greatly appreciated. I have additional clients I planned on moving to MS Access 2010 and converting the back end data to Sql Server but this slowness has me completely baffled.
I know you are not using Oracle for your database, but this might be worth looking into for you.
I just spent a week on this issue and I hope this helps anyone still having the problem. If you have a slow connection with windows 7 and access, but you have a fast connection with windows XP and access, then this may help.
It does not matter if you are using 32bit or 64 bit, the problem is with windows 7.
If you are using windows 7 and access to connect to Oracle through an ODBC connection I might have an answer for you.
If you connect to any other database through an ODBC and have a problem with windows 7, Check the trace and look for how the SQL is asking for the primary key or index. The solution I list here is for oracle, but you might be having a very similar problem. If you find something like
ParameterType SQLSMALLINT 0x0006 = SQL_FLOAT
start looking into data types of the primary key in your database.
Make sure the database is not casting a 64-bit double into a 32-bit single precision float.
Check the datatype of the primary key or index in the oracle table with slow access.
If the column is of type NUMBER without anything else after it, this could be your issue.
The solution is to change the datatype of the primary key or index columns to NUMBER(15,0) This is assuming your primary key does not need decimal points.
Below will be what I believe the problem to be and how this fixes the problem for windows7.
It seems to be that windows XP and access send a request to the oracle database treating the the index as SQL_DOUBLE, which access can handle just fine.
Windows 7 and access send a request to the oracle database treating the index as SQL_FLOAT.
This is where I believe the problem happens.
Oracle has to cast the index from a double(I know oracle doesn't have double for a data type, but NUMBER defaults to NUMBER(9,2) and stores it like a double), which is a double precision 64-bit floating point data type into a float, which is a single precision 32-bit floating point number, and when access gets the data from the ODBC, it converts it back to a double.
It is very CPU intensive for Oracle to convert 64-bit values into 32-bit values before replying to a query.
When oracle has the primary key stored as a data type number(15,0), windows 7 and access send a request to the oracle database treating the primary key as a decimal with 15 digits and 0 decimals. The data type decimal is a 128-bit floating point data type.
Basically this means that oracle does not have to cram a 64 bit value into a 32bit value before sending it back to who ever requested the data as a float.
I have ODBC trace files that indicate these results.