Can not find query in MS ACCESS

Report · May 28, 2009

I have a strange problem.

In a MS ACCESS database I have a table with the name POST

In my .cfm I have

<CFIF IsDefined("reactie")>

<CFQUERY NAME="Adressen" DATASOURCE=diversen">

SELECT * FROM post_invoer

WHERE publi LIKE '%#reactie#%'

ORDER BY discussieID desc

</CFQUERY>

and then the output.

It works fine.

But now I made in the MS ACCESS database a query that is exactly the same as the table, but ofcourse with another name.

<CFIF IsDefined("reactie")>

<CFQUERY NAME="Adressen" DATASOURCE=diversen">

SELECT * FROM postvip

WHERE publi LIKE '%#reactie#%'

ORDER BY discussieID desc

</CFQUERY>

I get this error

------------------------------------------------------------------------------------------

Error Executing Database Query.

Failed to find table postvip in database diversen

The error occurred in \\NAWINFS03\home\..................................: line 45

43 : <CFQUERY NAME="Adressen" DATASOURCE="Dordtnl_diversen">

44 : SELECT * FROM postvip

45 : WHERE publi LIKE '#reactie#%'

46 : ORDER BY discussieID desc

47 : </CFQUERY>

--------------------------------------------------------------------------------

SQL SELECT * FROM postvip WHERE publi LIKE '%' ORDER BY discussieID desc

DATASOURCE Dordtnl_diversen

VENDORERRORCODE 212992

SQLSTATE 34000

The error says that there is not a TABLE postvip. I know, there is a QUERY with that name. Why can CF not find the query?

Who can help me?

Greetings from Holland.

JesP

Report · May 28, 2009

You need to specify dbtype="query" in order to use Query of Queries

(which appears you're attempting to do if you want to select from a

previously made query postvip):

<CFQUERY NAME="Adressen" dbtype="query">

Mack

Report · May 28, 2009

Sorry, but it doesn't work. I still get the error

Error Executing Database Query.
Failed to find table postvip in database diversen

The error occurred in \\NAWINFS03\home\users\ ........... .........: line 45

43 : <CFQUERY NAME="Adressen" DATASOURCE="Dordtnl_diversen" dbtype="query">
44 : SELECT * FROM postvip
45 : WHERE publi LIKE '#reactie#'
46 : ORDER BY discussieID desc
47 : </CFQUERY>

--------------------------------------------------------------------------------

SQL    SELECT * FROM postvip WHERE publi LIKE '%' ORDER BY discussieID desc
DATASOURCE   Dordtnl_diversen
VENDORERRORCODE   212992
SQLSTATE   34000

Report · May 28, 2009

I'm confused. You're trying to select from a table or from a query ?

Mack

Report · May 28, 2009

I did it from a table. Everything worked fine. But I want to select from an Access database query.

My old lines (so from the table):

<CFIF IsDefined("reactie")>
<CFQUERY NAME="Adressen" DATASOURCE="diversen">
SELECT * FROM postkantoor_invoer
WHERE publi LIKE '%#reactie#%'
ORDER BY discussieID desc
</CFQUERY>

ettc etc etc

As I said, it works fIne from the table in the database.

Now I made in the database a query with the name postvip (a copy of the table, with exact the same fields, not selecting any records or doing other things in the query)

When I do

<CFIF IsDefined("reactie")>
<CFQUERY NAME="Adressen" DATASOURCE="diversen">
SELECT * FROM postvip
WHERE publi LIKE '%#reactie#%'
ORDER BY discussieID desc
</CFQUERY>

This is the error

Error Executing Database Query.

Failed to find table postvip in database diversen

The error occurred in \\NAWINFS03\home\..................................: line 45

43 : <CFQUERY NAME="Adressen" DATASOURCE="diversen">

44 : SELECT * FROM postvip

45 : WHERE publi LIKE '#reactie#%'

46 : ORDER BY discussieID desc

47 : </CFQUERY>

---------------------------------------------

SQL SELECT * FROM postvip WHERE publi LIKE '%' ORDER BY discussieID desc

DATASOURCE Dordtnl_diversen

VENDORERRORCODE 212992

SQLSTATE 34000

And when I do

<CFIF IsDefined("reactie")>
<CFQUERY NAME="Adressen" DATASOURCE="diversen" dbtype="query">
>
SELECT * FROM postvip
WHERE publi LIKE '%#reactie#%'
ORDER BY discussieID desc
</CFQUERY>

the error is almost the same

Error Executing Database Query.

Failed to find table postvip in database diversen

The error occurred in \\NAWINFS03\home\..................................: line 45

43 : <CFQUERY NAME="Adressen" DATASOURCE="diversen" dbtype="query">

44 : SELECT * FROM postvip

45 : WHERE publi LIKE '#reactie#%'

46 : ORDER BY discussieID desc

47 : </CFQUERY>

---------------------------------------------

SQL SELECT * FROM postvip WHERE publi LIKE '%' ORDER BY discussieID desc

DATASOURCE Dordtnl_diversen

VENDORERRORCODE 212992

SQLSTATE 34000

Thanks for helping me!

Report · May 28, 2009

Regarding "But I want to select from an Access database query."

I am pretty sure that this is not possible.

Report · May 29, 2009

I made a simple test with a query in MS Access and accessing the query

works for me. Check that the datasource is pointing to the correct mdb

file (I usually test this by removing the mdb file and expecting an

error when I run the page). Also, check that the query named 'postvip"

exist in the mdb file.

Mack