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