Record is deleted! Access 2003

[marcf]

Hi Everyone,

Hopefully someone will be able to offer a suggestion to this problem.

I have a multi user CMS running at work which I wrote. Aside from a few bugs everything has been going fine except for one annoying problem. Heres the info:

Windows XP workstations connecting to a windows 2003 server with roaming profiles for each user. Access 2003 SP2 on all machines.

Error: Record is deleted 3167

The wierd thing is users do not delete records from the table, each user has their own set of records acquired by a query when the form is opened so they dont EVER work on the same records. This error seems to mainly occur when the users are opening their reminders from a separate window or entering the main CMS form. Here's the code:

// start code sample

Private Sub Company_Name_Db lClick(Cancel As Integer)
Dim myVar

myVar = Me.Text16.Value

DoCmd.Close acForm, "main", acSaveYes
DoCmd.OpenForm "main", , , , , , myVar

End Sub

Heres the bit that it goes into when opened with args

Me.Filter = "([Postcode] = 'MK5 9DD' OR Imported=True) AND [username] = '" & User & "'"

Me.OrderBy = "Company Name"

Me.FilterOn = True
Me.OrderByOn = True

Me.Move 3200, 1

'If the form is being opened and the idnum is given by openArgs, open that record
If Forms!main.open Args <> "" Then
'Me.FilterOn = False

DoCmd.GoToContr ol "idnum"
DoCmd.FindRecor d Forms!main.open Args, , False
Else

//end code

Once one user gets this error everyone then gets similar problems or gets "Error excuting command." from the main switchboard. This always happens on all machines at the same time.

If we wait a few minutes and log back in it all works fine again!

It has been suggested that it could be a licencing issue with our server where we may need to buy more licences to ensure uninterrupted connections.

Aside from that I have no idea how this could be happening as I said users dont delete records from this table.

As an after thought the form load event for the main form fire up the reminders form which is a datasheet view like so:

Private Sub Form_Open(Cance l As Integer)

Me.RecordSource = "Select reminders.Compa nyID, reminders.[Company name], reminders.[Company Contact], reminders.Appoi nttime, Reminders.appoi ntdate FROM Reminders WHERE reminders.user ='" & User & "' and reminders.appoi ntdate<=#" & convertDate(For mat(Now(), "mm/dd/yyyy")) & "# ORDER BY Reminders.appoi ntdate ASC"

'Me.RecordSourc e = "Select * from reminders"
Me.Requery
Me.Move 1, 1, 3800, 8640

End Sub

Users DO right click->delete from this table but given the way it opens I cant see that causing a problem?

I did try doing a compact/repair this morning which SEEMED to fix the problem but it could have just fixed itself while I was doing it as it ranges from 2-15 minutes each time before we can log in!

Cheers

Marc

[Stewart Ross]

Hi. Can't see anything of concern in your code - does not look like it could result in the delete error you mention. The server licensing issue might pose a problem - but as all users seem to get this error simultaneously it is more like the back-end database tables being disconnected by some kind of server communication problem.

With Server 2003 is it not more about the maximum number of simultaneous connections, not licensing? By default there is no maximum, but a limit may have been defined by whoever set up the server with IIS.

-Stewart

[marcf]

Stewart,

We've done two things which seem to have fixed the problem, or at least changed the error message:

1) Split the database properly, now all the tables exist on the server and the forms/queries/reports all sit on the client machines.

2) Purchased more users for the server.

Now i'm getting on occasion, "You cannot you find/replace here" or something along those lines.

When the error comes up again i'll get the number written down, its in the form_open event shown above and appears to happen when I do a findrecord.

Again this may be a problem to do with server connections, we've just changed the switch in the office over the weekend so again this may resolve the issue.

Once again thanks for your response!

[marcf]

As an after thought:

Someone else suggested this could be to do with data corruption in memo fields, the notes field in our database is a memo field but i've tried looping through all memo fields in the database and accessing them without an errors being generated.