Issues Running an Access Query from Excel VBA

[toveyj]

Hi

I'm not very experienced with linking Excel to Access and am having some issues trying to get the correct data. Can any of you clever people spot what I am doing wrong please?

I have a cross-tab query in Access which uses a parameter for one field and the SQL is as follows:

PARAMETERS [First Day of 12 month period dd/mm/yyyy] DateTime;
TRANSFORM Count(DART_New_UBRNs_Master.UBRN) AS CountOfUBRN
SELECT DART_New_UBRNs_Master.REFERRING_ORG_ID, Count(DART_New_UBRNs_Master.UBRN) AS [All Specialties]
FROM DART_New_UBRNs_Master
WHERE (((DART_New_UBRNs_Master.REFERRING_ORG_ID) Not Like "V*") AND ((DART_New_UBRNs_Master.[Referral date])>=[First Day of 12 month period dd/mm/yyyy]) AND ((DART_New_UBRNs_Master.Report)="initial"))
GROUP BY DART_New_UBRNs_Master.REFERRING_ORG_ID
PIVOT IIf([DART_New_UBRNs_Master]![Specialty]="Orthopaedics",[DART_New_UBRNs_Master]![Specialty] & " ( " & [DART_New_UBRNs_Master]![Clinic Type] & " )",[DART_New_UBRNs_Master]![Specialty]);

What I want to do is run this query from EXCEL and paste the results onto an existing sheet. To do this I am using the VBA code below (which I found on another helpful forum!).

Sub GetAccessParameterData()
' Sample demonstrating how to return a recordset from a parameterised query
'#############################################################
' Requires reference to Microsoft ActiveX Data Objects library
'#############################################################
Dim cnn As ADODB.Connection
Dim strQuery As String
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset
Dim prm As ADODB.Parameter, prms As ADODB.Parameters
Dim strPathToDB As String
Dim wks As Worksheet
Dim i As Long

Set wks = Sheets("Ref Pivot")

wks.Range("A4:BA121").ClearContents

' change database path and query name as required
strPathToDB = "Z:\Support Team\Reporting\DART Monthly Practice Report\DART Practice Reporting Database.accdb"
strQuery = "[zzTest_Output]"

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
End With
Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = cnn
.CommandText = strQuery
.CommandType = adCmdTable
.Parameters.Refresh

' Change parameter names as necessary
.Parameters("[First Day of 12 month period dd/mm/yyyy]").Value = "01/11/2010"
End With

rst.Open cmd
With rst
If Not (.EOF And .BOF) Then
'Populate field names
For i = 1 To .Fields.Count
wks.Cells(4, i) = .Fields(i - 1).Name
Next i
' Copy data
wks.Range("A5").CopyFromRecordset rst
End If
End With
rst.Close
Set rst = Nothing
Set cmd = Nothing
cnn.Close
Set cnn = Nothing
End Sub

Although this works, what it returns to the sheet doesn't match what I see if I run the query in Access. It is ALMOST right but the WHERE clause in the SQL isn't working:

WHERE (((DART_New_UBRNs_Master.REFERRING_ORG_ID) Not Like "V*")

My results that are written back to EXCEL include results for those records where the REFERRING_ORG_ID begins with "V", whereas they don't appear if I run the query within ACCESS. All the results for the other REFERRING_ORG_IDs are correct but it is including those beginning "V" which I need to exclude.

Any ideas gratefully accepted!

 

[toveyj]

PS: I have checked and the results for each REFERRING_ORG_ID have correctly incorporated the other 2 parameters in the WHERE clause. It is just the "Not Like V*" element that isn't being applied but works when running the query within ACCESS.

 

[RonB1111]

Since your access query uses only 1 table, I'd suggest a different & simpler approach. You can create a pivot table in excel which selects "External Data" (ie: the DART_New_UBRNs_Master table from your access database). You can then use the pivot table in excel to select/summarize your data. You won't need the access query at all and you can make changes to the excel pivot table on the fly.

In Excel 2007/2010 click the PivotTable icon under the Insert tab and in the Create PivotTable window that pops up select "Use an External Data Source" and click the "Choose Connection" icon and follow the wizard to get to your Access Data Base.

 

[toveyj]

Hi Ron

Thanks for the suggestion.
I am trying to avoid using a pivot table.
My file is already 9MB and I have to send it out in Excel 2003 format as some users
still don't have 2007 in my user community.

Importing all the data into a pivot table has an impact on file size and I need to do this
in numerous different places against a database with 200,000 records so the file size
will get enormous. Hence I am trying to avoid pivot tables.