SQL Server Delay Problem

Question

Hello, I have just finished importing an ACCESS db (back-end) to a SQL Server 2008 db. My front-end is still ACCESS 2007 and the back-end is SQL Server 2008. What I've realized is that if I open a form (on a certain record) and then do some calculation which is supposed to fill in some text fields automatically on that form (for that record), then those values for those text fields are not filled in at that moment. Now, if I close the form and reopen it for that same record, the values are there. So there is like a delay, and that is preventing other things from happening. Can someone give me a hand on this? I tried with ODBC driver SQL Server, as well as with SQL Server Native Client 10.0, and none of them worked.

Thank you very much

Answer 1

Most features in a form work the same when using local tables or linked ones to sql server.

However one significant difference here is that even when you start editing information in a form in add mode the primary key is INSTANLTY added in a local form. This means if there’s any kind of lookups, perhaps even sub forms, or other expressions that are based on using the primary key, these displays will be instantly updated when you’re running a local edition of MS access and you start to type.

However when using linked tables to SQL server, the auto number primary key ID is not generated untill the record is actually saved. Therefore you should check these expression(s) if they use and rely on the primary key for their display of information.

If the above is your problem, then perhaps in one of the controls that a user common enters data you force a disk write (and thus force generation of the Primary key).

So in the “after update” event of a major textbox (field) you enter, you can force a disk write such as:

If isnull(me!id) = true then
   ‘ we have no primary key, force a disk write
   If me.Dirty = false then
      Me.Dirty = true
   End if
End if

You might want to expand and give a few more details as to what kind of expressions are not working. However, the above is to most significant difference in the sequence of what point in time the primary key is created and becomes available to other expressions on the form.

Answer 2

With server back ends, Access needs help to keep the form up-to-date. First off, every table needs a primary key (no properly designed table lacks one, but this can be the kind of thing that worked fine with Jet and breaks when upsized to SQL Server, or any other ODBC back end). Second, I have found that it is good practice to add a timestamp field to all SQL Server tables because that allows Access to know if the record has been updated without having to evaluate every field in the record. This also makes it possible for Access to refresh the display buffer in the form without doing too much extra work.