PROBLEMS (2000)

[Trabuda]

I have a rather large db that i've split. In my backend I've just started having problems, never had them before. On certian reports (scrolling through view) I keep getting an error msg that you can't do anything because another user is attempting to change the same data, however there are no other users in the db. I've compacted and repaired each user's front end and had them re-boot the machines and I still get the error. I've tried to compact and repair the backend and It only goes about 1/3 of the way and the db comes back up but it creates 2 copies.
I've rebuilt the report and query and still no success.... any ideas?

[Alexya1]

Have you checked for stray .ldb files??

[Trabuda]

Yes I did. There were none, then I had users who have access reboot to ensure there were none that I couldn't see.

[D Willett]

I've seen this with memo fields and queries.
It's very hard to locate the problem file.
Mine did a similar thing then went away after a few weeks for unknown reasons.

You could, back up your BE database and create a new one when all users are out of their FE's
Import all tables, queries etc into it.
Debug, compact & repair afterwards.
If this doesn't work, locate JETCOMP.EXE from the microsoft site and use this to try and repair the fault.
JETCOMP defaults on opening to version 4, this should be ok for you, A97 versions need Version 3 as the correct option.

Remember, use a backup or copy first in case it gets toasted ( as Charlotte says <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

[Trabuda]

I've rebuilt the query and report, value list and command button and code behind it. I've copied the db and imported info into a new one. Nothing helped. Upon more digging I found out that the 1 report only gives me one problem when I am using only 1 certian name in my value list. The other 10 names work fine with no problems. I've rebooted everybody's PCs 3 times. I still get the message that access thinks somebody else is using the same data (Error 3197). I've looked at the locks property and opened everything up for the time being, (Usually keep it on edited) and still nothing. Other than going to microsoft for the jetcomp.exe file and throwing the PC out the window, Anybody else have any more suggestions?

[charlotte]

Are you saying that only one user name generates this problem, or only one name in the data, or what? If you have isolated the problem that much, then it is probably a case of Access misinterpreting the name as a result of some character in it.

[Trabuda]

The problem is when one name "dewet" is used in the value list, which the query looks to to bring up only his records in a report. All the other names in the value list/query work fine. It doesn't matter who brings up the report or uses the query. Every body has the same problem with the "Dewet" when used in the one value list/query. When another value list/query is used with the data "Dewet" it works fine. Access is acting like two people are using the data at the same time which isn't the case because I had everybody with access log off the network server and re-boot. I went in alone and still had the problem. Even the network server showed me that nobody was in the program when I had them re-boot.

[charlotte]

OK, what service packs, etc., do you have installed? Can you post the SQL for the query that fails on the name? Oh, and are you running on a Novell network?

[Trabuda]

I can't remember the version off hand.. 7 maybe? It was whatever was most current in June/2003.
Here is the SQL

SELECT TBLCustInfo.CustName, TBLComLog.Date, TBLComLog.ContactFirstName, TBLComLog.ContactLastName, TBLComLog.WhatHappened, TBLComLog.Comments, TBLComLog.RankVisit, TBLComLog.Complete, TBLComLog.AddtlAeroRep, TBLComLog.InquiryInfoRequest, TBLComLog.Initiatedby, TBLComLog.TypeofCommunication, TBLComLog.WhoVisited
FROM TBLCustInfo RIGHT JOIN TBLComLog ON TBLCustInfo.CustID = TBLComLog.CustomerIdNumber
WHERE (((TBLComLog.Date) Between [Forms]![FRMMktgData]![Date1] And [Forms]![FRMMktgData]![Date2]) AND ((TBLComLog.Complete)="Yes" Or (TBLComLog.Complete)="-1") AND ((TBLComLog.TypeofCommunication) Like IIf([Forms]![FRMMktgData]![BoxComType]="All","*",[Forms]![FRMMktgData]![BoxComType])) AND ((TBLComLog.WhoVisited) Like IIf([Forms]![FRMMktgData]![BoxAeroRep]="All","*",[Forms]![FRMMktgData]![BoxAeroRep])));


This problem just started happening the other day. What I think what heppened is two users tried to use the same data at the same time and Access just went "nuts". I've already restored the back-up but I need to discover the source of the problem so It doesn't happen again.

[charlotte]

I'm still uncertain as to where the name that causes the problem is. Is this a user (i.e., login) name or is it a value in one of the name fields?

I did notice in your SQL that you are testing for TBLComLog.Complete = "Yes" or TBLComLog.Complete = "-1". If the field is a yes/no field, you don't want those quotes in there. If the field is text, then you're OK.

[Trabuda]

The name is a value in a name field. I don't have any user log-ins in this database since it is exclusively for Sales/Marketing purposes. The Complete field is a Text field so it should be fine. Like I said before... I think Access just went nuts.... It was working fine since I created it then it just went... well Nuts.... I can't think of what would cause an error like this.

[charlotte]

It's possible that you managed to get a non-printing character into that name record or otherwise wound up with a corrupted record.

[Trabuda]

Thats what I figured, that it got corrupted somehow. I just wanted to ensure that there wasn't a programming error lurking. I don't want this to happen again. Its such a headache bringing up the back up and restoring the data then getting the newer data in.