SQL Server vs Access JOIN Syntax

[mdlueck]

Greetings,

I was trying to use the Access query builder to speed up the creation of a SELECT statement... so I do not make a typo in one of the column names.

I have been aware that Access prefers complex / messy JOIN syntax, involving many () operators.

So, wishing to trick it into using neat and tidy JOIN syntax which SQL Server accepts, I changed its JOIN text to the format SQL Server accepts... taking care not to mess up the Access Linked Table names which are different than the actual SQL Server names, slightly.

That done, Access will not accept the simplified JOIN syntax!

SQL that works in SQL Server Management Console:

SELECT [met].[title]
FROM [dbo].[parts] AS [p]
LEFT JOIN [dbo].[partmetoolinglink] AS [metl] ON [p].[id] = [metl].[partid]
LEFT JOIN [dbo].[metooling] AS [met] ON [metl].[metoolingid] = [met].[id]
LEFT JOIN [dbo].[metoolingtooltype] AS [mettt] ON [met].[tooltypeid] = [mettt].[id]
LEFT JOIN [dbo].[vendors] AS [v] ON [met].[vendorid] = [v].[id]
LEFT JOIN [dbo].[auth] AS [a] on [met].[authid] = [a].[id]
WHERE [p].[id] = 2;

Access (non pass through) version which "should" work:

SELECT [met].[title]
FROM [dbo_parts] AS [p]
LEFT JOIN [dbo_partmetoolinglink] AS [metl] ON [p].[id] = [metl].[partid]
LEFT JOIN [dbo_metooling] AS [met] ON [metl].[metoolingid] = [met].[id]
LEFT JOIN [dbo_metoolingtooltype] AS [mettt] ON [met].[tooltypeid] = [mettt].[id]
LEFT JOIN [dbo_vendors] AS [v] ON [met].[vendorid] = [v].[id]
LEFT JOIN [dbo_auth] AS [a] on [met].[authid] = [a].[id]
WHERE [p].[id] = 2;

Error message is:
"Syntax error (missing operator) in query expression"
and it gives a large portion of the JOIN area of the query.

I used a text editor to swap out the underscore deliminator character for the SQL Server ].[ syntax to switch from the Access version to SQL Server. NO OTHER EDITS! So I know the Access version "should" work.

And pasted into another query window, the SQL Server version via Pass Through query works as well.

Just the sharp spot is I can NOT use the GUI query builder to add the columns / do the typing for me in Pass Through mode.

I will begin typing... suggestions how to get Access to write the SELECT statement for me next time?

 

[mdlueck]

I had forgotten that the SQL Server Management Studio has type-ahead capabilities where it guesses what column you might be typing.

I used that instead of Access to build the SELECT statement - to obtain the columns needed to be selected.

 

[vbaInet]

I had forgotten that the SQL Server Management Studio has type-ahead capabilities where it guesses what column you might be typing.

I used that instead of Access to build the SELECT statement - to obtain the columns needed to be selected.

Meaning you've solved the problem?

 

[mdlueck]

Meaning you've solved the problem?

Found a work-around...

I would still be interested in knowing why Access insists on ugly join syntax.

 

[vbaInet]

That's Access for you. They all have their own conventions and they have refused to agree to a common SQL syntax. Access expects you to group the JOINS using parens.

 

[mdlueck]

Access expects you to group the JOINS using parens.

Oh well... Knowing's half the battle...

Thanks!