records show "deleted"

  • Thread starter Thread starter Josh
  • Start date Start date
J

Josh

I have a SQL server 2k backend working with an Access 2002
frontend:

I have a form and subform. The form contains fields from
Appointment table, subform with fields from ApptOVDetail.
It is constructed this way because on adding new records
to the appointment table, SQL won't return the
AppointmentID back to Access so I can generate a new
record in the ApptOVDetail table. The form-subform format
seems to get around this.

On the BeforeUpdate event for the form, I have code to
record the username and date updated.

When the cursor moves from the form to subform, all the
fields from the appointment table are marked "deleted".
This behavior stops if I comment out the code on the
BeforeUpdate event.
Thanks for any suggestions you might have for this.
 
Josh,

I had a similar situation, however I attributed it to sharing the db from a
server copy. On a particular screen was a procedure to delete and
repopulate the forms underlying table. If a one user did this, the other
users would see the deletions as soon as they clicked on a new record.
Moving the Frontend off of the server to the local drives, so that they were
not sharing solved this.

Not knowing enough about your app, I don't know if this is relevant, but it
may help you insolving yoru problem.

God Bless,

Mark A. Sam
 
You do have timestamp fields in both tables, and they are exposed to
ms-access ..right?

When using sub-forms, ms-access REALLY needs to have a timestamp field
exposed. If you don't expose the timestamp field, them access has a rough
time.

Make sure you have timestamp fields exposed for both the parent,a and the
child table, and see if that does the trick.

While there are certainly many ways to get the "last", or new id of a
record, I have to agree that using a form and a sub-form is a standard way
to add records to the "many" side of a traditional one to many relationship.
 
Anytime you delete records from a form, the underlying recordset still
contains a place holder for that record. Since that record doesn't exist
anymore, the form will show "deleted" to indicate that the record doesn't
exist anymore. You can fix this by doing a requery of the data, which
removes unneeded place holders and adds in new data. This could also be the
problem with your new records not being shown.

If you are already doing a requery, then its a timing problem. Depending on
the code you use to perform the delete, it might not have finished before
the requery took effect. Simple solution is to put a pause between the
delete code and the requery code. If your delete code is in the form of a
SQL delete query use the execute command to run the query instead of
RunQuery. execute will pause until the query is finished before going on to
the next line of code.

Kelvin
 
Albert,

What do you mean by having the time stamps "exposed" to MS Access? Do you
mean they need to be in the form's underlying recordset? On the form
itself?

This information may be helpful to me as I have had many issues since going
to a SQL Server backend. Maybe this will solve some of them. I have
timestamps on all of my tables, but possibly not included in all of the
recordsets.
 
Yes, you want to always include the timestamp field in the recordset. You
of course don't have to place it on the form, but you do want to include it
in the data source.

Ms-access will other wise have to do a field by field checking to figure out
if things have changed, and you will get a lot of "deleted" records in a
sub-form if you don't include the time stamp.

You also want to be careful with bit fields. Check out the following:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table
http://support.microsoft.com/?id=280730

and

ACC2000: "The Microsoft Jet Database Engine Stopped the Process" Error
Message When You Try to Delete a Record
http://support.microsoft.com/?id=318882

As for timestamps, make sure you don't make the timestamp the key field, or
that the timestamp has a unique index on it.

ACC2000: Linked SQL Server Table Cannot Be Updated
http://support.microsoft.com/?id=208842

And, some general reading on optimizing
ACC2000: Optimizing for Client/Server Performance
http://support.microsoft.com/?id=208858

You also want to make sure that your child tables also have a key id.
Usually they do, but I did in some of my applications have a child table
with only a link field to the parent table, and NO key id (however, I don't
think you link to a odbc table without first setting the key id). You should
have one for each table. (by key id, I mean a identity field).
 
Albert,

I appreciate that informaton. I'll check out the link when I get a chance.

God Bless,

Mark
 
Albert:
I don't have timestamps on the tables. I'm sure that's it,
or a big part of this.
How do I add them? Meaning is there a timestamp datatype
in SQL?
Thanks so much (and why haven't you gone to "gramma's"?)
 
Back
Top