12 Replies - 721 Views - Last Post: 05 June 2016 - 07:35 AM

#1 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Connection.Execute problems

Posted 05 June 2016 - 05:25 AM

Hello Guys,

I like to set the field "Bemerkung" with Connection.Execute.

This is my Listing:
Sub Transaktionen()
    Dim cnn As New ADODB.Connection
    Dim rs_1 As New ADODB.Recordset
    Dim rs_2 As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    Set cnn = CurrentProject.Connection
    cnn.BeginTrans
    
    With cmd
        Set .ActiveConnection = cnn
        .CommandText = "UPDATE Belegung SET Mietpreis = Mietpreis * 1.01"
        .CommandType = adCmdText
        .Execute
    End With
    
    With rs_1
        .Open "SELECT MieterNr, SUM(Mietpreis) AS MietPreisSumme FROM Belegung GROUP BY MieterNr;", cnn, adOpenDynamic, adLockOptimistic, adCmdText
        With cnn
            .Execute "UPDATE Mieter SET Bemerkung = Mietpreissumme: " & rs_1!mietpreisSumme & " WHERE MieterNr = " & rs_1!MieterNr, , adCmdText
        End With
    .Close
    End With
    
    If MsgBox("Sollen die Änderungen jetzt gespeichert werden?", vbYesNo + vbQuestion) = vbYes Then
        cnn.CommitTrans
    Else
        cnn.RollbackTrans
    End If
    Set rs_1 = Nothing
    Set rs_2 = Nothing
End Sub



So my problem is: I get a message: missing operator in query 'mietpreissumme: 2069'
I have no idea, what i´m doing wrong. I hope anyone can help me with a tip.

Kindly regards,
Tobias

Is This A Good Question/Topic? 0
  • +

Replies To: Connection.Execute problems

#2 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 05:33 AM

Quote

missing operator in query 'mietpreissumme: 2069'

What are you trying to achieve with the colon ":"? It isn't an Access operator.

More specifically,
SET Bemerkung = Mietpreissumme: " & rs_1!mietpreisSumme

what are you trying to set Bemerkung to?
Was This Post Helpful? 0
  • +
  • -

#3 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 05:38 AM

1. The colon is only a string "mietpreissumme:", like shoppinglist: or price:
Do you think access thinks the colon is an operator?

I deleted the colon, but the same error is coming up

This post has been edited by andrewsw: 05 June 2016 - 05:51 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 05:46 AM

So i created the procedur again, but the same error is coming up.
Option Compare Database
Option Explicit

Sub Transaktionen()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    Set cnn = CurrentProject.Connection
    cnn.BeginTrans
    
    With cmd
        Set .ActiveConnection = cnn
        .CommandText = "UPDATE Belegung SET Mietpreis = Mietpreis * 1.01"
        .CommandType = adCmdText
        .Execute
    End With
    
    With rs
        .Open "SELECT MieterNr, SUM(Mietpreis) AS MietPreisSumme FROM Belegung GROUP BY MieterNr;", cnn, adOpenDynamic, adLockOptimistic, adCmdText
    End With
    
    With cnn
        .Execute "UPDATE Mieter SET Bemerkung = Mietpreissumme: " & rs!mietpreisSumme & " WHERE MieterNr = " & rs!MieterNr, , adCmdText
    End With
    
    If MsgBox("Sollen die Änderungen jetzt gespeichert werden?", vbYesNo + vbQuestion) = vbYes Then
        cnn.CommitTrans
    Else
        cnn.RollbackTrans
    End If
    
    rs.Close
    cnn.Close
    Set rs = Nothing
End Sub


Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 05:52 AM

If it is part of a string/text value then it needs to be surrounded by single quotes ', SET field = 'text value':

Preferably you should switch to using parameters then you wouldn't have to be concerned with quotes or colons.

Command Object Parameters

Quote

Do you think access thinks the colon is an operator?

No but I thought you might ;) The error message is because of the missing quote delimiters.
Was This Post Helpful? 0
  • +
  • -

#6 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 05:57 AM

First, thanks for your help, it is working. But one last thing, he is only updating one "MieterNr", but he should updating all "MieterNr" from the Recordset.
BTW: The failure was the missing quotes.

Correct Code:
Sub Transaktionen()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    Set cnn = CurrentProject.Connection
    cnn.BeginTrans
    
    With cmd
        Set .ActiveConnection = cnn
        .CommandText = "UPDATE Belegung SET Mietpreis = Mietpreis * 1.01"
        .CommandType = adCmdText
        .Execute
    End With
    
    With rs
        .Open "SELECT MieterNr, SUM(Mietpreis) AS MietPreisSumme FROM Belegung GROUP BY MieterNr;", cnn, adOpenDynamic, adLockOptimistic, adCmdText
    End With
    
    With cnn
        .Execute "UPDATE Mieter SET Bemerkung = 'Mietpreissumme: " & Round(rs!mietpreisSumme, 2) & "' WHERE MieterNr = " & rs!MieterNr, , adCmdText
    End With
    
    If MsgBox("Sollen die Änderungen jetzt gespeichert werden?", vbYesNo + vbQuestion) = vbYes Then
        cnn.CommitTrans
    Else
        cnn.RollbackTrans
    End If
    
    rs.Close
    cnn.Close
    Set rs = Nothing
End Sub


This post has been edited by andrewsw: 05 June 2016 - 06:18 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 06:17 AM

Note that you don't have to quote the previous post in full, there is a Reply button further down the page, or use the Fast Reply box.

Quote

But one last thing, he is only updating one "MieterNr", but he should updating all "MieterNr" from the Recordset.

It is updating Bemerkung not MieterNr. There is a WHERE clause so it will only update rows that meet your criterion on MieterNr. If you wanted to update all rows then you would omit the WHERE clause, but it is unusual to want to update ALL rows of a table, so I suggest that you check what your WHERE clause is achieving.
Was This Post Helpful? 0
  • +
  • -

#8 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 06:24 AM

The WHERE Clause is correct.

I think my explanation is very bad.
So, after the Open Method rs.open the recordset has 6 records. Every Record has a field named "Bemerkung"
So with my cnn.Execute i like to update every field "Bemerkung" with Round(rs!mietpreisSumme, 2), but only the record with number 1 gets updated.
The other 5 not.

I thought a can use While Wend, but for the connection object there is no .End of field property.
i´m so close, i know it.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 06:33 AM

Quote

but only the record with number 1 gets updated.

What does this number 1 refer to?

If you want to loop through all the rows of rs, performing an update for each one, then yes you can use While..Wend as shown here, While (Not .EOF). If you study joins though, which is essential knowledge when working with database, you would be able to perform these updates in a single statement.
Was This Post Helpful? 0
  • +
  • -

#10 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 06:47 AM

Thank you so much for your time and help :)
Now is all working, the solution is:
I must use while wend.
Here is the complete and working code.

Option Compare Database
Option Explicit

Sub Transaktionen()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    
    Set cnn = CurrentProject.Connection
    cnn.BeginTrans
    
    With cmd
        Set .ActiveConnection = cnn
        .CommandText = "UPDATE Belegung SET Mietpreis = Mietpreis * 1.01"
        .CommandType = adCmdText
        .Execute
    End With
    
    With rs
        .Open "SELECT MieterNr, SUM(Mietpreis) AS MietPreisSumme FROM Belegung GROUP BY MieterNr;", cnn, adOpenDynamic, adLockOptimistic, adCmdText
    While Not .EOF
    With cnn
        .Execute "UPDATE Mieter SET Bemerkung = 'Mietpreissumme: " & Round(rs!mietpreisSumme, 2) & "' WHERE MieterNr = " & rs.Fields("MieterNr"), , adCmdText
    End With
        .MoveNext
        Wend
    End With
    
    If MsgBox("Sollen die Änderungen jetzt gespeichert werden?", vbYesNo + vbQuestion) = vbYes Then
        cnn.CommitTrans
    Else
        cnn.RollbackTrans
    End If
    
    rs.Close
    cnn.Close
    Set rs = Nothing
End Sub



But one thing i don´t understand. Why i must use while wend?
I thought the cnn.execute method will automatically updating all records in the recordset. Because i´m using where clause.
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 07:03 AM

As I mentioned, you could do this with a single Execute call, but you would need to use a table join for it to know which row to update in Mieter for each value of MieterNr.

Yes, an UPDATE command would update all rows of a table if it didn't include a WHERE clause, but you don't want to set everything to a value of, say, 50, you want to update rows in one table that match rows (MieterNr's) of another table, requiring a join.
Was This Post Helpful? 1
  • +
  • -

#12 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6409
  • View blog
  • Posts: 25,892
  • Joined: 12-December 12

Re: Connection.Execute problems

Posted 05 June 2016 - 07:14 AM

Actually, it is coming back to me that Access is crippled as far as performing updates with aggregate functions:

ACC: Update Query Based on Totals Query Fails

Nevertheless, comprehensive knowledge of how joins work, and the different types of join, is essential to working with databases.
Was This Post Helpful? 1
  • +
  • -

#13 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: Connection.Execute problems

Posted 05 June 2016 - 07:35 AM

SQL is my weakness at this moment, much to learn.
Thank you for your help :)
Maybe you can help me by my next problem. Post is coming up in a few sec.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1