How to Combine 2 SQL Update Statements?

[sbrown106]

Hi , Could somebody help me with this syntax please for how to combine multiple SQL statements in vba, to change more than one value in a table

Ive been experimenting with this from the microsoft site but keep getting syntax errors the code I'm trying is below

        Dim dbs As Database


        Set dbs = CurrentDb
        
        dbs.Execute "UPDATE Employees " _
            & "SET ReportsTo = 5 " _
            & "SET Date = Now() ;"

 

[sonic8]

Just comma separate the columns to be set:

dbs.Execute "UPDATE Employees " _
            & "SET ReportsTo = 5 , " _
            & "       Date = Now() ;"

 

[arnelgp]

if you use Access Reserved word enclosed them in square brackets( [ ] )
in your query.
Date is reserved so you must enclosed it:

dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 , " _
& " [Date] = Now() ;"


Or you can include the tablename (and leave off the brackets):

dbs.Execute "UPDATE Employees " _
& "SET Employees.ReportsTo = 5 , " _
& " Employees.Date = Now() ;"

 

[sbrown106]

Just comma separate the columns to be set:

dbs.Execute "UPDATE Employees " _
            & "SET ReportsTo = 5 , " _
            & "       Date = Now() ;"

thanks - would you know why I am getting 'too few parameters' message with this code below please?, Ive tried it with the SET statement infront of the fldChangeDate as well but then I get a syntax error

        dbs.Execute "UPDATE Employees " _
            & "SET [ReportsTo]=0, " _
            & "    [fldChangeDate] = #01/01/1990# ;"

 

[nectorch]

Sorry May be I did not understand your question properly, the simple way is just create an SELECT QUERY , then convert that query into UPDATE AND CALL its name in VBA

Name of a query = QryGrnTagDebit

UPDATE tblGrnAccountDebit SET tblGrnAccountDebit.GrnStatus = [Forms]![FrmGrnPostings]![CboAuthority]
WHERE (((tblGrnAccountDebit.AnalysisID)=[Forms]![FrmGrnPostings]![CboGrnSus]));

Call it as below

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryGrnTagDebit"

 

[arnelgp]

check the fieldname if it is correct.
run the update on Each fields:

        dbs.Execute "UPDATE Employees " _
            & "SET [ReportsTo]=0;"

        dbs.Execute "UPDATE Employees " _
            & "SET [fldChangeDate] = #01/01/1990#;"

which one did not passed?

 

[theDBguy]

thanks - would you know why I am getting 'too few parameters' message with this code below please?, Ive tried it with the SET statement infront of the fldChangeDate as well but then I get a syntax error

        dbs.Execute "UPDATE Employees " _
            & "SET [ReportsTo]=0, " _
            & "    [fldChangeDate] = #01/01/1990# ;"

I recommend storing the SQL string in a variable, so you can print it and then paste it into the query designer for troubleshooting purposes.

 

[Cronk]

Is the field ReportsTo a Text field in which case it should be
.... SET [ReportsTo]='' ...

 

[nectorch]

The answer here is that you are trying to open the record source by a query , this means that your VBA code should take into account of that:

Dim dbs As Database
Dim prm As DAO.Parameter
Set dbs = CurrentDb
For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm      
dbs.Execute "UPDATE Employees " _
            & "SET ReportsTo = 5 " _
            & "SET Date = Now() ;"

 

[sbrown106]

check the fieldname if it is correct.
run the update on Each fields:

        dbs.Execute "UPDATE Employees " _
            & "SET [ReportsTo]=0;"

        dbs.Execute "UPDATE Employees " _
            & "SET [fldChangeDate] = #01/01/1990#;"

which one did not passed?

Hi Arnelgp,It was the fldChangeDate I kept getting syntax errors with, in the end I ended up using nectorch's answer just because the layout was a lot easier to understand in the SQL query - I was getting confused as to how to put all that into one string, my vba not that good.

 

[Pat Hartman]

When you get a Too Few Parameters error message, you have a typo somewhere.

Rather than building the SQL string in VBA, build it using the QBE and that will often avoid typos.

Also "Date" is a reserved word and should NEVER be used as a column name nor should "Name" or any other property or function name. They always cause a problem unless you are diligent in

Here's an example of the badness of using Name. It is the worst of all

Private Sub Name_BeforeUpdate(Cancel As Integer)
    MsgBox "Name = " & Me.Name & "  ----   [Name] = " & Me.[Name] & "  '''  txtName = " & Me.txtName
End Sub

Access gives you fair warning to not use Name but then it lets you use it anyway And here's what happens. I created two controls bound to the Name column. One I left at the default control name which equals the column name == Name. The second control has the name ==txtName. That is the way you get to reference name in your code