Update Query Syntax Error

[Rofeu]

Hello,

I'm trying to run an update query on an Excel sheet I have.
Below is the SQL I'm running but it's giving me a syntax error and I can't find where I'm going wrong:

SQL_Update_Source_OPP = "UPDATE `'OppTracking TAB Delimited-v2$'` A "
SQL_Update_Source_OPP = SQL_Update_Source_OPP & "SET A.[Current_Sales_Stage] = 'Lead' " & vbNewLine
SQL_Update_Source_OPP = SQL_Update_Source_OPP & "WHERE A.[Current_Sales_Stage]='05. Lead' " & vbNewLine
SQL_Update_Source_OPP = SQL_Update_Source_OPP & "SELECT A.* FROM `'OppTracking TAB Delimited-v2$'` A;"

I have a feeling that it might have to do with the way I'm running it:

With Workbooks.Open(DBQ).Sheets("OppTracking TAB Delimited-v2")
    .QueryTables.Add(Connection, .Cells(1, 1), SQL_Update_Source_OPP).Refresh
End With

I'm using an odbc connection to the Excel file. Maybe this type of connection is not suitable for update-queries??

Any ideas will be appreciated.

Cheers,

Roel

 

[PHV]

For me, a QueryTable is only for a SELECT instruction.
You may consider to play with DAO or ADODB.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

ok, will do that. Thanx

Cheers,

Roel