What to do about "#deleted"?

[valeryk2000]

My app includes
1) SS2K8 database on the back end
2) Access 2003 front end NOT LINKED, data flows through ADO and stored procedures on the back end.
=================
Front End consists of forms/subforms and local tables "connected' to subforms.
Data flows that way:
Sql server table <===> Access local table <===> subform
==============
when user switches to another case in "Case" lisbox and the subform is reloaded then records disappear from the subform with scary "#deleted" ,
that stays until the underlying table is populated with the next case data
==================
Can "#deleted" be supressed?
Thanks
Val

 

[vbaInet]

I don't understand. I thought you said ADO recordset pull the data from your SQL Server db into Access, but looking at your data flow explanation Access local tables are involved. Please explain the concept.

* You populate recordset
* Delete records in local table
* Insert data from recordset into local table

Is that what you're doing?

 

[valeryk2000]

Aha. Exactly.
Local table is a record source for the continuous subform.

 

[vbaInet]

I reckon you're deleting records from the table first before populating your recordset.

Nevertheless, follow this sequence:

1. Populate your recordset
2. Unbind the form, i.e. set the RecordSource property to ""
3. Purge table
4. Bind form, i.e. set the RecordSource property back to the table or query
5. Populate table

If the above doesn't work, swap steps 4 and 5.

Another sequence would be:

1. Populate your recordset
2. Purge table
3. Requery form
4. Populate table
5. Requery form

 

[mtn]

The best approach for me in avoiding "#deleted?# is to have the RecordSource of my form DESIGNED with "SELECT * FROM MyTableName WHERE 1=0". At runtime I can change the recordsource to whatever I want and once I delete a list of records, I immediately set the recordsource back to the above first before any othe step.

 

[valeryk2000]

ok. Let me try this
Thanks

 

[valeryk2000]

vbaInet - so simple? I tested this approach and it works. I feel stupid not to figure it out myself. But ... that's the friends are for ... THANKS A LOT!

 

[vbaInet]

No problemo. You're welcome!

 

[valeryk2000]

vbaInet, no good deed goes unpunished. May be you van help with another problemo - see thread Unsolicited Access backup Files ...
Thanks

 

[vbaInet]

I've responded.