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

osianllwyd

New Member
Joined
Apr 20, 2012
Messages
36
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:

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')
 
Last edited:

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

osianllwyd

New Member
Joined
Apr 20, 2012
Messages
36
<dateadd(day,1,'2012-06-01')
<dateadd(day,1,'2012-06-27')
</dateadd(day,1,'2012-06-27')
</dateadd(day,1,'2012-06-01')
 
Last edited:

osianllwyd

New Member
Joined
Apr 20, 2012
Messages
36
For some reason this forum will not allow me to paste my code in correctly - it automatically deletes lines of the code!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,368
Messages
5,601,220
Members
414,434
Latest member
Riyen

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
Top