Access VBA - Runtime error in Update Macro

Question

I'm fairly new to vba (and to this website - so apologies if I post this incorrectly), previously a SQL developer, but unfortunately my new job involves working in access 2010 only. I'm trying to build a vba macro which will run various update statements. I have built the queries in access and I'm trying to call the query in vba. I have tried doing this with one access query and I'm running into a run time error - syntax error in UPDATE statement. The query runs fine in access but the vba function fails with the runtime error.

The following is my vba function:

Public Function TestUpdate1()
Dim cmdT As ADODB.Command
Dim cnn As ADODB.Connection
Dim prmT As ADODB.Parameter

Set cnn = Application.CurrentProject.Connection
Set cmdT = New ADODB.Command
Set cmdT.ActiveConnection = cnn
cmdT.CommandText = "Update Table 1"
cmdT.CommandType = adCmdText

'Set prmT = cmdT.Parameters("Acc_Date")
'prmT.Value = #12/31/2012#
cmdT.Execute

'Set cmdT = Nothing
'
'If Err <> 0 Then
' cmdT.ActiveConnection.RollbackTrans
'Else
' cmdT.ActiveConnection.CommitTrans
'End If
End Function

When I debug the function the error occurs on line cmdt.Execute.

The sql query (Update Table 1) it is executing is as follows:

UPDATE Table_1 SET Product = IIf(Contract Like "*budget*amt*","BUDGET Annual",
IIf(Contract Like "*CLASSIC*AMT*","CLASSIC Annual",
IIf(Contract Like "*essential*AMT*","ESSENTIAL Annual",
IIf(Contract Like "*P*PLUS*AMT*","Premier Plus Annual",
IIf(Contract Like "*SELECT*AMT*","SELECT Annual",
IIf(Contract Like "*prestige*AMT*","PRESTIGE Annual",
IIf(Contract Like "*GAP*","GAP Productl",
IIf(Contract Like "*SINGLE*TRIP*","SINGLE TRIP",
IIf(Contract Like "*premier*","PREMIER Annual",
IIf(Contract Like "*standard*","STANDARD Annual",
IIf(Contract Like "*EVAC*","European VAC","???"))))))))))), End_Date =  Depart_Date+Days;

Any help you can give on the above would be gratefully appreciated.

Many thanks

Answer 1

this code is trying to execute the SQL statement: Update Table 1

cmdT.CommandText = "Update Table 1"
cmdT.CommandType = adCmdText
cmdT.Execute

to execute a query named Update Table 1, you need this code:

Docmd.OpenQuery "Update Table 1" 

if you don't want warnings about the number of rows that are about to be updated,

With DoCmd
     .SetWarnings False
     .OpenQuery "Update Table 1"
     .SetWarnings True
End With  

This is code I use to run a parametrized query using an access project and ADODB

Dim cmdl As ADODB.Command
Dim StrCon As New ADODB.Connection
Dim rsRecSet As New ADODB.Recordset

StrCon.Open CurrentProject.Connection

'Set CONNECTION timeout property
StrCon.CommandTimeout = 0

'Create a new command object to process the stored proc
Set cmdl = New ADODB.Command

With cmdl
    .ActiveConnection = StrCon
    'set COMMAND timeout property - query can time out on either the connection OR the command
    .CommandTimeout = 0
    .CommandText = "spCrossTabRun"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters(1).value = "MyValue1"
    .Parameters(2).value = "MyValue2"
    .Parameters(3).value = "MyValue3"
    Set rsRecSet = .Execute()
End With

Answer 2

I have a couple of centralized functions to return read-only or read/write access to an adodb recordset. Like:

Public Function dbWrite(SQLQuery As String, Optional blDynamic As Boolean) As ADODB.Recordset
'Centralized function to read data from db, return a EDITABLE recordset
Dim r As New ADODB.Recordset

If blDynamic = True Then
    r.Open SQLQuery, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, dbSQLPassThrough
Else
    r.Open SQLQuery, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic, dbSQLPassThrough
End If

Set dbWrite = r

Exit Function
End function

so to call it, within a new function:

Public Function SomeFunction
Dim r As New ADODB.Recordset
Dim s As String
set r = dbwrite("SELECT * FROM TABLE;")
s = "My Parameterized Value"
r.AddNew
     r![Fieldname] = s
     r.Update
r.Close
set r = nothing
End function

Having the dbWrite function makes things easy, and parsing out the fieldnames one at a time makes it easy to call another function, a simple IF, or whatnot.