Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
as FeederName,
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
as FeederWeb FROM
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
= 'Dental Assistant (Cert)' and
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
<> '' order by
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
which works using my Access DB, but when used against the SQL Server DB
returns the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
does not match with a table name or alias name used in the query.
I guess my question is how this ever worked in the first places, since
there is no
[Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
table... I can only see that table when I look at the access query in
design view, so it is the result of some kind of join.
How do I proceed with fixing this?Chris Lott wrote:
> I have the following query running in Cold Fusion and ODBC:
> SELECT distinct
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name],
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> as FeederName,
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2].[Program_Web]
> as FeederWeb FROM
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links] where
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Program_Name]
> = 'Dental Assistant (Cert)' and
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> <> '' order by
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Feeder_Program_Name]
> which works using my Access DB, but when used against the SQL Server DB
> returns the error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix
> 'Program_List_with_Feed_and_Exit_Programs_and_Web_Links.Healthcare_Catalog_2'
> does not match with a table name or alias name used in the query.
> I guess my question is how this ever worked in the first places, since
> there is no
> [Program_List_with_Feed_and_Exit_Programs_and_Web_Links].[Healthcare_Catalog_2]
> table... I can only see that table when I look at the access query in
> design view, so it is the result of some kind of join.
> How do I proceed with fixing this?
>
Your Access db must have a query with that big long name that joins two or more tables. Create a view in SQL joining
the same tables. If the view already exists, you may not have permission to access it. Or the view may be in another
schema and you will have to preface that view name with that.sql