Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    SQL in Access (Access XP)

    I know that SQL is a standard language for queries, but I have noticed some differences between SQL in Access and SQL in SQL Server.
    For instance, in SQL Server you usually use the CREATE TABLE synatx to make a new table, where as in Access it sets it up as SELECT Customers.* INTO [CTCustomers] FROM Customers.
    Why is there a difference, and will Access SQL still operate if I copy it into SQL Server?
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL in Access (Access XP)

    CREATE TABLE and SELECT INTO are valid in both Jet SQL (the dialect used by Access) and in ANSI SQL (the dialect used by SQL Server). They act differently:

    CREATE TABLE will create the structure of a table - you specify the fields (name, type, size) and perhaps some indexes - but it won't populate the table with records.

    SELECT INTO will create a new table and populate it with records. Access will determine the names, types and sizes of the fields in the new table based on the source records.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL in Access (Access XP)

    Thanx Hans,
    I do not have much knowledge on ANSI SQL.
    Your answer is very clear, but would you know if there is a difference? Is ANSI SQL more flexible or does it have more clauses that JET SQL.
    (You do not have to give examples...I am just querying out of interest??!)
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: SQL in Access (Access XP)

    Open the online help in Access 2002.
    Activate the Contents tab (if necessary)
    Locate Microsoft Access Help > Microsoft Jet SQL Reference > Microsoft Jet SQL Reference > Overview > Comparison of Microsoft Jet SQL and ANSI SQL.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL in Access (Access XP)

    Ah. thanx...I will do that! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: SQL in Access (Access XP)

    As you've undoubtedly discovered, there are significant differences between various brands of SQL. The Jet SQL version has been crafted to give a user maximum control over the Jet database engine, and is considered somewhat "non-standard" while SQL Server is much closer to the ANSI standard, but still has it's own vagaries. One of the beauties of Access is that the ODBC driver for SQL Server handles all the translation functions for you, so until you have need to construct views, stored procedures or triggers, you can continue to use the Jet dialect on linked SQL Server tables. Also note, that since Jet 4.0 you have been able to use a database option to switch to ANSI SQL, though few of us ever do.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •