Access Query works in Access but doesn't work in Excel VBA via ADODB

[Ashton2290]

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

 

[xenou]

Hi,
Always start with a much simpler query to make sure your connection works and you can run a simple query. Such as "select ID from Table1". That way you can reduce the number of variables (for instance, we don't know right now if the problem is the query or the connection and we would like to test the connection without a complicated query).

 

[Ashton2290]

Thanks for your reply xenou! I have tested it with the following query and it worked:

select * from [Department Name Master]

 

[james_lankford]

try taking out everything that is not an aggregate function and see what happens

Select a.str, a.name, b.dpt, c.desc, sum(b.[Sales TY]) AS [Sales TY], sum (b.[Sales LY]) AS [Sales LY],  <- keep this 
[Sales TY]/[Sales LY]-1 AS [% Change $ Sales],  <- remove
avg(f.[Ending Retail TY]) AS [Dollar Inv TY],  <- keep this 
avg(f.[Ending Retail LY]) AS [Dollar Inv LY],  <- keep this 
[Dollar Inv TY]/[Dollar Inv LY]-1 AS [% Change Dollar Inv], <- remove
sum(d.TW_SLS_TY) AS [FP UNIT SLS TY],  <- keep this 
sum(e.TW_SLS_LY) AS [FP UNIT SLS LY],  <- keep this 
[FP UNIT SLS TY]/[FP UNIT SLS LY]-1 AS [% Change FP Unit Sls],  <- remove
avg(e.TW_INV_TY) AS [FP UNIT INV TY],  <- keep this 
avg(e.TW_INV_LY) AS [FP UNIT INV LY],  <- keep this 
[FP UNIT INV TY]/[FP UNIT INV LY]-1 AS [% Change FP Unit Inv], <- remove
[FP UNIT INV TY]/[FP UNIT SLS TY]*(9-7+1) AS [SSR TY], <- remove
[FP UNIT INV LY]/[FP UNIT SLS LY]*(9-7+1) AS [SSR LY], <- remove
Sum(e.DISTRO) AS DISTRO, Sum(d.TW_SLS_TY) AS [MD UNIT SLS TY],  <- keep this 
Sum(d.TW_SLS_LY) AS [MD UNIT SLS LY],  <- keep this 
[MD UNIT SLS TY]/[MD UNIT SLS LY]-1 AS [% Change MD Unit Sls],   <- remove
Avg(d.TW_INV_TY) AS [MD UNIT INV TY],  <- keep this 
Avg(d.TW_INV_LY) AS [MD UNIT INV LY],  <- keep this 
[MD UNIT INV TY]/[MD UNIT INV LY]-1 AS [% Change MD Unit Inv]   <- remove
....
....

 

[xenou]

Also what is FirstWeek, LastWeek? are those dates or numbers or text?
What is Store? Is that a number or is it text?

 

[Ashton2290]

Hi James! I took out everything that isn't aggregated and the query ran but it only exported the columns, not the recordset itself.

Hi Xenou, the firstWeek, lastWeek and store are integer variables that are entered in by the user:


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

Sheet1.Select

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

 

[Ashton2290]

Hi James, I meant the column names

 

[xenou]

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],

I don't usually do this kind of thing in SQL. Are you sure it works in Access?
Normally I'd sum everything in one query (possibly a subquery), before I use the summed values in another expression. Note that I suppose in principle it should be okay but I just don't have experience with it. I'd still like to be sure it works in Access. How do you know it works in Access?

Hi Xenou, the firstWeek, lastWeek and store are integer variables that are entered in by the user:

Also, are these integer data types in the database too?

 

[Ashton2290]

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],

I don't usually do this kind of thing in SQL. Are you sure it works in Access?
Normally I'd sum everything in one query (possibly a subquery), before I use the summed values in another expression. Note that I suppose in principle it should be okay but I just don't have experience with it. I'd still like to be sure it works in Access. How do you know it works in Access?

I tested it in the SQL window in access first and it runs successfully but when I converted it into VBA I ran into the error.


Also, are these integer data types in the database too?

In Access the data type is Number.

I have also tested this query:

select a.str, a.name, b.dpt, c.desc
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

But the same result: The column names pull but the recordset doesn't. I think it has to do with the datatypes, integer does not convert to number I assume?

 

[james_lankford]

try using the query you posted as a starting point

select a.str, a.name, b.dpt, c.desc
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

first strip it down to table a, b and c

select 
  a.str, 
  a.name, 
  b.dpt, 
  c.desc
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
where 
  a.str = 701

if that works then add
and b.week >= 7 and b.week <= 9

if that works then add table d
if that works then add table e
if that works then add table f