Error in Update Statement

[GSWilson77]

Hi,

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 inot 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
GW

EDIT : ADDED VBA TAGS Tommy

[mohanvijay]

insert single quote before string like below

"'BUDGET Annual'"

and you can execute query in Connection object like below

cnn.Execute "Your Query Here"

[GSWilson77]

Hi Mohanvijay,

Many thanks for responding.

The query is working fine in access, when I put the single quote round it it no longer works.

I then just left the query in it's original state and change the execute statement to cmdT.Execute "Update Fiona 1" and also tried cnn.Execute "Update Fiona 1" however the runtime error - syntax error in UPDATE statement is still occuring.

Regards

[Tommy]

cmdT.CommandText = "Update Table 1"
should this be
cmdT.CommandText = "Update Table_1"

[GSWilson77]

Hi Tommy,

Thanks for your response.

Unfortunately I am still receiving the same error message.

The table I'm updating using the Access query is called Table_1 and the actually name of the access query is "Update Table 1".

What I'd like to do in my macro is just call and execute the query I've saved in access - "Update Table 1", as my end game is to try and call an access macro from excel so the end users will not need to go near access and run updates and reports from excel.

Apologies for my previous post where I name it Update Fiona 1, this is what I called it in my actual database but just changed the name for this thread to keep it simply!!

I just wish I still had SQL Server, it would have been so much easier :(

[Tommy]

I had to look awhile but i found it. :)
cmdT.CommandType = adCmdText
should be
cmdT.CommandType = adCmdStoredProc

[GSWilson77]

Hi Tommy,

Thanks for your help so far.

I made the amendment you said, but I'm now getting another run-time error - Expected Query Name after Execute.

My vb code is as follows:

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 = adCmdStoredProc

'Set prmT = cmdT.Parameters("Acc_Date")
'prmT.Value = #12/31/2012#
cmdT.Execute "Update Table 1"
'Set cmdT = Nothing
'
'If Err <> 0 Then
' cmdT.ActiveConnection.RollbackTrans
'Else
' cmdT.ActiveConnection.CommitTrans
'End If
End Function

Have you any ideas? I've tried the execute with and without " marks but to no avail.

Hopefully we are not too far off sorting it...:)

EDIT: ADDED VBA CODE TAGS Tommy

[Tommy]

cmdT.Execute "Update Table 1"
should be
cmdT.Execute

The Query is already in the command text :)

[Tommy]

I am getting my information here : http://www.w3schools.com/ado/met_comm_execute.asp

[mohanvijay]

try like below

'*budget*amt*','BUDGET Annual',