Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1661
  • Last Modified:

Access front-end with SQL back-end issues

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
Eileen Murphy
Asked:
Eileen Murphy
  • 3
  • 3
  • 3
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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
 
PatHartmanCommented:
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
 
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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
 
PatHartmanCommented:
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
 
PatHartmanCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now