My company has an Oracle back-end that is linked via ODBC to an MS Access 2003 front-end. It usedta be linked to a MS SQL server backend but due some factors it will be linked an Oracle Db in the near future. The problem is Oracle has several large tables (over 2.5 million in one case, 12 million another) in which some field are data type number with w/precision length 38. When these tables are fed in MS Access 2003 via an ODBC. Access sets these fields to text by default. It can't handle chars larger than 10 for precision length in a number data type field. I need to return the data type to number in Access. I can't change it directly as the ODBC will not allow linked tables to be modified.
Okay, I have found a way to avoid the ODBC and linked tables from Oracle to Access 2003. I will use replicated tables of what is in the Oracle database and then modify them. I simply recreated them. Now the problem is it seems is Access 2003 can't handle a dataset as large as what I have: 2.3 million records. Is there a way to finesse this? I don't wanna break up a table into smaller ones. It may be that a later version of Access is needed. I hope this is the solution.