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 58
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 58
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.

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


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 38

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 38

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 38

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 58
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

636 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