Running MSAccess Query thru ADO

[LISpeedyG]

Hi All,

I posted this in the wrong thread earlier (ADP) sorry.. I hope this is the correct thread..

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

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

 

[JerryKlmns]

Hi LISpeedyG,

I cann t see what 's the use of "cmdCommand" and also that you don 't "Set cmdCommand=Nothing".

But, have you concidered to create that sql string, a Query inside the DB?? Setting the rstRecordset.Source = yourQuery and opening it will do your work!

 

[LISpeedyG]

Hi JerryKlmns,

Thank you for your post.
Please know that I am relatively new at ADO implementation. I simplified my code to make it easier to understand. However, I do not seem able to get your suggestion to work.

As you suggested, I set up an MSAccess query that performs the Xtable and now I want to run the query and open the resultant table. However, When I run the VB program it errors when trying to interpret the command rstRecordset.Source = My query.. When run, indicates that the system is expecting an SQL statement not a query name form the dB.

The following is a stripped down version of the code. I hope you folks can help. I have been struggling with this for days.

Thanks in Advance..

-------------CODE-------------------
Sub TstADO()
Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & ctPth & ctFil 'Variabled passed from Globals
adoConn.ConnectionString = strConn
adoConn.Open

adoRS.Source = ctDbXtb '<----Access Stored Query Name
adoRS.CursorType = adOpenForwardOnly
adoRS.ActiveConnection = adoConn
adoRS.Open

Do While Not adoRS.EOF
Debug.Print adoRS.Fields("VisNo_Name").Value
adoRS.MoveNext
Loop

adoRS.Close
Set adoRS = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

 

[JerryKlmns]

Change adoRS.CursorType = adOpenForwardOnly
to adoRS.CursorType = adOpenStatic

I would also add

adoRS.CursorLocation = adUseClient
adoRS.LockType = adLockReadOnly

This should get things going!