The search key was not found

[Avipatel]

Hi

I am trying to import a table from a notepad doc and i get an error message " The search key was not found" Please can someone explain to me what this is about and if there is a solution for it. Does this have anything to do with size of the database beign too large?

Thanks
Avi

 

[Moniker]

It has nothing to do with size restrictions. How are you trying to import?

 

[Avipatel]

Error Message - The search key cannot be found

Hi

I am importing using a notepad text file.

1. Right click
2. Select Import File
3 Choose file from drive
4. Set to delimited with Pipe character as the seperator (This is set in the
other field)
5. Choose first row as heading
6. Set data types
7. Let access determine primary key

This is the procedure i am using.

If this is wrong, please advise

 

[Moniker]

Don't let Access determine the primary key. It's looking for unique values there. You can let Access create a primary key for you (it will be an autonumber).

 

[Avipatel]

Thanks

I tried both ways. You are correct that when access creates the primary key its better. I think that Access has a 2GB limit to the data it can store. I have maxed this out and I have not completed the data imports yet. Perhaps I should be using a different database. Any suggesstion? I am thinking SQL Server but am still waiting for my project manager to get back to me.

Thanks
Avi

 

[Moniker]

It's very difficult to reach the 2GB limit, especially if you're just importing text into a table. How many records is it? What else is in the DB? When was the last time you did a compact/repair?

Anyway, if it really is that much data, you'll have to upscale to SQL Server, MySQL, or the more robust DB of your choice. You can also just link to the text file without importing it if you like. I have to wonder though, just how much data is this, and is it normalized? Me thinks no, it's not, but I can't be positive without seeing a snippet of the data. (Don't post it if it's sensitive data.)

 

[Avipatel]

Hi

There are a few tables. some of them have abt 700 000 records and some have abt 900 000 records. The data is from SAP so they have header data and line data. I am not familiar with the compact / repair feature. How do I go about using that feature?

I have now cut down the size of the data base and am using records on a year to year basis. I will probably have seperate databases for each year. Not the ideal way to go about it, but am just using a workaround until I hear further from my project manager.

Thanks
Avi

 

[Moniker]

Compact/Repair.

Tools -> Database Utilities -> Compact and Repair Database.

This "cleans up" the data as it were. It removes old, deleted records, cleans out temporary objects and values it's written, etc. Note the size of your DB, the do a compact/repair, and then look at the size of your DB again. A compact/repair will make it a lot smaller, especially if you've never done one before and you've been test importing a lot of stuff. 700,000 or so records really isn't a lot (not 2GB worth) of records, but I forgot to ask, how many fields are there per record?