Results 1 to 5 of 5

SQL difference between Access and SQL Server

This is a discussion on SQL difference between Access and SQL Server within the Microsoft Access forums, part of the Question Forums category; Our company just got bought out by a big corporation, and they wanted to start tracking EVERYTHING in Excel. I ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Posts
    83

    Default SQL difference between Access and SQL Server

    Our company just got bought out by a big corporation, and they wanted to start tracking EVERYTHING in Excel.

    I talked them into using a db instead, so I went out and bought the SQL Server Dev kit, a book, and went to work. Two weeks later, the db was built, the lookup tables populated, and I was starting work on the ASP forms, when word came down from the corporate big shots that we needed to be using Access instead of SQL Server. (We already had free Access 2000, and they would have had to pay to license SQL Server.)

    So, I am trying to convert everything I learned about SQL Server over to Access.

    I started with a fairly easy db, three little tables, and one quick query. Try as I might, I couldn't get the query to work. So I created the exact same db in SQL Server, and, sure enough, the query runs fine.

    So I designed the query really crappily in the Access Wizard, then checked the SQL behind it, and noticed the code is a bit different.

    Here's how I entered the query in the QA

    SELECT Author, Title
    FROM tblAuthors AS A
    INNER JOIN tblTitleAuthor AS TA
    ON A.AuthorID = TA.AuthorID
    INNER JOIN tblTitles AS T
    ON TA.TitleID = T.TitleID

    and this is the way the Access Wizard designed it

    SELECT [tblAuthors].[Author], [tblTitles].[Title]
    FROM tblTitles
    INNER JOIN (tblAuthors INNER JOIN tblTitleAuthor ON [tblAuthors].[AuthorID] =[tblTitleAuthor].[AuthorID]) ON [tblTitles].[TitleID] =[tblTitleAuthor].[TitleID];

    WTF? Do you have to nest JOINS in access SQL?

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: SQL difference between Access and SQL Server

    Quote Originally Posted by Megatron-Lives
    WTF? Do you have to nest JOINS in access SQL?
    According to my SQL reference book (SQL Visual Quickstart Guide by Chris Fehiliy) :

    ...if you use JOIN syntax in joins that involve 3 or more tables Access requires that you nest the joins by using the following general syntax:

    Code:
    SELECT columns
    FROM table1
    INNER JOIN (table2
    INNER JOIN (table3
    INNER JOIN (table4
    INNER JOIN ...)
    ON table3.column3 op table4.column4)
    ON table2.column2 op table3.column3)
    ON table1.column1 op table2.column2;
    I'd recommend this particular book if you want to know the basic differences between the some of the more popular DBMS's (Oracle, Access, SQL Server, MySQL, PostreSQL).

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Posts
    83

    Default

    cool, thanks... I didn't want to hear that, but am glad to know that was the problem

  4. #4
    Board Regular
    Join Date
    Apr 2003
    Location
    Jacksonville, FL
    Posts
    1,344

    Default Re: SQL difference between Access and SQL Server

    I'll defer to somebody that has actually used both (SQL/Access) but there's two major differences between the two applications.

    1 - 255 Concurrent Access users is a myth. Plan on less than 30 (maximum) if you want any speed at all.
    2 - Access lacks the ability to schedule maintenance/backups that is built into SQL Server. I have seen some user written tools that do some functions, but they use things like MSTask to accomplish it.

    Reality - what MS Access (all versions) can do vs what's on the MS released documentation may vary. In a cost-cutting business environment, you may need to discuss your development plans with a pro.

    Mike

  5. #5
    MrExcel MVP CT Witter's Avatar
    Join Date
    Jul 2002
    Location
    Columbus, OH
    Posts
    1,208

    Default Re: SQL difference between Access and SQL Server

    Also, depending on how many users will be concurrently using the db, you may want to use MSDE. It is a Desktop version of SQL Server that is limited to 5 connections. The best part is that it is free!! I've used it before for company intranets with ASP pages and have had no problem supporting a few hundred users (you have to properly use connections and pooling).

    Download Here: http://www.microsoft.com/downloads/d...DisplayLang=en

    Information Here: http://www.microsoft.com/sql/msde/default.asp

    HTH,
    CT

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com