Access and SQL Server on Azure

[JohnPapa]

The SELECT statement of the Access Combo is as follows

SELECT dbo_tblPersonInCharge.intID, dbo_tblPersonInCharge.nvcSurname FROM dbo_tblPersonInCharge;

The SQL Server table is as follows

 

[isladogs]

I haven't been following this thread and this may well be irrelevant to whatever you are discussing. However, you should avoid allowing nulls in bit fields in SQL Server tables linked to Access. These are often the cause of write conflict errors.

Write Conflict Errors

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

www.isladogs.co.uk

 

[JohnPapa]

I haven't been following this thread and this may well be irrelevant to whatever you are discussing. However, you should avoid allowing nulls in bit fields in SQL Server tables linked to Access. These are often the cause of write conflict errors.

Write Conflict Errors

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

www.isladogs.co.uk

Agree. Nulls are a source of multiple problems. What does not appear in the table definition, is that ALL fields have a default value.

The problem with the Combobox is that the Control Source should have been intPersonInCharge instead of intID.

 

[Josef P.]

The problem with the Combobox is that the Control Source should have been intPersonInCharge instead of intID.

Are you mixing up ControlSource with RowSource?

Bound to intPersonInCharge ... ControlSource
SELECT intID, nvcSurname FROM dbo_tblPersonInCharge ... RowSource

 

[JohnPapa]

Are you mixing up ControlSource with RowSource?

Bound to intPersonInCharge ... ControlSource
SELECT intID, nvcSurname FROM dbo_tblPersonInCharge ... RowSource

I do not think so. It is the field where the combo selection is stored.

 

[Josef P.]

I cannot change the content of the Combobox.

How does this show? Does an error message appear or is the control locked?

Is the problem only related to the combobox?
If you add a textbox next to the combobox and bind it to intPersonInCharge as well, can you enter an ID value?

 

[JohnPapa]

How does this show? Does an error message appear or is the control locked?

Is the problem only related to the combobox?
If you add a textbox next to the combobox and bind it to intPersonInCharge as well, can you enter an ID value?

You read that the problem has been solved, right?
The problem was only with the Combobox.
Per your request I show below a textbox whose Control Source is intPersonInCharge. If I change the Combobox the textbox content changes, if I change the textbox contents the Combobox content changes

 

[JohnPapa]

I tried to manipulate tblUnit.numArea to update tblUnit.numCommonExpensePercent. I get the following

I did some research and found that a relink of the table would solve the problem, so within Access I selected tblUnit and clicked of Refresh Link. That did not help.

FYI,
the driver is

field numArea in Access is defined as follows

In SQL Server numArea is defined as follows

 

[cheekybuddha]

Do you get the same error if you try:

' ...
      rst!numCommonExpensePercent = rst!numArea / CDec(222.22)
' ...

Or you can avoid the recordset altogether:

Dim strSQL As String

strSQL = "UPDATE dbo_tblUnit SET numCommonExpensePercent = numArea / 222.22 " & _
         "WHERE intBuildingID = " & Me.intBuildingID & ";"
CurrentDb.Execute strSQL, dbFailOnError

 

[Minty]

Check you haven't got ARITHABORT in SQL server set to on:

decimal and numeric (Transact-SQL) - SQL Server

Transact-SQL reference for the decimal and numeric data types. Decimal and numeric are synonyms for numeric data types that have a fixed precision and scale.

learn.microsoft.com

 

[JohnPapa]

Do you get the same error if you try:

' ...
      rst!numCommonExpensePercent = rst!numArea / CDec(222.22)
' ...

Yes, same error

Or you can avoid the recordset altogether:

Dim strSQL As String

strSQL = "UPDATE dbo_tblUnit SET numCommonExpensePercent = numArea / 222.22 " & _
         "WHERE intBuildingID = " & Me.intBuildingID & ";"
CurrentDb.Execute strSQL, dbFailOnError

Works fine. I guess because you deal with the SQL Server directly with little Access intervention

 

[JohnPapa]

Check you haven't got ARITHABORT in SQL server set to on:

decimal and numeric (Transact-SQL) - SQL Server

Transact-SQL reference for the decimal and numeric data types. Decimal and numeric are synonyms for numeric data types that have a fixed precision and scale.

learn.microsoft.com

I have used

SELECT [ARITHABORT] = CASE CAST(cfg.value AS INT) & 64 --bitwise operation on the 7th position
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END
FROM sys.configurations cfg
WHERE name = 'user options'

to find out that it is OFF

Then I tried using

SET AROTHABORT ON

to set it to ON, unsuccessfully. Will investigate more later on today.

 

[JohnPapa]

I replaced the 222.22 with a field value on the screen and it works as seen below. So the problem is the way 222.22 is handled.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM dbo_tblUnit WHERE intBuildingID = " & Me.intBuildingID, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
If rst.BOF And rst.EOF Then


Else
    rst.MoveFirst
    Do While Not rst.EOF
        rst!numCommonExpensePercent = rst!numArea / Me!numAreaTotal
        rst.Update
        rst.MoveNext
    Loop
End If
Me.Requery

 

[cheekybuddha]

Some thoughts:

1. Why use a recordset for this simple UPDATE? It's much more efficient to send a single UPDATE statement which will update all possible rows at once rather than have the overhead of creating a recordset object, downloading the data locally and performing row by row updates one at a time.

2. If you really must use a recordset, why use a ADODB recordset as opposed to a DAO recordset?

3. When opening a recordset that does contain records (ie Not (.BOF And .EOF)), have you ever known it to open at anything other than the first record? Why .MoveFirst on open?

 

[Gasman]

3. When opening a recordset that does contain records (ie Not (.BOF And .EOF)), have you ever known it to open at anything other than the first record? Why .MoveFirst on open?

I see this all the time and always wonder why.

 

[cheekybuddha]

I see this all the time and always wonder why.

Similarly, you only need to test for .EOF when opening a recordset to see if it's empty.

Unless there is some way to open a recordset not at .BOF, but I've yet to discover it!

 

[JohnPapa]

Some thoughts:

1. Why use a recordset for this simple UPDATE? It's much more efficient to send a single UPDATE statement which will update all possible rows at once rather than have the overhead of creating a recordset object, downloading the data locally and performing row by row updates one at a time.

2. If you really must use a recordset, why use a ADODB recordset as opposed to a DAO recordset?

3. When opening a recordset that does contain records (ie Not (.BOF And .EOF)), have you ever known it to open at anything other than the first record? Why .MoveFirst on open?

Your thoughts are valid.
1. Yes it is better to use the UPDATE statement instead of looping though a recordset and that is what I will use. I came across the 222.22 problem during a debug session and the problem is "Why does it pose a problem, as opposed to whether to use the UPDATE statement or loop through a recordset, to avoid the problem"

2. Is there anything against an ADODB recordset and in favor of a DAO recordset?
3. I just wanted to be extra sure that I am pointing at the first record and not rely on what you mention. By analogy, when I ORDER BY I always specify ASC and DESC

 

[JohnPapa]

The following 2 options work. Since we have a Division operation, we may get into problems with 2 decimal place accuracy. In the second option below I make sure that the Division will give accuracy to 2 decimal places. Is there a way to achieve this with the UPDATE statement?

DoCmd.SetWarnings True
DoCmd.RunSQL "UPDATE dbo_tblUnit SET numCommonExpensePercent = (numArea / " & Me!numAreaTotal & ")   WHERE intBuildingID = " & Me.intBuildingID & ";"
DoCmd.SetWarningsFalse

Do While Not rst.EOF
        rst!numCommonExpensePercent = Int(((rst!numArea / Me!numAreaTotal) + 0.005) * 100) / 100
        rst.Update
        rst.MoveNext
Loop

Edit: I make sure that the user is no allowed to enter more than 2dp for numArea and numAreaTotal is the sum of numArea so it is not more than 2dp.

 

[cheekybuddha]

I came across the 222.22 problem during a debug session and the problem is "Why does it pose a problem

Yes, interesting as an academic exercise, since it's a problem you have no need of hitting.
I wonder whether trying the following makes a difference:

' ...
  Dim TotalArea As Variant
' ...
      TotalArea = 222.22
      rst!numCommonExpensePercent = rst!numArea / TotalArea
' ...

Is there anything against an ADODB recordset and in favor of a DAO recordset?

Unless you late-bind, you need to ensure a reference is set to the ADODB library.
Whilst I prefer the syntax and semantics of the ADODB model, MS failed in getting it to work better with Access than the more native DAO. DAO is built-in to Access and usually quicker in execution than ADO.

By analogy, when I ORDER BY I always specify ASC and DESC

Being extra verbose for sake of clarity is fine, however this is not a truly analagous example because specifying ASC does not cause any extra operation to be performed, whereas .MoveFirst is an unnecessary waste of computer cycles (granted, negligible to human perception!)

 

[cheekybuddha]

Is there a way to achieve this with the UPDATE statement?

Can't you just use the same expression?

Dim strSQL As String

strSQL = "UPDATE dbo_tblUnit SET numCommonExpensePercent = Int(((numArea / " & Me!numAreaTotal & ") + 0.005) * 100) / 100 " & _
         "WHERE intBuildingID = " & Me.intBuildingID & ";"
CurrentDb.Execute strSQL, dbFailOnError