SQL Statement that worked with SQL Database doesnt work with Access Database

Question

I am creating a GUI in C# and I have the following line of code to get the elements from lowerPageBound to upperPageBound.

command.CommandText = "Select Top " + rowsPerPage + " " +
        CommaSeparatedListOfColumnNames + " From " + tableName +
        " WHERE " + columnToSortBy + " NOT IN (SELECT TOP " +
        lowerPageBoundary + " " + columnToSortBy + " From " +
        tableName + " Order By " + columnToSortBy +
        ") Order By " + columnToSortBy;
adapter.SelectCommand = command;
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);

The generated SQL statement gives me an error(adapter.Fill(table) is executed) when used on an access database but works fine on a sql database.

Heres the SQL that is generated:

Select Top 25 [ID], [Business Process], [Tier Level], [Application], [CI ID], [Server], [Server Function], [Data Center], [HA], [DR Equip], [Procedure], [Procedure Tested], [Type], [Outcome], [Overall Status] From Data WHERE ID NOT IN (SELECT TOP 0 ID FROM Data ORDER BY ID) ORDER BY ID;

And the error I recieve:

Syntax error in query expression 'ID NOT IN (SELECT TOP 0 ID FROM Data ORDER BY ID)'.

Ive tried to fix this for hours but I've had no luck. It doesn't make sense why the same statement wouldnt work on an access database. Any help is appreciated!!

Access sql syntax is not the same as Sql server (or other) sql varieties. What does the sql statement look like after you've finished building the string? — automatic
– automatic, Commented Jul 26, 2012 at 18:13
Can you tell me what is throwing it off then? I assumed all SQL was the same... The SQL thats generated is right underneath the C# code. — Mohammed
– Mohammed, Commented Jul 26, 2012 at 18:15
@Mohammed Do you have the ability to execute SQL directly against the Access DB? That might be the easiest way to write your query, then rebuild it in C# — Dan
– Dan, Commented Jul 26, 2012 at 18:19
@Dan What do you mean directly against? Like try and generate it with the Access wizard? — Mohammed
– Mohammed, Commented Jul 26, 2012 at 18:22
@Mohammed Yes, that could be one option. Although I don't like the Access wizard necessarily, but you can open up the SQL view (or something like that) and write straight SQL. HansUp's answer below probably has your solution though! — Dan
– Dan, Commented Jul 26, 2012 at 18:24

HansUp · Accepted Answer · 2012-07-26 18:39:49Z

4

The Access db engine will throw an error with this part of your query.

SELECT TOP 0 ID FROM Data ORDER BY ID

You can break out that section and test it as a new Access query. Unfortunately, the error message is not very helpful: "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." And that's sort of a generic error message the db engine gives you when it's unable to describe the problem precisely.

Basically, it all boils down to the fact you can not do SELECT TOP 0 in Access SQL.

Also, once you resolve the problem about SELECT TOP 0, you need an ORDER BY clause in the outer query. Without the ORDER BY, the rows returned by TOP 25 is arbitrary.

edited Jul 26, 2012 at 18:39

answered Jul 26, 2012 at 18:22

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammed Over a year ago

Thanks for the answer. I guess Im going to have to find another way to select all the elements between two rows.

ErikE · Accepted Answer · 2012-07-27 01:42:42Z

3

To perform your paging function, you can:

Leave out the NOT IN clause when pagestart is 0.
Use the method SELECT TOP pagesize * FROM (SELECT TOP pagestart + pagesize * FROM X ORDER BY Condition) AS Alias ORDER BY Condition DESC. The important part is that the second ORDER BY is in the reverse direction of the first. You may need a final ORDER BY to get the correct order, though the client should be capable of this. Note that prior to Access 2007 the designer would change that derived table (the part in parentheses) to [SELECT ...]. AS Alias, but now it stays.

There are more methods if these are unsatisfactory.

Less as an answer to your question and more to be informative, in addition to what HansUp has said about TOP 0 being unsupported, you may also run into other differences between SQL Server and Access syntax. To help alleviate this, you may want to look into the DB-level setting to use SQL-Server syntax. It's not perfect, but allows some syntax that would normally fail in Access. Be aware that switching in the middle of a project can be problematic. See info on ANSI 89 and ANSI 92 syntax incompatibilities.

edited Jul 27, 2012 at 1:42

answered Jul 26, 2012 at 19:27

ErikE

50.7k23 gold badges157 silver badges201 bronze badges

4 Comments

Fionnuala Over a year ago

See office.microsoft.com/en-us/access-help/…

ErikE Over a year ago

Thanks for the catches. I will update. Do you know about versions prior to Access 2003--would they accept the parentheses form?

ErikE Over a year ago

@HansUp Do you know about versions prior to Access 2003--would they accept the parentheses form?

HansUp Over a year ago

I don't remember those well enough, Erik. I used older versions (97 and 2000) very little, a long time ago, and I'm an old guy so my memory is shaky anyway. :-)

Collectives™ on Stack Overflow

SQL Statement that worked with SQL Database doesnt work with Access Database

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related