Solved

Updating SQL 2005 table via Access 2003 ADP form

Posted on 2008-10-02
4
1,060 Views
Last Modified: 2013-12-05
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.
 
0
Comment
Question by:Adlerm
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
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 .
0
 

Author Comment

by:Adlerm
Comment Utility
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.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
Comment Utility
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.
0
 

Accepted Solution

by:
Adlerm earned 0 total points
Comment Utility
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now