I know it's a dying technology, but I still get a lot of value out of Microsoft Access manipulation of data stored in Oracle tables. I have a Windows 2003 server VM that runs Access 2003 and has 32 bit driver access to our corporate Oracle tables.
We're moving to Windows 7 (finally, and boy is Win 7 ugly), so in addition to my ancient VM I looked into using Access 2007 with those tables. The first thing I discovered was the 64bit Oracle 10.x installer didn't install the expected Oracle ODBC drivers. I also noticed that Windows no longer includes Microsoft's ODBC driver ...
Data Access Technologies Road MapI spent a few minutes poking around the net, and it looks like there's no easy way to install Oracle's drivers into 64bit Windows. Indeed, it's not clear they exist. I'll do a bit more research with some more focused searches and post an update here.... Oracle ODBC and Oracle OLE DB: The Microsoft Oracle ODBC Driver (Oracle ODBC) and Microsoft OLE DB Provider for Oracle (Oracle OLE DB) provide access to Oracle database servers. They are built by using Oracle Call Interface (OCI) version 7 and provide full support for Oracle 7. Also, it uses Oracle 7 emulation to provide limited support for Oracle 8 databases. Oracle no longer supports applications that use OCI version 7 calls. These technologies are deprecated. If you are using Oracle data sources, you should migrate to Oracle-supplied driver and provider....
Progress, as is usual, is a mixed bag. Access was an ugly, awkward Frankenstein application, but there's nothing like it for mixing and matching a wide range of disparate data sources. The deeply nested and reusable query modules could be horrendously slow -- but for what I do the performance was often adequate.
I'll miss Access.
Update: Superuser.com tells me that there exists a 64bit Oracle 11g driver. However, the user's unspecified version of Access is 32bit and it can't use the 64 bit driver. We're on Oracle 10g.
1 comment:
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.
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.
Post a Comment