Visual Basic&database, Q: MS SQL queries -> ANSI SQL

James B Tolla
#1 / 5

Q: MS SQL queries -> ANSI SQL

Quote:

>I believe you can make both queries on Access. Or don't? What's the advantage of using INNER JOIN instead of WHERE?

Robert,

The basic difference between the two syntax types makes no
difference to MS Access/VB. However, when creating back end
databases on SQL servers, the native SQL must be used (if
creating views / stored procedures on the box). This excludes
(in the case of Sybase) the use of the MS Access syntax.

Transact SQL refers to joins with the *= (left join) =* (right
join) = (inner join) type operators. Unfortunately, it is not
as simple as substituting a *= for a left join (create a simple
four table query with left/right and inner joins and you'll see
what I mean!!).

Anyway, I'll keep everyone posted if I find a way to
'Translate' the two native tongues.

All the best

Jimi

Fri, 15 May 1998 03:00:00 GMT

Roberto Nhu
#2 / 5

Q: MS SQL queries -> ANSI SQL

I believe you can make both queries on Access. Or don't? What's the
advantage of using INNER JOIN instead of WHERE?

Quote:

>Hi,
>Does anyone know of an API call (possibly to odbc.dll) that
>would allow one to send as a string, Microsoft SQL (from MS
>Access) and return the ANSI equivelant. The example below
>shows an example of what I mean:
>Microsoft SQL:
>SELECT DISTINCTROW Paragraph.Paragraph_Number,
>[Confirmation_Type].Confirmation_Type_Name,
>Paragraph.Paragraph_Text
>FROM ([Confirmation_Type] INNER JOIN
>[Confirmation_Paragraph_INT] ON
>[Confirmation_Type].Confirmation_Type_Code =
>[Confirmation_Paragraph_INT].Confirmation_Type_Code) INNER JOIN
>Paragraph ON [Confirmation_Paragraph_INT].Paragraph_Code =
>Paragraph.Paragraph_Code;
>ANSI SQL:
>SELECT DISTINCT Paragraph.Paragraph_Number,
>Confirmation_Type.Confirmation_Type_Name,
>Paragraph.Paragraph_Text
>FROM Confirmation_Paragraph_INT, Confirmation_Type, Paragraph
>WHERE
>((Confirmation_Paragraph_INT.Paragraph_Code=Paragraph.Paragraph
>_Code) AND (Confirmation_Paragraph_INT.Confirmation_Type_Code=
>Confirmation_Type.Confirmation_Type_Code));
>Basically I'd love to know if there is a function along the
>lines of (perhaps someone has written a dll to do this?):
>cSQLANSI=SQLtoANSI(MsQuery)
>Hope someone can help.
>Jimi
>--
> \|/
> (*.*)
>*---------------------------------------oOO--(=)--OOo-----*
>| James B Tollan |
>| London, United Kingdom |
>| |
>| O tongue, thou art a treasure without end. |
>| |
>| And, O tongue, thou art also |
>| a disease without remedy. |
>| == Jelal'uddin Rumi == |
>*---------------------------------------------------------*

Fri, 15 May 1998 03:00:00 GMT

Robert B. Port
#3 / 5

Q: MS SQL queries -> ANSI SQL

Quote:

>I believe you can make both queries on Access. Or don't? What's the
>advantage of using INNER JOIN instead of WHERE?

Yup,

If you use INNER JOIN the recordset is updatable, if you use the ANSI
standard where joins then the recordset is read-only. Ain't access
wonderful!

Cheers,

Bob Porter

Fri, 15 May 1998 03:00:00 GMT

James Shiel
#4 / 5

Q: MS SQL queries -> ANSI SQL

Quote:

>The basic difference between the two syntax types makes no
>difference to MS Access/VB. However, when creating back end
>databases on SQL servers, the native SQL must be used (if
>creating views / stored procedures on the box). This excludes
>(in the case of Sybase) the use of the MS Access syntax.
>Transact SQL refers to joins with the *= (left join) =* (right
>join) = (inner join) type operators. Unfortunately, it is not
>as simple as substituting a *= for a left join (create a simple
>four table query with left/right and inner joins and you'll see
>what I mean!!).
>Anyway, I'll keep everyone posted if I find a way to
>'Translate' the two native tongues.

If you fancy programming ODBC calls directly, there is an API call which
does this. SQLNativeSql is the name. The trouble is you have to connect to
the database, so there are quite a few calls invilved.

I think it would be worth your while learning Transact SQL, though. It
allows you to create much more flexible queries than Access SQL. There are
a lot of "concepts" which simply can't be expressed in a single access
query which are no problem in Transact SQL, and its generally a lot easier
to code to boot.

Hope this helps,

James Shields

Sun, 17 May 1998 03:00:00 GMT

James B Tolla
#5 / 5

Q: MS SQL queries -> ANSI SQL

[James kindly replied with:]

Quote:

>If you fancy programming ODBC calls directly, there is an API >call which does this. SQLNativeSql is the name. The trouble is >you =

have to connect to the database, so there are quite a few >calls invilved.

Quote:

>I think it would be worth your while learning Transact SQL, >though. It allows you to create much more flexible queries >than Acces=

s SQL. There are a lot of "concepts" which simply >can't be expressed in a single access query which are no >problem in Transact SQL=
, and its generally a lot easier to >code to boot.

Quote:

>Hope this helps,

>James Shields

James,

Thanks for the info. I actually came across the function
SQLNativeSql while looking for ways around this problem but
couldn't find any info on the API methods to call it up. If
you have any ideas in this area, I would be well chuffed.

Cheers

Jimi

BTW I agree the Transact SQL would be a better route to go and
I have made some progress in this area. However, at the
moment, I have a LARGE number of queries in Access that need to
be stored on the Sybase box as a combination of Views / Stored
procs. I was hoping to use the SQLNativeSql function to create
the basic Sybase equivelents and then tweek them. I'm sure
I'll crack it one way or another. If and when, I'll keep you
posted!!!

--

Sun, 17 May 1998 03:00:00 GMT