Update SQL Statement Freeze up

[Moore71]

Hi,
I have an access app which is being used in 2 system on a network. Whenever both users are updating simultaneously, one system will freeze up while ther will go through.
Please what do I do?

 

[jdraw]

Tell us about your application/database.
Is it split?

 

[The_Doc_Man]

This is an incomplete problem description. Does the one that freezes up show any errors when you try to use it?

This kind of behavior is typical of incorrectly shared files. We need more details to know what is actually going on.

 

[Moore71]

Yes, the Application is split
2 frontend and 1 database
when the 2 are updated simultaneously, that is running the update sql code through VBA, the one hosting the backend will go through, while the other will complain like "error running sql, or the other user has opened the program in Admin mode" and then freeze up

Here is the SQL statement:

(On Error Resume Next
Dim SQL As String
Dim SQL2 As String


SQL = "UPDATE tblOutbound INNER JOIN tblItem ON tblOutbound.ItemID = tblItem.ItemID SET tblItem.QtyAvail = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], tblItem.UnitSale = [tblOutbound].[ExtendedPrice], tblOutbound.ClientID = [Forms]![frmPoS]![cboClient] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![InvoiceNo]));"

SQL2 = "UPDATE tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID SET tblClient.ClientAcc = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![OutboundInv]));"

DoCmd.SetWarnings False

DoCmd.RefreshRecord

DoCmd.RunSQL SQL

DoCmd.RunSQL SQL2

DoCmd.SetWarnings True)

 

[valeryk2000]

Are front end apps have LINKED tables or you are sending the query over ADO?

 

[Moore71]

The Front end apps have LINKED tables

 

[jdraw]

Not sure I understand your sql

SQL = "UPDATE tblOutbound INNER JOIN tblItem ON tblOutbound.ItemID = tblItem.ItemID SET tblItem.QtyAvail = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], tblItem.UnitSale = [tblOutbound].[ExtendedPrice], tblOutbound.ClientID = [Forms]![frmPoS]![cboClient] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![InvoiceNo]));"

SQL2 = "UPDATE tblClient INNER JOIN tblOutbound ON tblClient.ClientID = tblOutbound.ClientID SET tblClient.ClientAcc = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded] WHERE (((tblOutbound.InvoiceNo)=[forms]![frmPoS]![OutboundInv]));"

I think these will be treated as string literals:

[forms]![frmPoS]![InvoiceNo] in first SQL,and
[forms]![frmPoS]![OutboundInv] in second sql

I think you have to adjust the sql such that you get the rendered value from the form controls.

Try using a Debug.print SQL and Debug.print SQL2 to see what the values are when you send the sql to the DoCmd.runsql

 

[valeryk2000]

I would run these queries using ADO command. Linking tables is not good, slows down and prone to inadvertent record changes by user

 

[The_Doc_Man]

valeryk, sometimes ADO is better, sometimes DAO is better.

jdraw's comment on the treatment of the bracketed items: Concur that there is an error in this method. If you are going to do a form reference, it has to look more like

SQL = "UPDATE tblOutbound INNER JOIN tblItem " & _
         "ON tblOutbound.ItemID = tblItem.ItemID " & _
         "SET tblItem.QtyAvail = [COLOR="Red"]" & [tblItem].[QtyAvail]-[tblOutbound].[QtyOut] & _[/COLOR]
         ", tblItem.UnitSale = [COLOR="Red"]" & [tblOutbound].[ExtendedPrice] & _[/COLOR]
         ", tblOutbound.ClientID =[COLOR="Red"] " & [Forms]![frmPoS]![cboClient] & _[/COLOR]
             "WHERE (((tblOutbound.InvoiceNo)= [COLOR="Red"]" & [forms]![frmPoS]![InvoiceNo] & "[/COLOR]));"

That's not all... if those are strings rather than numbers, you will also need the single-quote character getting involved here.

"error running sql, or the other user has opened the program in Admin mode"

This occurs when the front ends are not correctly configured. On the File >> Options >> Current Database page, there is a box for "Open Exclusive" and this must NOT be checked for a shared BE/separate FE case.

 

[Moore71]

Okay,
I will check through then

 

[valeryk2000]

I would Debug.Print SQL, copy it from the immediate window and paste it to the query window - it will show you the error (if any)