Querieng MS Access Query from Cold Fusion

[Leon1977]

Is there a way I could call a saved MS ACCESS query from Cold Fusion.
I want to send a parameter to the query and then receive the answer(s)...
If yes which version of Cold Fusion it applays on.

 

[r937]

any version of cf

a saved query in access can be queried just like a table

rudy

 

[Leon1977]

Thanx
I haven't even thought it could be that easy

 

[Leon1977]

..... NO no no ....
How can I use it if I use a parameter in the query

 

[r937]

what is the query? can you show us the sql?

 

[Leon1977]

PARAMETERS p1 Short;
SELECT ARTICLES.ARTICLE_NAME
FROM ARTICLES
WHERE (((ARTICLES.ARTICLE_NUMBER)=[p1]));

The above has been generated by MS ACCESS

Select *
from Articles
Where ARTICLE_NUMBER=[p1]

 

[r937]

i would not attempt to use this saved query, i would just construct a query against the base table in coldfusion, and insert your article number parameter into the coldfusion query

for example,

<CFPARAM NAME=&quot;url.article&quot; DEFAULT=&quot;21&quot;>
<CFQUERY NAME=&quot;articlequery&quot; DATASOURCE=&quot;#articles#&quot;>
select article_name
, Format(article_date, 'mmmm d, yyyy')
as published_date
, article_author
from articles
where article_number = #url.article#
</CFQUERY>
<CFIF articlequery.RecordCount>
<CFOUTPUT query=&quot;articlequery&quot;>
<p>#articlequery.article_name#<br />
#articlequery.published_date#<br />
#articlequery.article_author#</p>
</CFOUTPUT>
<CFELSE>
<p>Article #url.article# does not exist.</p>
</CFIF>

rudy

 

[Leon1977]

I think that it would be faster if it is saved as a query in the MS ACCESS database

I just got it .. it could be done with a custom tag.. thanx for your help

 

[r937]

saved query after all?

custom tag for something that simple?


okay, good luck

 

[SidianConsulting]

You might find this article interesting:

http://www.sys-con.com/coldfusion/article.cfm?id=51

It's about using stored procedures in Access.

-Tek

 

[r937]

actually, tek, it's about &quot;something similar to stored procedures ... what Access calls queries or parameter queries.&quot;

the author further goes on to say...

&quot;While this trick in using Access is nifty, be aware that it doesn't really open up the full range of complex programmatic SQL typically available in full-fledged DBMSs like Oracle or SQL Server. You can't specify any sort of conditional logic in the query, nor does Access support use of temporary tables. You can only perform &quot;simple&quot; SQL, which you could have done in a CFQUERY.&quot;

just trying to keep the record straight

p.s. that site is ugly, 80% promo, 20% content

 

[SidianConsulting]

Yes, I remember reading the article in CFDJ (when I subscribed to it and it wasn't an expensive POS like it mostly is now). I figured I wouldn't explain the article and just let one read it for themselves, but then again, I am an RTFM kinda guy so I always assume everyone else is!

I did think the article was interesting because at that time (1999) I wasn't working much with SQL Server yet as I'd just started using CF 4.0.

I would guess that if one has to use Access then the methods the article mentions are definitely something to look into. I have no idea about performance issues, however.

-Tek

 

[Leon1977]

Wow it works
<cfquery ...>
{call <name>(param1,param2)}
</cfquery>

but it's not as fast as I was expected

 

[SidianConsulting]

Access will never be as fast as a database server, since Access is itself a desktop database not suited for heavy concurrent use.

-Tek