Unanswered: Issue Linked Table from Oracle to Access - Field Size
Currently I have linked tables in an Access 2003 database to an Oracle 9i database. The links are actually to views within Oracle and one of the columns is the Oracle ROWID aliased as OBJID in the view.
Access 2003 assigns a field size of 18 characters. When the same database is opened in Access 2010 and the linked table is refreshed the field size is defined in Access 2010 as 10 characters. This is causing an issue as the data is being trucated.
One other thing to note is Access 2003 is running on Win XP using Oracle 9i ODBC drivers whereas Access 2010 is running on Win 7 using Oracle 11g drivers since the 9i drivers are not compatible on Win 7.
Anyone have any ideas? How does Access determine what the field sizes should be when really no field size is defined in the Oracle view since this is the ROWID?
When you create a link in Access to a Table or View on a SQL Server, Access requests the necessary information (metadata) by querying the system tables or view of the server (sys.objects, sys.columns, etc.) then performs some translation to create the equivalent data types (e.g. a VARCHAR or NVARCHAR will be translated to a Text is it's less than 256 characters, to a Memo if it's more, an INTEGER will be translated to a Long Number, etc.). I don't know for sure how it works with Oracle, but a guess that a similar process is used.
Not sure which versions of Access this applies to, as none are listed, but based on the date I would say it Access 2007 or Access 2003 and earlier. In the article it states Text(18) for the ROWID which is what Access 2003 is doing, but Access 2010 is giving it the 10 characters now.
Found the issue or at least a solution. I was using the Oracle 11g drivers versioned at 11.02.00.01 for the ODBC entry. I recreated the ODBC entry using the Microsoft ODBC for Oracle versioned at 6.01.7901.17514 and that solved the problem.
Not sure if the problem is within Access or the Oracle driver, but regardless, found a solution.