Last week, I wrote about using Access 2000 to manage SQL Server databases. Access has been a boon for developers and system managers for years because of its easy-to-use features, GUI, and import and export functions.
After reading my article on using Access, a few readers told me that a bug in Access 2000 can corrupt data in a SQL Server database. Here’s a description of the problem and some solutions from Lisa Gurry, Office product manager at Microsoft: "When a new record is added to a linked SQL table and a cursor is moved to a different record, the new record that has been added will appear to disappear and be replaced by a duplicate of the previous record. This issue will occur only if the SQL Server table has an identity column and contains more than approximately 400 records. When a record is added, a duplicate of the previous record appears. The new record is not displayed until you refresh the recordset. This behavior is related to the way Access requeries the SQL Server database. This issue can affect Access 2000 users on any operating system using SQL Server 6.5 and SQL Server 7.0 in this particular scenario."
"Customers should know their data is not lost and they should not try to delete the apparent duplicate record because this will actually delete the original record. Instead, they should refresh the recordset to see the new record."
"Customers can avoid this issue in three ways: First, use an Access project and open the SQL table directly. Second, if you see this behavior in a form, use Visual Basic for Applications (VBA) code in the BeforeInsert and AfterInsert events to requery the data and move the form to the newly added record. Third, if you see this behavior in a table, resort the table or close and reopen the table."
Lisa said that Microsoft will soon post a Knowledge Base article on its Web site about this problem.
