Running MSAccess Query thru ADO
From: GusGG (GusGG_at_discussions.microsoft.com)
Date: 02/27/05
- Previous message: dimension: "Access Database, Autogenerated code"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Feb 2005 08:11:03 -0800
Hi 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 to pass the current SQL query.
The following the the MSAccess SQL syntax that works within Access:
--------------------------------------------------------------------------------------------
TRANSFORM Count([LSE (PA) Query].[SUBJECT #]) AS [CountOfSUBJECT #]
SELECT [LSE (PA) Query].[FirstOfSNmPNmCntr], [LSE (PA) Query].VisNo_Name,
Count([LSE (PA) Query].[SUBJECT #]) AS [Total Of SUBJECT #]
FROM [LSE (PA) Query]
WHERE ((([LSE (PA) Query].[FirstOfSNmPNmCntr]) Like "*US*"))
GROUP BY [LSE (PA) Query].[FirstOfSNmPNmCntr], [LSE (PA) Query].VisNo_Name
ORDER BY [LSE (PA) Query].[FirstOfSNmPNmCntr], [LSE (PA) Query].VisNo_Name
PIVOT "ABC";
---------------------------------------------------------------------------------------------
In trying pass the SQL argument (using array variables) I get syntax errors
althought I am sending the exact statement used in Access. I suspect it is
because of the SQL statement includes reference to another stored query ([LSE
(PA) Query])residing in Access.
So, I thought that I could try issue a VBA command to Access to run it's
stored query but have been unable to assemble the correct syntax.
I then tried to make a Stored Procedure in Access using the original stored
query. Again, I have been unsucessful.
I cannot believe that something this simple should be this difficult. After
several days of this, and absolutely getting nowhere, I am truly out of gas..
I have stipped down the VBA code and posted it here for someone with more
experience with some time to devote to help.
When I run the Sub 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. I tried to set the
execute method to adCmdFile and adCmdStoredProc and in both cases it caused
an error
What am I missing?? Arggggg
I would appreciate any advice. I am truly at a dead end..
Thank You.
-------------CODE-------------------
'
' Note All varaibles are passed from Globals
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 '<-- Variables Passed
adoConn.ConnectionString = strConn
adoConn.Open
adoRS.Source = ctDbXtb '<----Access Stored Query Variable Name Passed
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
- Previous message: dimension: "Access Database, Autogenerated code"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
