Running MSAccess Query thru ADO

[LISpeedyG]

All,

I have been trying to run a query that returns a pivot table from Access to Excel.

I have been successful in some ways (non-pivot) but have been stumped with "syntax issues" when attempting this current query.

In trying pass the SQL argument I get syntax errors. I suspect it is because of the SQL statement including another query reference residing in Access. So, I thought that I could try and run the query directly within VBA but do not know the correct syntax.

The code below was my attempt at passing the SQL statement that generates the syntax error. If someone here could help I would appreciate it. My hair is getting thinner and thinner since starting this quest...

Thanks.

------------------------------------------------
''
''
'' This procedure will extract the values from an Access Db and fill in the xTable information into Excel
Sub XTabQuery(DBFullName As String, _
TableName As String, TargetRange As Range)

Dim cnn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Recordset
Dim NumberOfRows
Dim vtSQL0 As String
Dim vtSQL1 As String
Dim vtSQL15 As String
Dim vtSQL2 As String
Dim vtSQL3 As String
Dim vtSQL4 As String

Set TargetRange = TargetRange.Cells(1, 1)

'' Open the Connection and Db
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
DBFullName & ";"

''Set Command Text
Set cmdCommand = New ADODB.Recordset
Set cmdCommand.ActiveConnection = cnn

'' Initialize the Strings
vtSQL0 = ""
vtSQL1 = ""
vtSQL15 = ""
vtSQL2 = ""
vtSQL3 = ""
vtSQL4 = ""


'' Open the recordset
Set rstRecordset = New ADODB.Recordset

vtSQL0 = "TRANSFORM Count([LSE(Act) Query].[S #]) AS [CountOfS #]"

vtSQL1 = "SELECT [LSE(Act) Query].[FirstOfSName-PName-Country], "
vtSQL1 = vtSQL1 & "[LSE(Act) Query].VisNo_Name, "

vtSQL15 = "Count([LSE(Act) Query].[S #]) AS [Total Of S #]"

vtSQL2 = "WHERE ((([LSE(Act) Query].[FirstOfSName-PName-Country]) Like '*US*'))"
vtSQL3 = "GROUP BY [LSE(Act) Query].[FirstOfSName-PName-Country], "
vtSQL3 = vtSQL3 & "[LSE(Act) Query].VisNo_Name"

vtSQL4 = "ORDER BY [LSE(Act) Query].[FirstOfSName-PName-Country], "
vtSQL4 = vtSQL4 & "[LSE(Act) Query].VisNo_Name "
vtSQL4 = vtSQL4 & "PIVOT 'ABC';"

With rstRecordset
.Open vtSQL0 & vtSQL1 & vtSQL15 & vtSQL2 & vtSQL3 & vtSQL4, cnn, , , adCmdText
TargetRange.Offset(0, 0).CopyFromRecordset rstRecordset
End With

''Close the Connection and Clean Up
rstRecordset.Close
Set rstRecordset = Nothing
cnn.Close
Set cnn = Nothing

End Sub

--------------------------------------------------------