Hi.
Have more than enough disk space (26 gigs).
Unfortunately, it doesn't matter how much disk space you have on the hard
drive. Jet 4.0 can only handle up to 2GB of data at a time. It's possible
that the one million records exceeds the amount Jet can handle and place in
the temporary file.
I'm not familiar with the Pervasive data transfer tool, but since you are
building queries with this tool from Oracle data, is it possible that you are
using a Cartesion join, instead of ANSI SQL-92 syntax for the join? For
example, it's valid in Oracle to use the following:
SELECT *
FROM EMPS, DEPT
WHERE EMPS.DEPTNO = DEPT.DEPTNO;
This will result in a Cartesion join in Access, whereas:
SELECT *
FROM EMPS INNER JOIN DEPT ON EMPS.DEPTNO = DEPT.DEPTNO;
uses ANSI SQL-92 syntax and will return only the matching records -- which
can be _immensely_ fewer records -- depending upon how many records are in
each table.
Other items to check are the amount of RAM, the pagefile size (it should be
big enough to handle the whole transaction), enough quota available in the
system's Temp directory (you may want to clean out the Temp directory before
beginning the session so you have the entire quota available), and increasing
the MaxLocksPerFile value in the Windows Registry. For this last item,
please see the following Web page:
http://support.microsoft.com/?id=286153
If none of this helps, you may need to cut down the total number of records
in order to work with Jet. Perhaps the Pervasive data transfer tool can
accommodate this, but if not, see your Oracle DBA, who can help you with
handling the data in a more effective way, especially with Views and table
partitions.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.
