VB5 DAO and SQL Queries prob 
Author Message
 VB5 DAO and SQL Queries prob

Hello all

SOme help would be greatly appreciated here, I've been away from VB
for a few months working with mainly PL/SQL and have hit a problem
that's got me stumped at the moment.

The following query works fine in Access :

SELECT Sum(Value.NewValue) AS b
FROM (SELECT Main.F8, Main.OUC, Main.DepLevel, Main.Description,
Value.F8, Value.OUC, Value.NewValue
From Main, [Value]
WHERE (Main.F8=[Value.F8] AND Main.OUC = [Value.OUC])
AND Main.Description = 'Pay'
AND Main.DepLevel = 1)

I need to use this query in VB5 to get the SUM value from a local
Access db so have tried to following:

Set dbs = OpenDatabase(sdbpath + sCVDB, True, True, ";PWD=tmp")

SQL = "SELECT Sum(Value.NewValue) AS b " _
& "FROM (SELECT Main.F8, Main.OUC, Main.DepLevel, Main.Description,
Value.F8, Value.OUC, Value.NewValue " _
& "From Main, [Value] " _
& "WHERE (Main.F8=[Value.F8] AND Main.OUC = [Value.OUC]) " _
& "AND Main.Description = 'Pay' " _
& "AND Main.DepLevel = 1)"

Set rst = dbs.OpenRecordset(SQL)

MsgBox (b)

I am assuming that the above is logically correct but when trying to
execute the code fragment  I get the following:

Run-time error '3131':
Syntax error in FROM clause.

Where is the error? I've tried playing around with the string but with
no use.

Any ideas anybody?

Regards,

Simon
*****************************************************************************

*****************************************************************************



Thu, 09 May 2002 03:00:00 GMT  
 VB5 DAO and SQL Queries prob
Hi,

Which Access? The statement is supported in Access 2000 (Jet 4), not in
Access 97 (Jet 3.5x). Jet 3.5 does not support a SELECT statement in the
FROM clause - at least not in any documented way. There is an
undocumented way to use it, but I don't think that you should go that
way.

I suggest you store the entire SELECT clause in a separate query in
Access and use the query name in the FROM clause.

Also, for performance, use an INNER JOIN in the query, rather then the
first two conditions in the WHERE clause.

Last, use MsgBox rst!b to display the result.

Hth,
--
Radu Lascae
___
Visit www.mvps.org/access for answers to common questions

Please reply to newsgroup messages in the group

Quote:

> Hello all

> SOme help would be greatly appreciated here, I've been away from VB
> for a few months working with mainly PL/SQL and have hit a problem
> that's got me stumped at the moment.

> The following query works fine in Access :

> SELECT Sum(Value.NewValue) AS b
> FROM (SELECT Main.F8, Main.OUC, Main.DepLevel, Main.Description,
> Value.F8, Value.OUC, Value.NewValue
> From Main, [Value]
> WHERE (Main.F8=[Value.F8] AND Main.OUC = [Value.OUC])
> AND Main.Description = 'Pay'
> AND Main.DepLevel = 1)

[snip]


Thu, 09 May 2002 03:00:00 GMT  
 VB5 DAO and SQL Queries prob


Quote:
>Hi,

>Which Access? The statement is supported in Access 2000 (Jet 4), not in
>Access 97 (Jet 3.5x). Jet 3.5 does not support a SELECT statement in the
>FROM clause - at least not in any documented way. There is an
>undocumented way to use it, but I don't think that you should go that
>way.

>I suggest you store the entire SELECT clause in a separate query in
>Access and use the query name in the FROM clause.

>Also, for performance, use an INNER JOIN in the query, rather then the
>first two conditions in the WHERE clause.

>Last, use MsgBox rst!b to display the result.

>Hth,
>--
>Radu Lascae

Thanks very much, I reworked the query to do away with the sub-query
and it worked fine.

Thanks for the tips.

Simon
*****************************************************************************

*****************************************************************************



Mon, 13 May 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VB5 DAO and SQL Queries prob

2. Prob: VB5 closes after DAO statements finish

3. DAO 3.5 prob w/ VB5

4. Prob w/updatable query between Acc97 and VB5?

5. Newbie Questions About DAO Queries to SQL Server

6. SQL queries or DAO recordset?

7. Newbie needs help with DAO SQL Query Problem

8. Running SQL Query with DBCS in VB4.0 and DAO 2.5

9. SQL Queries with Data Control and DAO Objects

10. HELP! DAO and sql queries using Double datatypes

11. Using DAO to connect to SQL - simple query

12. Help with SQL query using DAO please

 

 
Powered by phpBB® Forum Software