Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Access front-end with SQL back-end issues

Posted on 2014-02-21
Medium Priority
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.

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 39

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 39

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 39

Accepted Solution

PatHartman earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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