Make Table Query limitation

I am using Office 2003 on Windows XP.

I love the make-table query (Select...Into) that is so championed by Jamie
Collins, and at first I viewed it as a potential panacea to all my data
transfer woes. But in working with the MTQ with Oracle I seem to have quickly
found a limitation - at least on my system.

I find that when running a MTQ in MS-Excel against Oracle into an MS-Access
table, there is roughly a 10,000 record ceiling. If a query returns many more
records than that (and also sometimes several thousand fewer) the query may:

Just quit mid-stream without generating any errors:

1. Sometimes some of the records are returned;
2. Sometimes no records are returned;
3. Seldom times (about 2 out of 10 attempts) the query actually completes.

I have the ADO CommandTimeout set to zero. The query tested returns 57,623
rows/records by 22 columns/fields and when successful, it runs in about 6
minutes. I have noticed in clocking it when using a recordset, that it
usually takes about 3 minutes to return the data. Coincidentally, if the make
table query just quits - it is usually at about the 3 minute mark - i.e. when
it is time to make the table in Access.

Has anyone else confronted issues such as this? Could this be the result of
my code structure/syntax (I can post the SQL if anyone wants to see it)?
Could it be related to our network and traffic issues? Any ideas?

Thanks much for reading all this, but I couldn't find a way to make it more
brief without losing a complete explanation. Thanks in advance for your
replies.

quartz wrote:
> I find that when running a MTQ in MS-Excel against Oracle into an MS-Access
> table, there is roughly a 10,000 record ceiling.

How are you connecting to the databases? Presumably your ADO connection
is to the target Access database. Are you connecting to Oracle via an
odbc connection embedded in the commandtext e.g.

SELECT *
INTO NewAccessTable
FROM [odbc;Driver=etc etc].OracleTable;

Are you using linked tables at all? Are you querying Oracle base
tables/VIEWs or invoking a stored proc?

Jamie.

--

quartz wrote:
> I am using Office 2003 on Windows XP.

Which version of Jet are you using (e.g. Jet 4.0)? Have you checked the
odbc connection timeout in the registry e.g.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

Jamie.

--