I have an Access 97 database that I am upgrading to utilize a SQL Server 7.0 backend and an Access 2000 front end. First, I am upsizing the Access 97 database to Access 2000 and then running the Upsizing Wizard to create the Access 2000 database with linked tables to the SQL Server. When I upsize the backend to SQL Server, I check the following attributes to upsize: indexes, validation rules, defaults, and table relationships using triggers(I currently have update and delete cascading selected and I enforce referential integrity). I let the wizard add timestamp fields and I only upsize the table structure. I later import the data without any problems. I then choose the option to link SQL Server tables to the existing application. 99% of the program runs fine. I use unbounded forms that I created in Access to manipulate the data. The vast majority of data adds, edits, and deletes are done through the use of recordsets with an SQL statement as the source for the recordset. My specific problem is in two different forms that access a "PhaseList" table and a "PartNumber" table. These two tables are related with a one to many relationship with "PhaseList" being the ONE side and "PartNumber" being the MANY side. The "PartNumber" table is then on the ONE side of several other tables throughout the database. I have no problem adding new records to these tables through my forms, but I get errors when I try to update or delete certain records in each table from my forms. It only occurs on some of the records in each table, but other records update fine. Another thing is that if I go directly to the linked SQL table in Access, I can make all the updates and deletes I want to the records that don't work in the form and it works fine. The specific errors that I get are: "Run-time error '3157': ODBC--update on a linked table 'PhaseList' failed." or "Run-time error '3156'...." when I try to delete. I have tried about everything I can think of to fix the problem. I have rewrote the form’s VBA code, I have tried to update the code with a SQL statement, I have only upsized the "PhaseList" table and then Imported the data. That didn't work, but if I copied/pasted the data in when I only had the "PhaseList" table upsized, then the update PhaseList form worked. If I upsized other linked tables though it wouldn't work. I did get it to work with other upsized/linked tables if I only copied/pasted in the first 140 records from the "PhaseList" table. If I copied one more record in or even added my own new record to the end of the table, I would start getting the error again. I have looked into the Triggers that the upsize wizard created, but I didn't see any problems there. I am just a beginning SQL Server user, so I am not too familiar with triggers, indexes, or permissions. I have however, got the rest of my database working great, it is just these 2 forms accessing these two tables that give me a problem. I have updated these “problem” records through another form, although I was updating different fields then in the other forms. I also should note that this has never been a problem when I had an Access backend, it is only with SQL Server as the backend. Another note is that when I do get the ODBC error and I click “end” to end the procedure, if I click my “Save” button again, it runs through the same update code and works, but then if I try to update that record again, it stops working. I could really use some help! Thanks for your time.<br>
Arnold<br>
Arnold<br>
I guess Access 2000's upsizing wizard is more picky when it comes to importing data because I didn't get any problems when I imported the data from sql server. I ended up changing a memo field in Access from "allow zero length" No, to "allow zero length" yes. I also found some leading spaces in one of the fields that is part of the primary key, so I eliminated those spaces. Lastly, I added zeros to fields that had a default value of zero, but were currently null or blank.<br>