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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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