F & BE Record insertion / Query errors (1 Viewer)

[sphynx]

Not sure whether this is the correct area to post this in so I apologise n advance

I have been working on a projects database and have got to the point of multiple users accessing and writing data at the same time.

The Database is deployed via an MDE FE across 03,07 & run time machinery. The front end is linked to a MDB BE.

I have been experiencing the following issues:


Duplication of data via an append query

The projects setup form logs the projects overview into one table and appends task data from a master task list table into another table to the ID number of the project, this is all done via VB and a command button

Missing Records in Queries

Certain machines will not pickup certain records in queries, where as they are present in the BE and accessible via other peoples machines, this is generally happening on the machines that have Runtime installed

An advice would be greatley appreciated

 

[Guus2005]

You seem to have two questions.

You're not clear on the first one.

For the second one i need more info. Are you using transactions in the frontends? Why are you using Acc2003, Acc2007 and runtime versions? Stick to one version, it is easier to maintain.

What version is the backend?

HTH

 

[sphynx]

In reference to the second question, the FE and BE were built with acc2003.

Unfortunately I have tried to persuade the powers that be to upgrade all software to the same version but they will not budge so I have to implement a non cost system!!


In reference to the first question

when multiple users use a FE form to insert into a single BE table at roughly the same time it is causing errors in my database ie some records are pushed into the database twice, or are pushed in correctly, are present in the BE but the users FE wont pickup the records in queries and reports!

I have done some more research on file locking etc and have ask the network admin to check the permissions on the BE file and reset to Read/Write/Execute

The FE & BE are set to Shared with no locks

 

[Guus2005]

How is it possible that a record is stored twice in the same table? Aren't you using primary keys? Perhaps the primary key was issued twice? That would explain the problem. When you are using an Autonumber field as a Primary Key you don't have this problem.

There are ways to work arround this problem, when the PK was issued twice. Let me know if that is the case.

HTH

 

[sphynx]

I have always had my PK setup as an Autonumber with the indexing set to "Yes (No Duplicates)" so cannot understand this either!!

I have since done some more testing and cannot get it to recreate the error above. This doesn't mean that it won't happen again so any suggestions would be good!

I am also still getting problems with user entries not pushing into the BE and the FE not picking up some records on different users machines, this seems to coincide with users running Runtime

 

[Guus2005]

It is very hard to believe that Access creates the same autonumber field twice.

Could you post the database or the table with the duplicate Autonumber? Just to satisfy my curiousity.

Here's what you can do to create a unique number as a PK.

Create a table (tblUniqueID) with two fields ID (autonumber) and GUID (string)

Create a GUID (search for GUID on the forum) and insert it into the table.
retrieve the ID from that table with the GUId in the where clause. This is your unique ID. Use it to store data to your table.

HTH

 

[sphynx]

I have since removed all records to try and duplicate the error so cannot post the table with errors, sorry.

I will research what you have suggested and let you know how I get on, thanks for the help so far

 

[sphynx]

I have looked at GUID and would like to check that I understand fully what you are suggesting;-

Current DB

From opening the FE form it assigns the next ID in the sequence to PK autonumber field in the BE, im guessing what you are suggesting is that if the FE form is open simultaneously on different machines then the same PK ID could be assigned to both records, thus causing my errors?

With GUID

I would set the FE form up to generate a GUID on opening a new record, save the GUID to a seperate table where its entry would prompt an autonumber ID set as the PK.

I would then assign the PK ID of the GUID back to the record created in the FE form as its PK ?

Sorry if the above is a bit garbled little sleep last night, a late shift today and trying to get my head around this is taking its toll!!!!!!

 

[Guus2005]

Right on the button! If that's an expression.

I wonder... was it because my explanation was so good or are you so intelligent? Must be the latter!

Do you know how to create a GUID?

 

[sphynx]

I think we could settle on it being a combination of both!

I have research GUID and found the following post's on this forum

http://www.access-programmers.co.uk/forums/showthread.php?t=108630
http://www.access-programmers.co.uk/forums/showthread.php?t=159401&highlight=guid

The latter being one of yours!

I also have some other resources book marked in my browser so better get reading. Doesn't look to difficult, it would be easy to cut and paste the code but I prefer to understand what I am trying to do and give it a go myself first.

Thanks for your time & the pointers in the right direction, I will let you know how I get on!

 

[sphynx]

I have implemented GUID this morning in my Trial / Test MDB & all seems to be good & work as it should.

I will now split to FE & BE and trial with multiple users again.

Hopefully all is good, thanks for the help

 

[LPurvis]

While GUIDs are great for their purpose, storing one in a table as well as an autonumber for the purposes of maintaining a unique identifier feels pretty much like "having a dog and barking yourself". ;-)

The PK on the Autonumber field is sufficient for ensuring unique values.
However the Autonumber itself is not guaranteed to supply unique values to the column (and hence the index would summarily refuse the proposed new value - as it's violating the unique PK).

The AN value churning out duplicates is entirely possible, deliberately achievable and also the result of an older bug (in Jet 4 SP 5/6 IIRC).
Have a look at Allen Browne's page on the subject.
http://allenbrowne.com/ser-40.html

Cheers.

 

[sphynx]

Thanks for the link LPurvis. I have read and ensured that I have followed before re-testing.

I finally got around to retesting the DB in our training room on multiple machines simultaneously and unfortunately I am still experiencing some anomalies!!!

After implementing GUID it is clear each form opened on a separate terminal is registering a unique PK. So there is no conflict here

The issue I am still stuck with occurs in the following scenario:

• FE form open on multiple terminals connected to BE
• Records entered and saved simultaneously

All the records save to the back end tables correctly but in some incidences some of these records will no show up in queries within users FE.

I am using multiple update and append queries from my FE form to generate a list of tasks, specific to the product enter on the main form. Could this be part of the issue?

 

[LPurvis]

>> "After implementing GUID it is clear each form opened on a separate terminal is registering a unique PK. So there is no conflict here"

Well, I didn't think there was originally.
It would be extremely rare that an AN was causing concurrency problems other than the generated number bug of SP 5 era. (I think AN concurrency was last reported to me as a general issue as part of an Access 95 app
GUIDs are great as UIDs but shouldn't be necessary.

As for why records aren't showing up in queries... Sheesh - that's one of those that could be anything without more information or actually sitting infront of it.
What query are you running - and what rows is it not bringing back?

Cheers.

 

[sphynx]

I have finally worked out the problem with some records not showing up in queries.

As previously stated I have been running an MDE in 03, 07 and Runtime, as I didn't want to spend 3 days setting every terminals Action query warnings to false, I inserted the statment

 DoCmd.SetWarnings

into my code

This has stopped the warnings on all machines but has also appeared to stop my action queries from running on machines using Runtime!!!!!!!!

Has anyone else experienced this problem and know of a resolution?

 

[LPurvis]

I can't see at all why that would affect the actual queries execution at all.
However the fact that you've implemented it to prevent messages implies you're also executing your queries with methods of the DoCmd object (OpenQuery or RunSQL).
Using other methods of execution will mean you don't need to switch off warnings - which is a problematic activity - your error handling needs to be such that it always gets switched back on.

Using a library specific method such as
CurrentDb.Execute "QueryName", dbFailOnError
or
CurrentProject.Connection.Execute "QueryName"
will see no messages raised before execution.

Form expression references as parameters in your queries aren't evaluated implicitly by using DAO or ADO methods though. That's easily handled by using a dedicated function to call the execution.

fExecuteQuery "QueryName"

Function fExecuteQuery(strQuery As String, Optional intOptions As DAO.RecordsetOptionEnum = dbFailOnError, _
                                           Optional blnReturnAuto As Boolean = False, _
                                           Optional pdb As DAO.Database) As Long
    
    Dim db As Database
    Dim prm As DAO.Parameter
    Dim qdf As QueryDef
    Dim rst As DAO.Recordset
    
    If Not pdb Is Nothing Then
        Set db = pdb
    Else
        Set db = CurrentDb
    End If
    
    Select Case Left(strQuery, 7)
    Case "INSERT ", "UPDATE ", "DELETE "
        Set qdf = db.CreateQueryDef("", strQuery)
    Case Else
        Set qdf = db.QueryDefs(strQuery)
    End Select
    
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next
    
    qdf.Execute intOptions
    If blnReturnAuto Then
        Set rst = db.OpenRecordset("SELECT @@Identity")
        fExecuteQuery = rst(0)
        rst.Close
    End If
    
    Set prm = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Function