Error: 3086

[Jon Slack]

I have a database consisting of 3 main tables. The first
is a static table containing around 86,000 records. The
second is a single-record table that stores data entered
by the user that is then used as a Search Term to search
through Table 1, to create (by Append) Table 3 - which
then obviously contains a subset of the data in Table 1.
Still with me?

After the user has performed his/her search, (s)he can
then enter another search term (in the single-record Table
2) to refine the search. The query that looks after this
takes the first results (Table 3) and deletes all records
that do NOT have the 2nd search term in them. This creates
a smaller result set (Table 3) and the user can then
refine the search further if (s)he wishes.

Except that it doesn't work. When running the macro that
calls the query that removes non-matching records from
Table 3, it fails with an Action Failed error on the
OpenQuery command. Upon clicking Halt, a diague box opens
up with 'could not delete from specified tables.'

The Help for error 3086 offers 4 potential problems, none
of which seem to apply. Can anyone off any insight?

Yours hopefully,

Jon

 

[Rene]

Hello, how are you ?

I think this due to a joint for one to any. In this case
the query locks in view mode only and you can neither
change or delete data.

You may want to do this by code, using the filter table
and iterated on table 3.

Or you may want to use filter form provide with access
that do the job for you.

Or you may just use ordinary filter on one field at a time.

Hope it helps.

Have a nice day.

 

[Jon Slack]

Merci Beaucoup Rene

I actually solved the problem using a different method.
Instead of getting a query to delete records I did not
want, I copies the records from Table 3 that I wanted
(satisfied the search criteria) into a new Table 4. Then I
wiped table 3, copied the contents of Table 4 into Table 3
and wiped table 4.

It's a bit convoluted but it works. And users can keep
refining their search all the way down to one record and
the thing works )

So for info: you cannot, as far as I can tell, delete
records that satisfy a NOT LIKE criterion using the
contents of a single-record, single-field table as the
criterion data. I know that <, >=, <> etc work ok but not
NOT LIKE......

Once again, thanks very much for the time/trouble, Rene.
Much appreciated.

Jon