Access query [Urgent Help Please]

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...

Code:

---

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...

Quote:

---

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

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

Quote:

---

Originally Posted by satkin2

Code:

---

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:

Code:

---

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 :)

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.