Updating ODBC Linked Fields in Access

Question

I am having a lot of trouble updating fields in Access 2010 that are bound to linked tables using ODBC to SQL Server 2012.

Some fields I have to update using hard coded queries for various reasons.  It's very difficult to requery and/or refresh those fields.  I often get a data has changed error message.  The field.requery does not seem to work with ODBC tables when the underling data has been changed by query.  I never had this issue with native Access tables but unfortunately there is no going back.

Is there a good methodology to handle this?

Answer 1

You in general should not have such issues.

The “trick” here that most often fixes this issue is:

Ensure that the table has a primary key. (you likely have this already).

If the table has bit (yes/no) columns, then make sure you set the default for such columns as 0.

And also including a have a timestamp column in the table really helps this.

If/when you make any such changes to tables, then you want to re-link your tables.

So check the above 3 possible issues. Once you have above sorted out then your tables really should work as if they are local.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Answer 2

In addition to Albert's excellent advice:

>  I often get a data has changed error message

One cause of this is updating the data on the form by some other means, like running an Update query or a Passthrough query, or having another form bound to the same data.

---

-Tom. Microsoft Access MVP

Answer 3

I have done all 3 of those suggestions when I first converted to SQL.

1. Every table as an Identity ID integer primary key

2. Every table has a time stamp

3. All Boolean fields were defaulted to 0

I still have a lot of problems with data not updating or severely lagging.  I have placed if Me.dirty = True then Me.dirty = false everywhere where is an after after event.  I have placed Docmd.Refreshrecord in every place where there is an update.  This has helped a little but not much.

It's not a infrastructure issue as we are running SQL Server 2014 Enterprise and this is a relatively small application.

The requery method on controls definitely does not work work at all.  Access is definitely not updating the SQL tables efficiently.  It's like the events don't work or the Jet Engine engine can't handle it when using DAO.

I have had better results rewriting queries using SQL Server ADODB but there is too much code to rewrite and all the forms are bound.

Answer 4

There are at least two SQL Server ODBC drivers. The best one to use is SQL Server Native Client. If that's not the one you're using, switch to it and try again. There should be zero code change, except for the connection string in your reattach code.

> I have placed if Me.dirty = True then Me.dirty = false everywhere where is an after after event.
> I have placed Docmd.Refreshrecord in every place where there is an update

Those are two bad ideas. Code should be written based on understanding, not out of desperation.

Most of my projects are Access FE with SQL Server BE, and I never see these problems. You can't necessarily blame Access for this.

---

-Tom. Microsoft Access MVP

Answer 5

I am using the SQL Server native client.

There is definitely an issue with data being written to the tables.  We see data the been entered or updated to controls on the access front end but the code running in events is reading old data in the tables.  The saving and refresh seems to be the only way to get that data into the the table and then updated back to the controls although as you stated, it is problematic and an ugly solution.

Right now I have many users complaining and bad data is starting to creep in so yes - I am doing what i can to solve this.

Not sure if caching is playing a role in this.  I haven't found anything regarding the best practices regarding caching.

Answer 6

If someone has some ideas to fix this, I would appreciate it.  It's turning into a disaster with blank records getting inserted because data validation is not firing on current data.

Answer 7

I went through and made sure all primary key indexes are clustered and i changed Access's refresh rate to 1 second.  The refresh thing sounds drastic but not sure what else to do.

Answer 8

I would ensure that after any mods to any SQL table (setting the default for Boolean fields, and adding timestamps), that you then re-link all tables again from Access. (I suspect you done this, but after you make any changes to the SQL server tables, you NEED to re-link all tables again from Access).

As noted, the bit columns default and ensuing a timestamp column is the usual fix for the update issue (the record been modified by someone else). 

However if you have code in a existing form that updates the table separate from updating controls on that form, then this also can cause issues (when possible, you want such code to update the current forms record by use of controls in that current form, not a separate recordet or SQL update).

Another source of this issue can be SQL triggers - if there are any that fire and update data in the record, then again this can cause issues. 

Regards,
Albert D. Kallal (Access MVP 2003 - June 2017)
Edmonton, Alberta Canada

Answer 9

Thanks Albert. That's very helpful.

I do have some SQL update queries hard coded because I don't know of any other way to perform certain update tasks.  With native Access tables the control.requery was sufficient to bring the data associated with the control current.  However, as your comments seems to confirm, if not explicitly, requery does not work very well with linked SQL tables.

I am wondering if anyone has a proven methodology to update the bound form when a SQL update has been run on the table directly.  That seems to be the crux of the problem here.

Answer 10

I am wondering if adding a control directly on a bound form that is updated on the form whenever an update is done on the table will help to sync up the table & form?

Sort of like a timestamp on the form itself.  Has anyone had experience with this?

Answer 11

I removed Me.dirty = True then Me.dirty = False from the After_Update event for a Comobox that's the first control used to create a new record in a List Sub-Form.  A blank record was inserted without it's primary key ID identity number and of course errored out.  Breaking everything.

So not sure what the better understanding would be in this case.