I am trying to run a Dcount to find duplicates in my recordset, but I get an error telling me that the Jet engine can't find the local disconnected recordset (although I have a watch on it and it is perfectly fine, right there in memory). I have the same problem when I try to run a report off this recordset. Do I have to copy the whole table from the backend again just to check for duplicates?

Using the query wizard, you can create a query that does that for you. No need to recreate the wheel. Have you tried it yet?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Well, I don't actually have any tables in the frontend. Just the forms and the reports.

That doesn't matter. As long as your forms and reports are linked to the back end and getting their data, the queries can also link to the tables in the back end.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I don't have any active links. I use the ADO to load the data to the local disconnected recordset and release the connection. Once all the data entry etc. is done, I re-connect to do a batch update. That's why my recordset is only locally present.
PS: I get an error when I try to run the query wizard. Telling me there is no query or table.

Ah, gotcha. In that case I suggest this. Create a small table with a couple of records in them in your front end. Run the query wizard on THAT table. Tell it you want to find duplicates in your table. After the query is created, open it in SQL view. Copy the SQL code you see and then paste it into your VBA code. Change the names of table and fields in your new VBA code to match the REAL recordset you want to work on. Once you got it working, delete the test table and test query.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Great idea. I will try that now. Thanks so much.

I tried it, so far no luck. Do you think I have to run the query on the connection to the backend? I tried to again run it on the local recordset. Somehow Access doesn't seem to want to work with that recordset at all.