Run-time error 3709: The connection cannot be used to perform this operation.

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
769
"...it is either closed or invalid in this context."

I'm trying to run a make-table query through an ADO recordset. Maybe that's the wrong way to do it... I guess that creating a recordset and making a table are two different things. I was trying to do one, then the other. But I'm getting the error mentioned above. Not sure why.

Code:
Sub CreateBlahTable()

Dim strsQL As String

Dim cnConnection As ADODB.Connection
Set cnConnection = New ADODB.Connection

Dim strConnection As String
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & "C:\Documents and Settings\My Name Here\My Documents" & "\Database12.accdb;"
cnConnection.Open strConnection

Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command

Dim rsRecordset As ADODB.Recordset
Set rsRecordset = New ADODB.Recordset


strsQL = "SELECT Sum(Analyzer.[Revenue Under Goal (Lifetime)]) AS [SumOfRevenue Under Goal (Lifetime)], Analyzer.[End Date], Analyzer.[Order Line], Analyzer.[Order ID], Analyzer.[Confidence Pct], Analyzer.Priority INTO [BLAH SITE REVENUE AT RISK JULY 29 2012]"
strsQL = strsQL + "FROM Analyzer"
strsQL = strsQL + "GROUP BY Analyzer.[Internet Site], Analyzer.[End Date], Analyzer.[Order Line], Analyzer.[Order ID], Analyzer.[Confidence Pct], Analyzer.Priority"
strsQL = strsQL + "HAVING (((Analyzer.[Internet Site])='BLAH SITE') AND ((Sum(Analyzer.[Revenue Under Goal (Lifetime)]))>1000) AND"
strsQL = strsQL + "((Analyzer.[End Date])>(Date()+7)) AND ((Analyzer.[Confidence Pct])='IO'))"
strsQL = strsQL + "ORDER BY Sum(Analyzer.[Revenue Under Goal (Lifetime)]) DESC;"

rsRecordset.Open strsQL

End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Thanks.

Upon further reflection, I really don't need a recordset for this at all; I can just use DoCmd.RunSQL. I was working from a book that has exercises focused on forms, but I'm not using forms.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top