Updating SQL 2005 table via Access 2003 ADP form

Hi all - I recently converted an Access 2003 mdb database to an Access.adp(2003)/SQL 2005 client/server set up. I'm trying to add a new record to an underlying SQL 2005 table via a form in the client side Access.adp database.
The form  has a view as its record source, the view is based on a single SQL table and the record in the table has a datetime field.
The form in question displays the current information from the SQL 2005 table and has an Add button on it so when the users wants to add a new record s/he simply clicks on the Add button which executes an After Click event to execute the following function code

               Function AddData(frm As Form) As Integer
                             Call ToggleEditMode(frm)
                             frm.DataEntry = True
                             DoCmd.GoToRecord , , acNewRec  <======  Error the system is complaining about
                            frm.CmdEdit.Caption = "&Done"
               End Function

This code is basically to set up the form - in Edit Mode - ie to allow all fields on the form to be written to -
but unfortunately when this code is executed I get the following message

                     Runtime error 2105
                     You can't go to the specified record
and stops on the line of code indicated above..

I have no idea why this is happening BECAUSE I have the same objects (forms, reports, modules etc) in an Access.mdb front end connected to the same SQL 2005 Server database EXCEPT that the SQL tables and views are linked via an ODBC connection and everything works as expected. What am I doing wrong? Surely I'm not the first person on the planet wanting to update an SQL 2005 table via an Access.adp front end form.  
I have also noticed that the add record selector (> *) is greyed out when the form is loaded in the Access.adp database but is not greyed out when loaded in the Access.mdb ODBC connected database.
Any thoughts/suggestions as to how I can fix the Access.adp approach would be greatly accepted.
Who is Participating?
AdlermAuthor Commented:
To Vadimrapp1: Problem solved - the points are yours and many thanks for your efforts. I didn't do exately what you suggested but what you said got me thinking  - especially Point 1 about triggers. When I initially loaded the Access tables into the SQL Server database via the SSMA wizard I went through each table in the SQL 2005 database and made sure that every table had a Primary Key(s) defined and the record definition in each table had a Timestamp field - as I've run into update problems before (especially with not having a Timestamp field associated with a record definition). Anyway I went and checked the bad table (tblJobs) I recently loaded into the SQL Database via the Access import wizard and sure enough the table didn't have a Primary Key defined nor did it have a Timestamp field associated with the record definition. The Access import wizard never prompted me for a Primary Key when loading the table (as the imported Access table didn't have a Primary key defined) so it was something I basically overlooked. Anyway after defining a Primary Key and adding a Timestamp field to the record definition in the bad tblJobs table in the SQL Server database everthing started to behave as it should. Maybe this is something that can be stored away in the memory bank for later reference. Again many thanks.
Vadim RappCommented:
Comment out  Call ToggleEditMode(frm) and frm.DataEntry = True and see if that works. Or, add button to the form, allow the wizard to create the code for you.

Most likely, ToggleEditMode somehow interferes with GoToRecord .
AdlermAuthor Commented:
To vadimrapp1 - Tried what you suggested but no luck.
A further update - I have discovered the problem has something to do with the table in the SQL Server database. During the initial development phase of the Access.adp/SQL Server approach I loaded the tables into the SQL 2005 Server database by using the SSMA wizard and all worked as expected. After completing the development/testing I then installed the Access.adp on the customers PC (running Access 2007) and used the database restore function to load the SQL 2005 database I had created on the customers server. Again everything worked as expected except for the fact that the SQL table in question (tblJobs) was now a couple of week out of date. I opened the Access.adp front end database on the clients PC and  used the Access import wizard  to import the latest  "tblJobs" table from the Access.mdb database which effectively created a new table (same table name but with a 1 suffix - tblJobs1) on the clients SQL 2005 server. I deleted the original "tblJobs" table from the SQL Server and renamed "tblJobs1" to "tblJobs". Unfortunately it's the renamed "tblJobs" table that is now causing the problem. I've proved that this is the problem by restoring the old SQL Server database and checking that the application is operating as expected and it is - it is only after I restore the SQL database that contains the table loaded via the import wizard that my troubles begin.
Is it possible there is a compatibility problem between the file formats of the old "tblJobs" table and the current imported "tblJobs" table and if so how can I tell. I've looked at the properties/extended properties of both versions of the SQL table but that doesn't tell me much except that there appears to be more extended properties for the current "tblJobs" than for the old "tblJobs" table - eg "MS_Validation Test - Cell is not a string" does not appear in the extended properties list for the old "tblJobs" table. In all there are 12 extended properties listed for the current "tblJobs" table while there are only 6 extended properties listed under the old "tblJobs" table. I tried deleting some of the extended properties from the current "tblJobs" table in an effort to align both sets of extended properties but I just keep getting an error message I don't understand. Again any suggestions would be much apreciated.
Vadim RappCommented:
1. see if the table has triggers
2. try both "good" and "bad" tables without restoring the whole database, by renaming the tables.
3. if (2) indeed works fine with good table and fails with bad, run profiler on sql server - profile this operation fo rhte good table and for the bad table, and then compare the trace and see what's different. Enable all sql events.

Extended properties are highly unlikely to be causing anything. Triggers and constraints are suspect. Having both "bad" and "good" table, in management studio you can generate the script that creates that table; if still no luck, please generate these scripts and post here.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.