RunTime Error 1004 Invalid Object Name when connection to SQL Server Database

[osianllwyd]

Hi,

I've searched the forum and not found any posts reporting this problem.

When running a QueryTable command in VBA I keep getting the error: RunTime Error 1004: Invalid object name: 'MyDatabase.Data.Amount_Source'

What I cannot understand is that when I copy the SQL Query Command Text and run it within SQL Server, there are no errors and it runs without any issues.

I therefore don't think there are any issues with the SQL Query because I've been able to run it on several other tables and I know I am able to connect to the database too as I've been able to do it for other tables (For example, I am able to run the contents of the first set of brackets by itself with no issues).

Can anyone understand where I'm going wrong, especially considering the SQL code works fine when executed within SQL Server?

Here is the SQL code:

WITH T1Amount AS
(SELECT Member as Member,
Amount as T1Amount
FROM MyDatabase.Data.Amount_Source
INNER JOIN MyDatabase.Data.Member
ON Amount_Source.MemberID=Member.MemberID
WHERE Amount_Source.RecordStartDate<DATEADD(Day,1,'2012-06-01')
AND (Amount_Source.RecordEndDate>DATEADD(Day,1,'2012-06-01')
OR Amount_Source.RecordEndDate IS NULL)
AND MODELLINGYEARID=2012), 
T2Amount AS 
(SELECT Member as Member,
Amount as T2Amount
FROM MyDatabase.Data.Amount_Source
INNER JOIN MyDatabase.Data.Member
ON Amount_Source.MemberID=Member.MemberID
WHERE Amount_Source.RecordStartDate<DATEADD(Day,1,'2012-06-27')
AND (Amount_Source.RecordEndDate>DATEADD(Day,1,'2012-06-27')
OR Amount_Source.RecordEndDate IS NULL)
AND MODELLINGYEARID=2012) 
SELECT ISNULL(T1Amount.Member,T2Amount.Member) AS Member,
T1Amount,
T2Amount
FROM T1Amount 
FULL OUTER JOIN T2Amount 
ON T1Amount.Member= T2Amount.Member 
ORDER BY Member
<dateadd(day,1,'2012-06-01')
<dateadd(day,1,'2012-06-27')

</dateadd(day,1,'2012-06-27')
</dateadd(day,1,'2012-06-01')

 

[osianllwyd]

<dateadd(day,1,'2012-06-01')
<dateadd(day,1,'2012-06-27')
</dateadd(day,1,'2012-06-27')
</dateadd(day,1,'2012-06-01')

 

[osianllwyd]

For some reason this forum will not allow me to paste my code in correctly - it automatically deletes lines of the code!