Query works in access, fails in new SQL Server db

I have the following query running in Cold Fusion and ODBC:
SELECT distinct
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me],
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
as FeederName,
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthcare
_Catalog_2].[Program_Web]
as FeederWeb FROM
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_Na
me]
= 'Dental Assistant (Cert)' and
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_Name]
<> '' order by
& #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_Pro
gram_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_Program
s_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
& #91;Program_List_with_Feed_and_Exit_Prog
rams_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
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name],
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> as FeederName,
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_Catalog_2].[Program_Web]
> as FeederWeb FROM
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links] where
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Program_
Name]
> = 'Dental Assistant (Cert)' and
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_Name]
> <> '' order by
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Feeder_P
rogram_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 pref
ix
> 'Program_List_with_Feed_and_Exit_Program
s_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
> & #91;Program_List_with_Feed_and_Exit_Prog
rams_and_Web_Links].[Healthca
re_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 m
ore 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.