Access query [Urgent Help Please]

[satkin2]

Hi, this both a VB and an SQL question.

I have an access database that I have created querys, which run perfectly well in access.

I have also tried using this query in vb to open a data report, but it doesn't work.

I am using the following code to open the report...

Call connectDB
rs.Open "SELECT Sum(ServiceClaim.Live) AS SumOfLive, Sum(ServiceClaim.Dead) AS SumOfDead, SumOfLive+SumOfDead as Total From ServiceClaim"

Set rptSection1a.Datasource = rs
rptSection1a.show

When I run this code I get the following message...

Run-time error '3709':

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

The method of setting the record set and calling the report does work as I've used it on a different query.

I don't understand why/how the code is falling down in vb but not in access, when it is using the same sql code.

Any advice on either my codeing or a better way would be greatly appreciated.

Thanks

Satkin2

[jp140768]

I think, that you are supposed to supply the connection, as part of the RS.Open, try putting a comma after the string and check out the other parameters.

HTH

JP

[Buddy008]

Call connectDB
rs.Open "SELECT Sum(ServiceClaim.Live) AS SumOfLive, Sum(ServiceClaim.Dead) AS SumOfDead, SumOfLive+SumOfDead as Total From ServiceClaim"

Set rptSection1a.Datasource = rs
rptSection1a.show

Satkin2

A little more information would be more helpful, but if your using ADODB, you can use something like this:

Dim conn as New ADODB.Connection
Dim rs as New ADODB.RecordSet
Dim mySQL as string

Set conn = New ADODB.Connection

conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\myDBFOLDER\myDB.mdb;"
    
mySQL = "SELECT Sum(ServiceClaim.Live) AS SumOfLive, Sum(ServiceClaim.Dead) AS SumOfDead, SumOfLive+SumOfDead as Total From ServiceClaim"
    
Set rs = New ADODB.Recordset
rs.Open mySQL, conn, adOpenDynamic, adLockOptimistic

You need both the dim's and the set's, for it to work correctly... I usually put the dim's in my general declarations... just personal preference though!

If your not using ADODB, could you please post your code for connectDB, I would like to see how your trying to connect.

Hope this helps

[satkin2]

Hi,
Thanks for your messages. I appears that I had missed some of the end comments out as your said.

It now appears to be working.
Thankyou both.