A couple of things...
Offhand I'd say it sounds like an issue with the form and it's not updating the relational tables correctly but you'll have to test by having the user add data directly into the tables or SQL Server didn't establish the relationships between the table when upsizing (see below comments on relationships of the tables). I also think the ODBC DSN might be the problem considering that it works fine on one machine and not the other. Try re-creating the ODBC DSN on the specific problematic user's machine and deleting/re-linking the tables. Does this happen for 1 specific user or does it happen randomly for all other users while working fine on your machine? Do the other users have a good network connection as you do or have they also been experiencing other abritrarly network problems? Also, is it possible that the user's might be doing something you hadn't anticipated on the form which is causing the problem?
There's a lot of possibilities here and you need to try and narrow down what might be the problem the best you can. Start by narrowing it down to 1 or more specific users if possible and see if you can tell if they are doing ANYTHING different or if there's anything different with their setup. You can even throw in a few msgbox lines of code in certain places in the front-end to see what values are being returned during data entry. This may help to make sure certain fields such as the primary key or other required fields are being updated as the user enters data. If the user gets the error, make sure you walk through EXACTLY what they did on your machine to see if you can reproduce. Hopefully you can reproduce somehow on your machine and find out it's a simple code fix somewhere in the form.
Other things to check/try:
1. Possibly add a timestamp field (timestamp data type) to one or more of the SQL Server main data tables.
2. Refresh your linked tables in the Access interface.
3. There is some code in the codebank which automatically refreshes linked tables. You could possibly use this code to refresh the linked tables somewhere in the startup of the app. Also try deleting all the tables out of the mdb and re-linking the tables back in. Check your ODBC DSN Connection or even try creating a new one. I like to use Windows authentication verses SQL Server authentication on the ODBC DSN Connection.
4. *** Check to make sure you have a primary key in the tables and an autonumber (which is set to increment). Check the tables as sometimes the upsizing wizard doesn't configure them correctly. Did you use DRI or Triggers when upsizing the tables? (There was a checkbox for this in the upsizing wizard and you should have checked DRI - Direct Referential Integrity!) It sounds like the problem is relational so check the relationships in SQL Server on the tables.
5. Make sure you're not experience possible network connectivety issues to the SQL Server db and that the form itself isn't causing update problems. Test by adding data directly into the tables verses via the form. Make sure you/users have permissions in SQL Server for all (including the relational) tables.
6. Maybe consider re-indexing the tables via SQL Server.
7. *** Again, make sure the relationships of the tables on SQL Server have no issues. If you somehow got or had any orphaned records, SQL Server might be complaining when adding new records or it especially didn't setup the relationship correctly during upsizing! Check this. Especially check all the relationships of the tables on SQL Server. Possibly delete relationships on the tables and re-create them and see if you get any errors when re-establishing the relationships (make sure to refresh or delete/relink the tables in the mdb after making any modifications to the SQL Server tables!)
Last edited by pkstormy; 02-15-08 at 15:01.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)