Solved

Access front-end with SQL back-end issues

Posted on 2014-02-21
9
1,571 Views
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).

Thanks.
0
Comment
Question by:Ei0914
  • 3
  • 3
  • 3
9 Comments
 
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)?

Jim.
0
 

Author Comment

by:Ei0914
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.
0
 
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.

Jim.
0
 

Author Comment

by:Ei0914
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:PatHartman
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.
0
 

Author Comment

by:Ei0914
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 :)
0
 
LVL 34

Expert Comment

by:PatHartman
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.
0
 
LVL 34

Accepted Solution

by:
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.
0
 
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.

Jim.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now