Excel VBA ADODB works in access sql but error in excel vba

Ashton2290

New Member
Joined
Jun 29, 2017
Messages
9
Hello Everyone,

First time poster, the following query works just fine in access:

However, when I try to connect it to VBA it doesn't work: I get a runtime error '2147217887 (80040e21)': Automation Error

Code and query below

Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY],
[Sales TY]/[Sales LY]-1 AS [% Change $ Sales],
avg(f.[Ending Retail TY]) AS [Dollar Inv TY],
avg(f.[Ending Retail LY]) AS [Dollar Inv LY],
[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv],
sum(d.TW_SLS_TY) AS [FP UNIT SLS TY],
sum(e.TW_SLS_LY) AS [FP UNIT SLS LY],
[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls],
avg(e.TW_INV_TY) AS [FP UNIT INV TY],
avg(e.TW_INV_LY) AS [FP UNIT INV LY],
[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv],
[FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY],
[FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY],
Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY],
Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY],
[MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls],
Avg(d.TW_INV_TY) AS [MD UNIT INV TY],
Avg(d.TW_INV_LY) AS [MD UNIT INV LY],
[MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv]
from (((([Store Comp Status Master] a
inner join [Dlr F19] b on a.str = b.str)
inner join [Department Name Master] c on b.dpt = c.dpt_no)
left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str)
left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str)
left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str
where a.str = 701 and b.week >= 7 and b.week <= 9
group by a.str, a.name, b.dpt, c.desc



Sub SPM()

Dim SPMConn As ADODB.Connection
Dim SPMData As ADODB.Recordset
Dim SPMField As ADODB.Field

Set SPMConn = New ADODB.Connection
Set SPMData = New ADODB.Recordset

If Sheet1.Range("A1").Value <> "" Then
Clear
End If

SPMConn = Winners

SPMConn.Open

With SPMData

.ActiveConnection = SPMConn
.Source = reportSource
.Open

End With


Range("A1").Select
For Each SPMField In SPMData.Fields
ActiveCell.Value = SPMField.Name
ActiveCell.Offset(0, 1).Select
Next SPMField


Range("A2").CopyFromRecordset SPMData

SPMData.Close
SPMConn.Close

End Sub

Function:

Public Function DeptQuery() As String

Dim firstWeek As String
Dim lastWeek As String
Dim store As String
Dim query As String

Sheet1.Select

firstWeek = Range("BG1").Value
lastWeek = Range("BG2").Value
store = Range("BG3").Value

query = "Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY], " & _
"[Sales TY]/[Sales LY]-1 AS [% Change $ Sales], avg(f.[Ending Retail TY]) AS [Dollar Inv TY], avg(f.[Ending Retail LY]) AS [Dollar Inv LY], " & _
"[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv], sum(d.TW_SLS_TY) AS [FP UNIT SLS TY], sum(e.TW_SLS_LY) AS [FP UNIT SLS LY], " & _
"[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls], avg(e.TW_INV_TY) AS [FP UNIT INV TY], avg(e.TW_INV_LY) AS [FP UNIT INV LY], " & _
"[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv], [FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY], [FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY], " & _
"Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY], Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY], [MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls], Avg(d.TW_INV_TY) AS [MD UNIT INV TY], " & _
"Avg(d.TW_INV_LY) AS [MD UNIT INV LY], [MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv] " & _
"from (((([Store Comp Status Master] a inner join [Dlr F19] b on a.str = b.str) " & _
"inner join [Department Name Master] c on b.dpt = c.dpt_no) left join [MD Summ F19] d on b.week = d.week and b.dpt = d.dpt and b.str = d.str) " & _
"left join [Dpt Summ F19] e on b.week = e.wk_no and b.dpt = e.dpt_no and b.str = e.str) left join [Dlr Inv F19] f on b.week = f.week and b.dpt = f.dpt and b.str = f.str " & _
"where a.Str = " & store & " And b.week >= " & firstWeek & " And b.week <= " & lastWeek & _
" group by a.str, a.name, b.dpt, c.desc"

DeptQuery = query
End Function
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top