SQL difference between Access and SQL Server

Megatron-Lives

Board Regular
Joined
Jul 7, 2002
Messages
83
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?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Megatron-Lives said:
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).
 
Upvote 0
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
 
Upvote 0
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!! :p 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/...d1-a0bc-479f-bafa-e4b278eb9147&DisplayLang=en

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

HTH,
CT
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top