Excel-to-Excel data connection gives limited SQL options

I'm mainly an Excel user and only dabble into the techie/programming sides of things when I need to, so assume I'm a bit of a newbie when it comes to all these data links, but:

On a number of occasions, in Excel 2010, I have gone to

• Data -> From Other Sources... -> From SQL Server

and this has given me the ability to connect to our database tables. After I've done that, I can go to

• Data -> Refresh All [V] -> Connection Properties... -> Definition tab

and change the Command Type to SQL. And then I can paste in the same sort of SQL I use in Microsoft SQL Server 2012, and everything is great. It connects to the database, extracts the required information according to the SQL code, and I get what I want.

But then I have a different connection, to a different Excel workbook. I've tried creating the connection in two ways:

1) Data -> From Other Sources... -> From Microsoft Query -> Excel Files

2) Data -> From Access -> and then browse and choose the Excel file instead

Both of these methods work, and get me a link to my Excel file. But both of them have the same issue when I try to edit the SQL query as I would usually:

I go to the Connection Properties... -> Definition tab -> Command type, and it says SQL, and then I edit the SQL code that appears in the Command Text box underneath.

My problem is that the SQL now doesn't recognise syntax that it was happy to recognise when I did it via the SQL Server method. For example:

• If I try to put in a comment using either -- or /* ... */ syntax, I get the error: [Microsoft][ODBC Excel Driver] Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
• If I try to add a column saying: CURRENT_TIMESTAMP AS [Refresh Date], then I get [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

The SQL code currently is pretty simple, just:

SELECT
[ID],
[Gift]
FROM
    [Data$]
WHERE
    [Gift] <> ''
    AND
    [Gift] <> 'Not a Gift'
    AND
    [ID] <> ''

All I want is a third column, next to ID and Gift, called [Refresh Date]. I guessed this would be:

SELECT
[ID],
[Gift],
CURRENT_TIMESTAMP AS [Refresh Date]

But that doesn't work as described above.

I've noticed that the Connection Type is different, depending on which of the connection methods I use - 'Database Query' for the Excel Files method, 'Excel File' for the Access Method, and OLE DB Query for the SQL Server one; I don't know if that affects what SQL language I'm able to use. The connection string is also completely different for the different methods, but I'm not sure if that's related to why I can't use more than just the basic SQL commands.

The actual aim is for me to be able to get a cell in Excel that (a) puts in the date when it is refreshed by someone doing Data -> Refresh All, and (b) doesn't put that date in if the refresh fails for that table. I thought the easiest way was just to create a column in the table with the current date, but I'm wondering if the SQL limitation stops that?

Is there a way to get more functionality into the SQL, like I do with my SQL server link? Or as a workaround is there some other method, perhaps via VBA, to pick up whether a Table has successfully refreshed or not, so I could write today's date in a cell somewhere else using VBA if it has indeed refreshed successfully (I already have the VBA to do the Data->RefreshAll for me)?

Thanks in advance!

The flavor of SQL you are using is closer to Access DAO SQL than MS SQL's T-SQL. You won't have CURRENT_TIMESTAMP as an option but Now() should be available as an alternative.

SELECT
[ID],
[Gift],
Now() AS [Refresh Date]