What is wrong with this SQL query?

Question

This is the SQL query I wrote in MS Access 07:

IF EXISTS (Select * FROM MyTable) print 'Yes' else print 'No'

This is the error it's giving me when i run the query:

"Invalid SQL Statemenet; Expected DELETE, INSERT, PROCEDURE, SELECT, UPDATE"

The query is correct, as far as I know, I think it's from Access, can anyone help me please? Thanks

Answer 1

IF EXISTS cannot be used in MS Access.

Take a look at this link, it might be what you are after.

Answer 2

Perhaps you need a BEGIN - END.

UPDATE: Sorry to be the bearer of bad news.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22102849.html

Looks like MS Access has no control of flow execution in its SQL syntax. It only executes a single command. (based on the link above)

Apparently there is no conditional statement in MS Access.

Answer 3

As far as I know: Access has a light version from SQL. You won't be able to use TSQL. You can try to use vba to achieve what you want.

Answer 4

I have no idea about MS Access, but what about the following:

IF SELECT EXISTS (SELECT 1 FROM mytable WHERE somecondition) THEN...

The error message "Invalid SQL Statemenet; Expected DELETE, INSERT, PROCEDURE, SELECT, UPDATE" suggests you are missing one of those. You need to SELECT EXISTS, rather than just calling EXISTS.

This works in PostgreSQL, but I'm not sure if Access is different.

Answer 5

You can't use IF EXISTS in Access. EXISTS() is allowed but only as an evaluative expression, not a procedural one. The EXISTS() expression returns True or False (in the form of -1 and 0).

Imagine that you could use a procedural expression... where are you trying to PRINT the result? There is no place for text results to be printed.

Please describe more clearly what problem you're trying to solve, and where you want the results to appear, and we can help you better.

Taking a wild guess, if you did want a query to return 'Yes' or 'No' then this can do it for you:

SELECT Iif(EXISTS (SELECT * FROM MyTable), "Yes", "No") AS MyTableHasRows
FROM Numbers
WHERE Num = 1;

There's a problem here, though: Access doesn't allow SELECT statements that don't refer to a table. (SQL Server allows this, and Oracle offers the fake DUAL table to select from.) One way around this is to select from a table that has only one row. My query above selects from a Numbers table that's in my test database, where only one row has Num = 1. Selecting from a table with many rows makes the engine do extra work for no reason.

If you want to do this checking in code, here's the best-performing way I can think of:

If CurrentDb().OpenRecordset("SELECT Top 1 * FROM MyTable").RecordCount > 0 Then
   Debug.Print "Yes"
Else
   Debug.Print "No"
End If

This is logically identical to EXISTS and will perform better than COUNT(*) which has to look at every row in the table.

Answer 6

As others said before me, Access doesn't know IF EXISTS.
You could do something like this in VBA:

Public Sub Foo()

    If Not IsNull(DLookup("SomeColumn", "MyTable")) Then
        MsgBox "yes"
    Else
        MsgBox "no"
    End If

End Sub

Answer 7

A few points:

1. Access SQL uses iif(), not IF
2. Access SQL doesn't use EXISTS
   Edit:
   Actually it does work, even though I couldn't find it in the help files. This works fine:
   Select (IIf(Exists (SELECT 1 FROM [Table1]),'Y','N')) as A from [Table1];
   /Edit
3. DCount is the key:
   iif(
DCount("AnyFieldName","TableName")>0,
"Yes",
"No"
4. I suggest bookmarking this site for future reference. Really helpful.
   http://www.techonthenet.com/access/functions/
5. The Access help files are actually pretty helpful and straightforward. They describe parameters, output, give examples, etc. I recommend using them frequently.

Answer 8

If you want to know if a table has records in it, you can check CurrentDB.TableDefs("MyTable").Recordcount. That will never be 0 for a table with records in it.

Now, where you can use that is a different kettle of fish, but your question is so vague as to make it impossible to provide anything other than generalized approaches to solving the problem (which hasn't really been defined in the first place).