updates to SQL database = weird behavior in Access 2

[LabMouse]

I'm trying to make some changes to my company's db. I'm a summer intern and am new to SQL Server and Access. I'm having the following problem.

The tables are on SQL Server 2000. Access 2000 is the front end with the forms and queries in the .mdb file. The changes I need to make are dropping and adding columns to tables on the server. I do these tasks with SQL in the Query Analyzer. I then relink the tables in Access. When I open the forms in Access to test, I get a bunch of problems. When either changing an existing record or adding a new one, the user gets a message that another user is making changes to the record and thus any changes will be lost, yadayada. I added a timestamp column, that usually does the trick, but then I will sometimes get complaints about checkboxes not allowing Null values and so a record can't be saved. Also, forms will become uneditable. Their properties say they can be edited, but no changes can be made to any fields in the forms. I just get a beeping sound everytime I hit a key.

The weird thing is that I'll revert to the backup on SQL Server that was made just before I made the changes. This doesn't clear up the problems in Access' forms. My personal feeling is that Access is flakey. Can anybody give me any insight?

 

[PHV]

First, be sure that NO Bit field is allowing Null value in any SQL Server table and that access does recognize a PrimaryKey for EACH linked table.
Then, have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jsteph]

Labmouse,
I can tell you that Access 2000 had some severe flakiness and corruption issues. I'm not sure if any or all of that was fixed in any service packs.

I'd strongly suggest upgrading to at least Access 2002 or 2003,
--Jim