Visual Basic - ANSI 97 SQL (MS ACCESS)

Author

ANSI 97 SQL (MS ACCESS)

Rick

2006-01-26, 6:56 pm

I'm working on a VB 6.0 application that uses DAO 3.51 to connect to an MS
Access 97 database (I know it's quite old, please save your comments)

My problem is in Access's SQL syntax. The program is suppossed to
automatically bluid the queries (they are all SELECT statements). I find that
the INNER JOIN statements are quite different from SQL2000.

Is there any information on Internet that teaches me how to build queries
from access97? The problem is with the INNER JOINs, since I have to relate
several tables at once.

--
Rick

David J Mark

2006-01-26, 6:56 pm

"Rick" <Rick@discussions.microsoft.com> wrote in message
news:1711CCDA-8562-4E2E-A5C0-B6CBEA86A676@microsoft.com...
> I'm working on a VB 6.0 application that uses DAO 3.51 to connect to an MS
> Access 97 database (I know it's quite old, please save your comments)
>
> My problem is in Access's SQL syntax. The program is suppossed to
> automatically bluid the queries (they are all SELECT statements). I find
> that
> the INNER JOIN statements are quite different from SQL2000.

SQL joins are SQL joins. There are minor variations in syntax from one
platform to another. Here's the thing: you shouldn't be passing SQL from
your VB app anyway. Imagine a future developer trying to migrate your code
to SQL Server. They would have to track down a bunch of string constants in
your VB code before they can even think about the task at hand. Once you
figure out your JOIN's, put them in queries. Then switch to ADO. You can
use the same syntax to call queries in Access or SP's in SQL Server (and you
won't have to worry about DAO's object layer messing with your SQL syntax.)

>
> Is there any information on Internet that teaches me how to build queries
> from access97? The problem is with the INNER JOINs, since I have to relate
> several tables at once.

Okay, so what problem are you having with INNER JOIN's? How about posting
the query and/or code?

>
>
> --
> Rick

Rick

2006-01-26, 6:56 pm

The problem is wih the nested joins.
--
Rick

"David J Mark" wrote:

>
> "Rick" <Rick@discussions.microsoft.com> wrote in message
> news:1711CCDA-8562-4E2E-A5C0-B6CBEA86A676@microsoft.com...
>
> SQL joins are SQL joins. There are minor variations in syntax from one
> platform to another. Here's the thing: you shouldn't be passing SQL from
> your VB app anyway. Imagine a future developer trying to migrate your code
> to SQL Server. They would have to track down a bunch of string constants in
> your VB code before they can even think about the task at hand. Once you
> figure out your JOIN's, put them in queries. Then switch to ADO. You can
> use the same syntax to call queries in Access or SP's in SQL Server (and you
> won't have to worry about DAO's object layer messing with your SQL syntax.)
>
>
> Okay, so what problem are you having with INNER JOIN's? How about posting
> the query and/or code?
>
>
>
>

Jeff Johnson [MVP: VB]

2006-01-26, 6:56 pm

"Rick" <Rick@discussions.microsoft.com> wrote in message
news:1711CCDA-8562-4E2E-A5C0-B6CBEA86A676@microsoft.com...

> I'm working on a VB 6.0 application that uses DAO 3.51 to connect to an MS
> Access 97 database (I know it's quite old, please save your comments)
>
> My problem is in Access's SQL syntax. The program is suppossed to
> automatically bluid the queries (they are all SELECT statements). I find
> that
> the INNER JOIN statements are quite different from SQL2000.
>
> Is there any information on Internet that teaches me how to build queries
> from access97? The problem is with the INNER JOINs, since I have to relate
> several tables at once.

Access formats its joins in a funky manner (lots of parentheses and
nesting), but it's not necessary and they can generally be rewritten in a
straightforward manner. Simply put, if you're familiar with SQL, write the
statement manually and plop it into an Access query. It should work. If not,
post the SQL here and we'll try to help.

Rick

2006-01-26, 6:56 pm

No, it doesn't. Supose you have 5 tables:
Table1 relates to Table2 AND Table5 through ID2
Table2 relates to Table3 through ID3
Table2 relates to Table6 through ID6
Table2 relates to Table5 through ID2 AND ID4

Suppose you just want to see all the fields.
it's straight forward when you do it in SQL2000 way, but
if you copy and paste the code you get an error on Access 97.

--
Rick

"Jeff Johnson [MVP: VB]" wrote:

>
> Access formats its joins in a funky manner (lots of parentheses and
> nesting), but it's not necessary and they can generally be rewritten in a
> straightforward manner. Simply put, if you're familiar with SQL, write the
> statement manually and plop it into an Access query. It should work. If not,
> post the SQL here and we'll try to help.
>
>
>

Gman

2006-01-26, 9:55 pm

So can you post your code as Rick asked? And the error message generated
when you try and run it would be useful too.

Rick wrote:
> No, it doesn't. Supose you have 5 tables:
> Table1 relates to Table2 AND Table5 through ID2
> Table2 relates to Table3 through ID3
> Table2 relates to Table6 through ID6
> Table2 relates to Table5 through ID2 AND ID4
>
> Suppose you just want to see all the fields.
> it's straight forward when you do it in SQL2000 way, but
> if you copy and paste the code you get an error on Access 97.
>
>
>
>

Paul Clement

2006-01-30, 6:55 pm

On Thu, 26 Jan 2006 07:56:06 -0800, "Rick" <Rick@discussions.microsoft.com> wrote:

¤ I'm working on a VB 6.0 application that uses DAO 3.51 to connect to an MS
¤ Access 97 database (I know it's quite old, please save your comments)
¤
¤ My problem is in Access's SQL syntax. The program is suppossed to
¤ automatically bluid the queries (they are all SELECT statements). I find that
¤ the INNER JOIN statements are quite different from SQL2000.
¤
¤ Is there any information on Internet that teaches me how to build queries
¤ from access97? The problem is with the INNER JOINs, since I have to relate
¤ several tables at once.

You might want to take a look at the Jet SQL documentation:

http://msdn.microsoft.com/library/d...ml/acintsql.asp

You can also use the QBE grid in Access to build your queries and then view the resulting SQL.

Paul
~~~~
Microsoft MVP (Visual Basic)

Sponsored Links