locked
Syntax error in FROM clause RRS feed

  • Question

  • User-1693623980 posted

    Hi,

    This code works fine in Sql Server. it's for pagination.

    but it throws error in Microsoft access.

    The first error: Invalid Sql statement; expected DElETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

    when I remove this first three line

    declare @RecordCount int

    declare @PageSize int=4

    declare @PageIndex int=3

    the error change to: Syntax error in FROM clause

    What is wrong?

    declare @RecordCount int
    declare @PageSize int=4
    declare @PageIndex int=3
    Select @RecordCount= Count(*)  From tbl_product
    
    
    SELECT *
    FROM (
        SELECT Top (@PageSize) sub.*
        FROM (
            SELECT TOP ((@RecordCount) - (((@PageIndex) - 1) * (@PageSize)))  tbl_product.*
            FROM tbl_product
            ORDER BY tbl_product.pd_id Desc
        ) sub
       ORDER BY sub.pd_id Asc
    ) subOrdered
    ORDER BY subOrdered.pd_id

    what is my worng?

    tanx

    Friday, October 4, 2013 6:32 AM

Answers

  • User753101303 posted

    Hi,

    Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 4, 2013 6:54 AM
  • User-1199946673 posted

    Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).

    And as an addition, in Access, parameters cannot be used in the TOP clause. Also Acces doesn't support batched statements

    In order to achieve what you want in Access, you need to create the query in code, (beware of SQL injections!)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 4, 2013 7:15 AM

All replies

  • User-1716253493 posted
    Set @RecordCount = Select Count
    (*) From tbl_product;
    Friday, October 4, 2013 6:44 AM
  • User753101303 posted

    Hi,

    Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 4, 2013 6:54 AM
  • User-1199946673 posted

    Sql Server and Microsoft Access are different. I doubt you'll be able to just take a SQL Server procedure and just copy it over to Access (if I remember you can have parameters which are declare differently but you don't have local variables).

    And as an addition, in Access, parameters cannot be used in the TOP clause. Also Acces doesn't support batched statements

    In order to achieve what you want in Access, you need to create the query in code, (beware of SQL injections!)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, October 4, 2013 7:15 AM
  • User-1693623980 posted

    yes hans_v you are right.

    this two query work fine seperately

    SELECT Count (*) from tbl_product

    SELECT *
    FROM (
        SELECT Top 4 sub.*
        FROM (
            SELECT TOP 11  tbl_product.*
            FROM tbl_product
            ORDER BY tbl_product.[pd_id] Desc
        ) sub
       ORDER BY sub.[pd_id] Asc
    ) subOrdered
    ORDER BY subOrdered.[pd_id]

    but they don't work when i write them after each other.

    so when i cant pass parameter to top clause i can't use it for dynamic pagination.



    Friday, October 4, 2013 7:38 AM
  • User-1199946673 posted

    so when i cant pass parameter to top clause i can't use it for dynamic pagination.

    Correct, in order to do that, you need to create the query dynamically in code behind.

    Friday, October 4, 2013 7:48 AM