Access front-end with SQL back-end issues

Posted on 2014-02-21
Last Modified: 2014-02-26
Hi Experts. I have a client with a mission critical Access application which is in production. When I migrated their back-end to SQL last year we encountered tons of write conflicts due to the following:

They have multiple instances of the same table opened on one form - filtered to display different records. Subform #1 shows "A" records, and #2 shows "B" records for example.

When I migrated over to SQL, and even when I was the only user connected I experienced write conflicts. So -- I decided to use temporary tables and update the live tables periodically throughout the session....

Lately the users have been losing multiple records while in production -- it appears that something is happening and for whatever reason (and it appears random) a few or all of the A and/or B records go missing.

The parent record remains. The related records go missing.

I am unable to reproduce the error myself. All users connect using remote desktop to the server, so the environments are the same for all of us. With the exception of power failures which I am assured is not happening, I don't know what to do to rectify this.

I would like to eliminate the temporary tables (which would assure that the records are written in real time to the tables) but need to address the write conflict issues.

Any suggestions would be appreciated. I'm banging my head against the wall -- not to mention dealing with a pissed off client (rightfully so).

Question by:Ei0914
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
LVL 57
ID: 39877308
1. Did you add a timestamp field to the tables?

2. What locking method are you using on the forms, edited record or no locks (which means no edit locks, not that there is no locking going on)?


Author Comment

ID: 39877395
Access 2003 (for now - planning on upgrading soon)

Default record locking - No locks

Default open mode - Shared

Sub-form record locks - No Locks

All tables have a timestamp field

I also changed all bit fields to int.
LVL 57
ID: 39877452
<<I decided to use temporary tables and update the live tables periodically throughout the session.... >>

<<it appears that something is happening and for whatever reason (and it appears random) a few or all of the A and/or B records go missing.

The parent record remains. The related records go missing.>>

  Sounds like there's a problem with the temp table operations.  Are you fully error trapped on those?   No set warnings off?   Everything done in code should be using CurrentDB().Execute <SQL>, dbFailOnError.

 My guess is that their data set is larger, and record updates/inserts are not occurring as you think.

  As for the write conflicts, just sounds like it's whatever your doing in the interface.  With no locks on the forms, which is optimistic locking, a lock won't be getting placed until you actually go to do something with the record.  

  So I'm scratching my head a bit as to how/why you should be getting conflicts.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39877562
Now I don't feel so bad :) I'll keep you posted. I'll remove all error checking to see if I can't isolate this issue.

Thanks a lot.
LVL 37

Expert Comment

ID: 39877851
The write conflicts are being caused by yourself.  You have at least two recordsets open with the same recordsource.  You need to be very careful to make sure records are being saved before you move to a different form.  So, if you edit a record in form A and don't save it and then go into form B and edit the same record or a record in the same block, you will get the write conflict.  Write conflicts also occur when you use update statements or run update queries in the form's class module that impact the record currently being edited (or a record in the same block)  You should probably rethink your interface design or perhaps the schema.  I'm using the term block which is actually the mainframe term.  The PC term is escaping me at the moment.  There is a difference between a physical record and a logical record.  We always think of logical records.  The row for a specific client or order item or part, etc.  But for efficiency, the database engine groups adjacent records and reads/writes them as a "block".  So, depending on the sector size, and the logical record size, a physical record will contain 1-n logical records.  You may be updating one of them on form A and another record in the same block in form B.  If you always save the record before attempting a different update, you should stop getting the write conflicts.

The temp table issues may be caused if the temp tables are in the BE and multiple users are using the same temp table.  Temp tables should almost always be in the FE or in a separate BE for each user so there is no conflict.  If the users share the same temp table in the BE, you must include a userID so you can operate on only one user's data at a time.

Author Comment

ID: 39877876
Hi Pat!!!!!!!!  Yep the temp tables are all in the front-end. I am in the process of working in a development copy and eliminating the temp table scenario so I can maybe isolate the conflict. I do save the record(s) as I navigate away from the sub-forms.

I am doing this on my own time because I'm curious if I can get away from the temp tables altogether. They've become quite convoluted and I want to get back to simple.

It's me -- Eileen :)
LVL 37

Expert Comment

ID: 39877981
Hi Eileen.  Hope you are well.  We've been buried in snow for a week and now it is raining and melting anything so it can freeze tomorrow.  If you want me to take a look, give me a call over the weekend.  I'm on my way to look at an investment property in New Haven.  A 4-family with a river view.  Take care.
LVL 37

Accepted Solution

PatHartman earned 500 total points
ID: 39879523
I had another thought - scary isn't it.

Make sure the RecordSource queries select ONLY the columns needed for a particular form.  When SQL Server determines conflicts this will have an impact.  If the queries select all columns, then even though they are changing different columns, there will be a conflict.
LVL 57
ID: 39879624
<< I'm using the term block which is actually the mainframe term.  The PC term is escaping me at the moment.  >>

  In a RDBMS, it's a page, but that can represent a number of clusters (and hence sectors) physically on the disk.

 But the write conflicts come out of the RDBMS at page or record level despite how their physically stored on disk.

  I guess when/where these write conflicts are occurring would tell the tale of what's going on, but it does sound like you were working with recordsets outside of the form with having a current record dirty.


Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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