Another - The search key was not found in any record - Issue

[Eric D via AccessMonster.com]

I'm using MS Access 2003 on Windows XP.
I've exported data from DB2 into 4 files using ascii text format.
I'm trying to import this data into MS Access and am running into the "Search
key was not found" problem.
I created a new database, turned off the AutoCorrect and can import two of
the text files before getting this error. If I create another db and import
the one that failed in the previous copy, it imports without issue. The
Compact and Repair does not resolve the issue either.

What I've found is that I probably have way too much data for Access to
handle. The four ascii files combine to provide about 60 million records.
Each record is approximately 60 bytes in length and once I import about 30
million records, I'm hitting the upper limits of MS Access (2 gig). (not sure
the 2 gig limit is fact)

This is a real bummer as 60 million records is a short month's worth and I
need to store 18 months worth of data for trending history. My company does
not want to spend the money for a front-end reporting tool that can interface
with DB2 and multiple people need to view this data on a periodic basis,
creating adhoc views and reports.

I added my findings on this website as I see MANY similiar reported issues
and thought that my findings might help others with the same problem.

 

[George Nicholson]

(not sure the 2 gig limit is fact)

It is.

 

[Tony Toews]

I'm using MS Access 2003 on Windows XP.
I've exported data from DB2 into 4 files using ascii text format.
I'm trying to import this data into MS Access and am running into the "Search
key was not found" problem.
I created a new database, turned off the AutoCorrect and can import two of
the text files before getting this error. If I create another db and import
the one that failed in the previous copy, it imports without issue. The
Compact and Repair does not resolve the issue either.

What I've found is that I probably have way too much data for Access to
handle. The four ascii files combine to provide about 60 million records.
Each record is approximately 60 bytes in length and once I import about 30
million records, I'm hitting the upper limits of MS Access (2 gig). (not sure
the 2 gig limit is fact)

That's a pile of data.

This is a real bummer as 60 million records is a short month's worth and I
need to store 18 months worth of data for trending history. My company does
not want to spend the money for a front-end reporting tool that can interface
with DB2 and multiple people need to view this data on a periodic basis,
creating adhoc views and reports.

Access is an excellent front end reporting tool. But, in your case,
you almost certainly want to use your current data storage system, ie
DB2. For that volume of records it would be far better at
manipulating the data that Access would. Besides Access chokes on a
small fraction of that volume of records.

Now presumably you really need to slice and dice that data and get
nice summaries out of it. Now I'm not at all sure what the
equivalent in DB2 is but in SQL Server these are called views. You
can summarize data quite nicely with these. The advantage is that DB2
now does all the work for you and passes back just the summary data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[ericdreczko via AccessMonster.com]

Tony... yeah, views are used in DB2 also.
Problem I'm having with this is that even using DB2 to slice/dice the data,
every time you open a view, you are running the query that creates the view.
With 150 million records, this is very slow. I'm having difficulty convincing
users that if they wait 20 min ... or longer, the data will eventually
display....
With two months of data stored, my nightly backups take up about 25 gig. The
person who architected this setup did not size the project correctly and I
only have 68 gig of space available for backups.... about that for the
database.... and, again, I'm expected to store 18 months worth for history.
Fun stuff!

I'm using MS Access 2003 on Windows XP.
I've exported data from DB2 into 4 files using ascii text format.

[quoted text clipped - 10 lines]

million records, I'm hitting the upper limits of MS Access (2 gig). (not sure
the 2 gig limit is fact)

That's a pile of data.

This is a real bummer as 60 million records is a short month's worth and I
need to store 18 months worth of data for trending history. My company does
not want to spend the money for a front-end reporting tool that can interface
with DB2 and multiple people need to view this data on a periodic basis,
creating adhoc views and reports.

Access is an excellent front end reporting tool. But, in your case,
you almost certainly want to use your current data storage system, ie
DB2. For that volume of records it would be far better at
manipulating the data that Access would. Besides Access chokes on a
small fraction of that volume of records.

Now presumably you really need to slice and dice that data and get
nice summaries out of it. Now I'm not at all sure what the
equivalent in DB2 is but in SQL Server these are called views. You
can summarize data quite nicely with these. The advantage is that DB2
now does all the work for you and passes back just the summary data.

Tony

 

[Tony Toews]

Problem I'm having with this is that even using DB2 to slice/dice the data,
every time you open a view, you are running the query that creates the view.
With 150 million records, this is very slow. I'm having difficulty convincing
users that if they wait 20 min ... or longer, the data will eventually
display....

Access and I'm pretty sure SQL Server will automatically display the
first X records, just enough to fill in the screen so you can start
looking at things. I'm somewhat surprised DB2 doesn't do the same
thing. Does DB2 have something similar if you use their tools to
view the data?

With two months of data stored, my nightly backups take up about 25 gig. The
person who architected this setup did not size the project correctly and I
only have 68 gig of space available for backups.... about that for the
database.... and, again, I'm expected to store 18 months worth for history.
Fun stuff!

Sounds like your corp doesn't want to spend a bit of money on this.
Anyhow my sympathies for your troubles.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm