I have an intractable problem with Oracle 10g. At my company we are replacing a MS SQL back-end with Oracle 10g. It's thru a consultancy, so we don't have control over the specifications they set-up. One spec they set is that the data type number is made 38 characters for precision length. We connect thru an ODBC (right now in the test phase only). When the connection is made, our MS Access 2003 front-end can't convert a number data type with this large precision length for number of characters. Access 2003 by default sets such number types to text. Therein is our problem. We need to force thru the ODBC connection the restraint that number types of this character size remain number in MS Access.
So far, I've tried recreating the tables in Access with a number data type after the ODBC connects and feeds data. This doesn't work for various reasons. One big reason is large tables can't be changed due to memory. Worst than this, if we leave it as is, then the queries and macros that run them will take very long completion times.
I am looking for a tool that might handle this.