I'm using Access 2003 under Windows XP.
I have a very large linked table that is growing daily, and will soon exceed the 2Gb size limit of a single MDB file. So, my management have told me to host this table in their big Oracle system that does not have this limit.
However, there are many hundreds of Excel spreadsheets and tens of other Access databases on the network that explicitly use this linked table - so we do not want to change its referenced name and location if possible.
So, we thought we could keep the current linked-table's MDB file, and replace the local data table with a link to the replacement Oracle table, so that everything would look the same, and nothing external would need to be changed.
Unfortunately, Access does not let you link to a linked table - you have to link to the actual table directly.
So, we thought that we could fool it by writing an Access pass-through query in the current linked-table's MDB file that would SELECT * from the Oracle table - and name the Access pass-through query the same as the original table.
This all works fine if you open the pass-through query or write a test SQL query against it - though I suspect we may hit the 2Gb problem anyway, if it tries to make a temp table.
However, there seems to be a "feature" in Access that when you try to make an external link to that query, the Linked Table Manager says that "The Microsoft Jet database engine cannot find the input table or query 'MyTableQuery'. Make sure it exists and that its name is spelled correctly".
But, the query is definitely there and definitely spelled correctly. I'm copying and pasting the location directly from the address bar in the Explorer window, so I'm not using the wrong database. And, it must be possible to link to queries, else it wouldn't refer to queries in the error message.
I've even tried remaking the database using the undocumented /decompile MSACCESS.EXE command switch to be sure there aren't any dud references lying about.
Can anyone help?
Ron
(Access 2003 under Windows XP)
I have a very large linked table that is growing daily, and will soon exceed the 2Gb size limit of a single MDB file. So, my management have told me to host this table in their big Oracle system that does not have this limit.
However, there are many hundreds of Excel spreadsheets and tens of other Access databases on the network that explicitly use this linked table - so we do not want to change its referenced name and location if possible.
So, we thought we could keep the current linked-table's MDB file, and replace the local data table with a link to the replacement Oracle table, so that everything would look the same, and nothing external would need to be changed.
Unfortunately, Access does not let you link to a linked table - you have to link to the actual table directly.
So, we thought that we could fool it by writing an Access pass-through query in the current linked-table's MDB file that would SELECT * from the Oracle table - and name the Access pass-through query the same as the original table.
This all works fine if you open the pass-through query or write a test SQL query against it - though I suspect we may hit the 2Gb problem anyway, if it tries to make a temp table.
However, there seems to be a "feature" in Access that when you try to make an external link to that query, the Linked Table Manager says that "The Microsoft Jet database engine cannot find the input table or query 'MyTableQuery'. Make sure it exists and that its name is spelled correctly".
But, the query is definitely there and definitely spelled correctly. I'm copying and pasting the location directly from the address bar in the Explorer window, so I'm not using the wrong database. And, it must be possible to link to queries, else it wouldn't refer to queries in the error message.
I've even tried remaking the database using the undocumented /decompile MSACCESS.EXE command switch to be sure there aren't any dud references lying about.
Can anyone help?
Ron
(Access 2003 under Windows XP)
