Search Key not found in any record - error message

[Paul]

I have a datase that uses a table.
A record has somehow appeared that has a symbol (like a japanese
character) in one of the fields the word Anema in one of the other
fields.
I have tried to delete the record but get and error message saying
that the 'Search Key not found in any record'
I have tried to compact and repair the database but it seems to lock
up halfway through.

Can anybody advise on hoe to get rid of this record?

Thanks
Paul

 

[Brian]

Try a make-table query but filter out that record (or perhaps filter IN all
the others). If that prevents the error, you may then be able to delete the
old table and rename the new one as the old one.

 

[Paul]

Sorry Brian - I am a complete novice at this.
How do I di a 'make-table query'?
Also, How can I filter if the record does not have a search key?


Paul

 

[Brian]

Open the query builder window ("Create query in design view")
Select the table in question.
Change the query type (toolbar icon that looks like two tables overlapping)
to Make-Table Query
Type a name for your new table
Click OK
Double-click the asterisk in the list of fields
Double-click the name of the field that contains "Anema" (assuming this is
the only record that has this in it, we can exclude this entry)
Uncheck the "Show" box for that field name
In the Criteria line below that field name, enter <>Anema
Run your query (red exclamation mark on the toolbar)
Answer "Yes" when it notifies you that it will paste X number of records
into a new table.

If it completes successfully, open the new table and see if the record in
question was correctly excluded. If not, let's dig a little deeper.

 

[Paul]

Brian,

Followed these instructions but it just creates an empty table (when
it doesn't lock up)

The part I am saying 'yes' to is giving a message that 0 records are
to be copied.

Paul

 

[Brian]

I think I forgot the quotes; the criteria should have been this: <>"anema".

However, if that doesn't solve the problem, try the following as a next step:

Question: when you look at the table directly, how many records are there?

Let's simplify it a little. Just make a select query (the default kind of
query) first, double-click the *, and don't put anything into the criteria.
This should return all records in the table (scroll to the bottom of the list
after the query runs to find out how many it returned).

Next, enter <>"Anema" in the criteria of the affected field, and see how
many records are returned.

If this all works, then you can look at turning it back into a make-table
query.

 

[Paul]

When I used the quote marks and 'Nanema' it pulls out a single record
- the problem one.
If I do the same thing without the criteria it returns all the records
(359)

The problem record is actually listed as 363 but 362 is missing.

Just as a matter of interest - is there a bug or virus that would add
a record such as this? It seems strange that a character such as this
would appear from nowhere and the word is not one that I have ever
used - either in the database or anywhere on my system.

(even stranger - when I just tried to copy and paste the character to
show you it comes out as ( ½É) no brackets)

 

[Brian]

Well, now we're getting somewhere.

Before we continue, have you done a compact/repair yet? If not, make a
backup copy of your database, then try this: Tools -> Database Utilities ->
Compact and Repair Database. This may, in fact, repair any corruption (but if
the corruption involves other records, it might also affect other records;
thus the need for the backup first.)

Now, more on focusing on the bad record.

The fact that it pulled ONLY the problem one makes it sound like the
"Nanema" doesn't have the <> (means "does not equal") in front, which should
show all EXCEPT that one. Make sure to put the "<>" in front of Nanema so
that the criteria line looks like this:

<> "Nanema"

What are the results now? If this works, then switch it to a make-table
query again (leaving the <> in place). This should make a new table without
the problem record.

Access, being a programming language, can be subject to a host of viruses
that can do almost anything from propagating themselves across a network to
deleting files on your hard drive. I suppose it is possible that a virus
could have caused this. Scan your .mdb file (the database) with your AV
scanner to see.

 

[Paul]

Something very strange happening.
I have used the <> as you suggested.
If I just run the query it returns all the records except the problem
one as you suggested it would - Great.
However, if I use the make-table query it returns and error box
saying - Invalid argument.

The first time I tried it it actually locked up the program and I had
to close the whole lot down.

One other thing that might be worth mentioning - when I open the query
in design view I then selct the table - fine.
In the next column I select the field - fine.
I then deselct the tick box in the field column - fine.
I cannot then click on the criteria box in the field column but I can
click on any other box or area and then I can click on the criteria
field. - Very strange!!

I have scanned the file with Norton 2005 and it came up clear.

I did try a compact and repair. It was one of the first things that I
did to try and solve the problem but each time I try it my whole
system locks up and has to be restarted.

I supected that there might be problems with the actual Access program
so I have uninstalled and re-intalled it.

I also run 3 or 4 other databases and have found no problems at all
with these and this database does everything I ask it with no problems
other than the ones mentioned.

Could it be the actual size of the database? It holds a lot of jpeg
photos 1 in each of 2 fields of each record) so it is pretty big
(2,095,108kb)

 

[Paul]

Quick update - I have just tried to compact and repair the database
again and it comes up with the Invalid Argument error again.!!

 

[Brian]

At this point, it is just the process of brainstorming in an attempt to
isolate the cause of the corruption. Try making a copy of your database.

With your copy:

1. Remove everything (tables, forms, queries - everything) except the
problem table.
2. Try a compact/repair now. Does it lock up?
3. If this did not resolve the problem, try copying the query from the
original DB into ths copy and re-running it against this table. Results?

 

[Paul]

With your copy: Copied

1. Remove everything (tables, forms, queries - everything) except the
problem table.

Allowed to delete all tables
Allowed me to delete all queries.
Will not allow me to delete ANY of the forms - gives Invalid argument
error for couple of seconds before closing the program off.

2. Try a compact/repair now. Does it lock up?

Gives an error message panel - Table 'TempMsysAccessObjects' already
exists with OK and Help buttons.
When OK pressed seems to close of the repair section and take me back
to database pages - table not altered.

3. If this did not resolve the problem, try copying the query from the
original DB into ths copy and re-running it against this table. Results?

Still get Invalid argument error.

 

[Paul]

Been playing about and think I might be getting somewhere.

I created a blank database, selected the problem table from the
original database and used the Export utility to export it to the
empty database.
I then ran compact and restore on the new database.
I then opened the table and was able to delete the problem record -
hooray.
I then tried to export the table back to the original database. It
asked if I wanted to overwrite the original table and I answered yes.
It then threw a wobbler - gave the invalid argument error and deleted
the original table.
Luckily I have the copy that I made earlier.

I think my only option now is to rebuild the new database with the
good table in it.
Its getting late now and my brains gone to mush so I will leave the
rest till tomorrow.
Think I will try exporting each of the other tables, forms and queries
from the old database into the new one, trying it at each stage and
keep making a backup.
Its going to be an awful lot of work though. I'll be rebuilding
something that I have been building up over the last 3 or 4 years.
At least I still have all the data in the table though.

 

[Brian]

Following on that success, you could always export that table to a new DB,
then compact/repair, then copy/paste the rest of the objects (or use the
import feature in the new DB) one at a time and see if it stays consistent.
If this works all the way through, the only true rebuilding you will have to
do is the table relationships in your new DB.

 

[Paul]

Brian,

By using the import or export facility on both databases I have now
managed to recreate a usable database.

I must thank you for all the assistance you have provided during my
problems I would never have succeeded without it.

I now only have one problem - the database does not open using the old
switchboard.
Will I have to delete the old one and recreate it or is there a way of
getting the old one working?

 

[Brian]

Despite the "magic" surrounding switchboards, they are forms, just like any
other form. So, here is the sequence I would check to get it working:

1. Did you import it as one of the forms from the old DB to the new one?
2. If not, you can open both DB's; copy from the old, paste to the new; give
it the same name. (Do a backup of your new DB first in case it was involved
in the DB corruption)
3. You probably need to go to Tools -> Startup & select the name of the
switchboard form in the [Display Form/Page] dropdown.

 

[Paul]

Have already done as you suggest in 1 & 2 but had not done no. 3

Maybe this is what was causing the initial problem as it doesn't work.
Selected the switchboard form , closed down the database and re-opened
it.
Instead of the switch board appearing I got an error:
compile error user defined type not defined with OK and help buttons.
It also opens the form in MS Visualbasic.

Now I really am out of my depth with all that "gobbledegook"
If its any help there are 2 sections highlighted.
The first in yellow - Private Sub Form_Open(Cancel As Integer)

the second in grey - dbs As Database (this is after the word DIM which
is NOT highlighted)

mean anything to you?

Despite the "magic" surrounding switchboards, they are forms, just like any
other form. So, here is the sequence I would check to get it working:

1. Did you import it as one of the forms from the old DB to the new one?
2. If not, you can open both DB's; copy from the old, paste to the new; give
it the same name. (Do a backup of your new DB first in case it was involved
in the DB corruption)
3. You probably need to go to Tools -> Startup & select the name of the
switchboard form in the [Display Form/Page] dropdown.

Brian,

By using the import or export facility on both databases I have now
managed to recreate a usable database.

I must thank you for all the assistance you have provided during my
problems I would never have succeeded without it.

I now only have one problem - the database does not open using the old
switchboard.
Will I have to delete the old one and recreate it or is there a way of
getting the old one working?

 

[Brian]

Try changing it from Dim dbs as Database to Dim dbs as Object and see what
happens. Do you want to take this thread to e-mail, since it is getting
pretty long for the forum? If so, send to:

brainy
at
pacifier
dot
com

(it's written that way to keep the spammers out).

Have already done as you suggest in 1 & 2 but had not done no. 3

Maybe this is what was causing the initial problem as it doesn't work.
Selected the switchboard form , closed down the database and re-opened
it.
Instead of the switch board appearing I got an error:
compile error user defined type not defined with OK and help buttons.
It also opens the form in MS Visualbasic.

Now I really am out of my depth with all that "gobbledegook"
If its any help there are 2 sections highlighted.
The first in yellow - Private Sub Form_Open(Cancel As Integer)

the second in grey - dbs As Database (this is after the word DIM which
is NOT highlighted)

mean anything to you?

Despite the "magic" surrounding switchboards, they are forms, just like any
other form. So, here is the sequence I would check to get it working:

1. Did you import it as one of the forms from the old DB to the new one?
2. If not, you can open both DB's; copy from the old, paste to the new; give
it the same name. (Do a backup of your new DB first in case it was involved
in the DB corruption)
3. You probably need to go to Tools -> Startup & select the name of the
switchboard form in the [Display Form/Page] dropdown.

Brian,

By using the import or export facility on both databases I have now
managed to recreate a usable database.

I must thank you for all the assistance you have provided during my
problems I would never have succeeded without it.

I now only have one problem - the database does not open using the old
switchboard.
Will I have to delete the old one and recreate it or is there a way of
getting the old one working?



On Wed, 17 Aug 2005 21:20:02 -0700, "Brian"

Following on that success, you could always export that table to a new DB,
then compact/repair, then copy/paste the rest of the objects (or use the
import feature in the new DB) one at a time and see if it stays consistent.
If this works all the way through, the only true rebuilding you will have to
do is the table relationships in your new DB.

 

[Paul]

Done as you suggest and changed it to Object.

I have not used the visual basic debugger before so I am not sure if I
did it correctly - I just edited the words, closed everything down and
retried it but I got loads of various error messages.
In the end I have just deleted the switchboard and form and created a
new temporary one in switchboard manager. This appears to be working
OK so I will just recreate a new one - there were only a few items on
it anyway.

Thank you so very much for all the assistance you have given me - I
appreciate very much.

regards

Paul