Compare & Delete between Two Different Tables

[james85]

I have a client who needs to remove all of the FCC issued No Call phone numbers from his marketing database by Wednesday if the courts do not continue to block the list from going live.

I have taken his database with all the contact info for his contacts and imported another table with the phone numbers provided by the FCC to be removed.

I then made a delete query between the phone number column in the original table and the FCC supplied phone numbers.

When I run the query it confirms I want to do it because all deleted data will be lost. I click okay and get the following error message:

"Deleting data in a linked table is not supported by this ISAM."

I have updated to Jet 4.0 drivers and that did not help.

Is there an easier way to compare and delete between two different tables in Access 2002?

Thanks,

James the Access Advanced Novice

 

[bat17]

and imported another table with the phone numbers provided by the FCC to be removed.

The error msg that you are getting is for a linked table not an imported one. Are you sure that you have imported and not linked?

Peter

 

[james85]

I simply opened the FCC single column list and imported into my client's database.

When I opened up my client's database the FCC list was in another table.

That is all I did.

When I created the delete inquiry I originally linked the Phone number column in my client's database to the single column in the FCC database. When I got the error mentioned above, I deleted that query and tried it without linking the two and still got the same message.

You would think that a compare and delete from one source to another would be a common and easy thing, but looking throughout the net and all the messages on this shows I am not alone.

Anyway, if it is a linked issue how would I resolve it?

Thanks,

James The Access Advance Novice

 

[bat17]

If it is linked to the original table rather than being inported then the answer is to import it.
In Access 97 you can tell if a table is linked to an external table or not because it has a black arrow next to it if it is. I am not sure about later versions but there will be an indicator of some sort.

If the table is in the database and not linked then I don't know what could cause this error.

Another work-a-round may be to add a Yes?No field to the customers phone list and update that to show which numbers are blacked. you could then use the field to filter by in a query.

Peter

 

[james85]

THANK YOU FOR YOUR REPLY!!!

It was enough to get me on right track of thinking.

I am using Access 2002. After a couple more importing experiements, I discovered that when you import from a mdb file it creates a link to the original external database. I could not figure out how to break that link and keep the data in the new database I imported into.

So I got to thinking, if the mdb file is dynamically linked then a text file should remain static......I then exported to a csv file from the original database and imported from the text file into the new database, instead of from the mdb file, and that did it. After that my query worked great!

Dennis The Advanced Novice