RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
Dan? Any thoughts on this? /Charlie http://www.carehart.org/blog/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Monday, November 27, 2006 10:39 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db Dan, the problem you've hit is not a syntax problem but rather a security issue Access, it seems. For instance, if you tried that SQL statement WITHIN Access, did it work? It did for me, yet trying to pass it in from outside (from CF), it fails. And it's not a datasource setup problem. I tried both the ODBC Socket and the Microsoft Access driver in CFMX. Both failed. Doing some digging, I've found that you've run into is something that many, many (indeed, mostly outside of CF) have hit as well. Access has implemented a security sandbox for certain functions to prevent their use from outside of Access itself. There are several references that describe it. Do a search inside Access' help for sandbox to see many. Still, I tried all the things that were offered there and in other web sites to configure things to work, and none would. I tried setting the security to Low as described in the help, but no dice. I even tried a Registry fix discussed here: http://support.microsoft.com/default.aspx?scid=kb;en-us;294698Product=acc To no avail. I even tried converting an Access 2000 DB to Access 2003 and it still would not work. And I'll add that I stopped and restarted both Access and CF (and the CF datasource services) after the changes, and even reloaded a test page in a new browser window (to avoid seeing a cached result). And with none could I get it to work. I found some references suggestion that you create a wrapper function or module to get things working, but I'm not as familiar with creating those in Access let alone calling them from CF, so I'll leave this info with you for your consideration. Teddy's idea may be the better solution, if indeed there's no reason for the data to exist in its current state. If it must, I'll point out one last out of the box solution: though it's preferable to let the database do database work, in this case where you're stumped, you could pull the data into a CF query (as is) and use CF functions to manipulate it, then use a query of queries to do the sort that you prefer on the manipulated result. May not perform well, but sometimes you need function over form. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Sunday, November 26, 2006 8:01 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db Sorry, the double quotes in the query statements are simply a typo on my part. I didn't copy and paste the actual query, I simply retyped it as I wrote the email and out of bad habit used quotes. The quotes ' or is not the problem. This query and/or using the REPLACE function works perfectly well on all my SQL datasources. This particular site (still) has an Access db. And therein lies the rub. Yes, I could, and probably should, upsize the Access mdb to SQL-2000, but for right now that's more trouble than simply making the query behave. So, any ideas on how to use the REPLACE function in a query on an Access db? Or, baring that, how would you create a similar end result of ORDER BY the mTitle column ignoring the if it is the first word in a movie title? Thanks, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Chambers Sent: Sunday, November 26, 2006 4:45 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db did you try single quotes? [EMAIL PROTECTED] wrote: I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see
RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
Yes Charlie, and thank you for refreshing this thread. When the problem first occurred for me I did think it was a syntax problem (of mine) and so that is where I looked for an answer. Ultimately I also found an explanation similar to yours within a Microsoft blog from an Access Dev. Engineer. He explained it somewhat like your explanation below that this (the REPLACE) function is internal to Access only but didn't go into the Security issue that you did. He basically just said It cannot be used outside of Access. I did not bother to test if from within Access at the time since I didn't need a solution there, I needed it on the outside, within the CF interface. But ironically that's exactly where I did end up using the very same REPLACE function. I took the advice of Rob Munn with the San Diego CFUG (SDCFUG)who suggested I simply add an Ordering column to the db. Overall this was so obvious and too simple. So what I ended up doing was this: 1. from within Access I did a search on the mTitle column for all records with a leading The. There were only a couple dozen. 2. I created a new column oTitle and from WITHIN Access I used the REPLACE function and copied all the records to the oTitle column stripping out the instances of The. 3. I then modified the data entry form to include the oTitle field and using a little JS magic I copy the mTitle entry to the oTitle field stripping off any leading The So at the point of adding a new movie to the db there is almost no cost in controlling how it is ordered. Another advantage here is that the user can, of course, manually edit the oTitle value to create grouped sets of movies. For example, my client wanted to group all the James Bond movies together. Now all he has to do is put 007 in front of the movie Title in the oTitle field: 007 Die Another Day. The oTitle field is not displayed anywhere in the web interface. 4. And I of course modified the INSERT and UPDATE queries to include the new oTitle field. Thanks again to everyone for their input. Sometimes [I] get so close to the trees I fail to see the forest--or the big picture, and lose track of my proper goal (by getting hung up on technique and detail). Dan Kaufman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Sunday, December 03, 2006 12:11 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db Dan? Any thoughts on this? /Charlie http://www.carehart.org/blog/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Arehart Sent: Monday, November 27, 2006 10:39 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db Dan, the problem you've hit is not a syntax problem but rather a security issue Access, it seems. For instance, if you tried that SQL statement WITHIN Access, did it work? It did for me, yet trying to pass it in from outside (from CF), it fails. And it's not a datasource setup problem. I tried both the ODBC Socket and the Microsoft Access driver in CFMX. Both failed. Doing some digging, I've found that you've run into is something that many, many (indeed, mostly outside of CF) have hit as well. Access has implemented a security sandbox for certain functions to prevent their use from outside of Access itself. There are several references that describe it. Do a search inside Access' help for sandbox to see many. Still, I tried all the things that were offered there and in other web sites to configure things to work, and none would. I tried setting the security to Low as described in the help, but no dice. I even tried a Registry fix discussed here: http://support.microsoft.com/default.aspx?scid=kb;en-us;294698Product=acc To no avail. I even tried converting an Access 2000 DB to Access 2003 and it still would not work. And I'll add that I stopped and restarted both Access and CF (and the CF datasource services) after the changes, and even reloaded a test page in a new browser window (to avoid seeing a cached result). And with none could I get it to work. I found some references suggestion that you create a wrapper function or module to get things working, but I'm not as familiar with creating those in Access let alone calling them from CF, so I'll leave this info with you for your consideration. Teddy's idea may be the better solution, if indeed there's no reason for the data to exist in its current state. If it must, I'll point out one last out of the box solution: though it's preferable to let the database do database work, in this case where you're stumped, you could pull the data into a CF query (as is) and use CF functions to manipulate it, then use a query of queries to do the sort that you prefer on the manipulated result. May not perform well, but sometimes you need function over form. :-) /Charlie http://www.carehart.org/blog/ -Original Message- From
Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
Instead of beating your head against the screen, why not write a quick CF template that will query for every title that has the word the in the beginning and run a batch update against it. In essence, you would just change The Big Lobowski to Big Lobowski, The. The template would probably not take too long depending on your record set size and you would not have to worry about badly formed titles. Since that site uses movie listings for certain things, IMDB has a Pro service: https://secure.imdb.com/signup/v4/?d=IMDbTab which they offer the ability to use their database. Teddy On 11/26/06, Tom Chambers [EMAIL PROTECTED] wrote: I've run into many issues using CF-built queries on an Access db over the years. I recall one that had about a dozen doublequotes in it. Freaky! Have you tried including the [] around the field name just for fun? T [EMAIL PROTECTED] wrote: Sorry, the double quotes in the query statements are simply a typo on my part. I didn't copy and paste the actual query, I simply retyped it as I wrote the email and out of bad habit used quotes. The quotes ' or is not the problem. This query and/or using the REPLACE function works perfectly well on all my SQL datasources. This particular site (still) has an Access db. And therein lies the rub. Yes, I could, and probably should, upsize the Access mdb to SQL-2000, but for right now that's more trouble than simply making the query behave. So, any ideas on how to use the REPLACE function in a query on an Access db? Or, baring that, how would you create a similar end result of ORDER BY the mTitle column ignoring the if it is the first word in a movie title? Thanks, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Chambers Sent: Sunday, November 26, 2006 4:45 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db did you try single quotes? [EMAIL PROTECTED] wrote: I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=gin.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - -- cf_payne / Adobe Certified ColdFusion MX 7 Developer Atlanta CFUG (ACFUG): http://www.acfug.org - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
[ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
did you try single quotes? [EMAIL PROTECTED] wrote: I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
RE: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
Sorry, the double quotes in the query statements are simply a typo on my part. I didn't copy and paste the actual query, I simply retyped it as I wrote the email and out of bad habit used quotes. The quotes ' or is not the problem. This query and/or using the REPLACE function works perfectly well on all my SQL datasources. This particular site (still) has an Access db. And therein lies the rub. Yes, I could, and probably should, upsize the Access mdb to SQL-2000, but for right now that's more trouble than simply making the query behave. So, any ideas on how to use the REPLACE function in a query on an Access db? Or, baring that, how would you create a similar end result of ORDER BY the mTitle column ignoring the if it is the first word in a movie title? Thanks, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Chambers Sent: Sunday, November 26, 2006 4:45 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db did you try single quotes? [EMAIL PROTECTED] wrote: I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db
I've run into many issues using CF-built queries on an Access db over the years. I recall one that had about a dozen doublequotes in it. Freaky! Have you tried including the [] around the field name just for fun? T [EMAIL PROTECTED] wrote: Sorry, the double quotes in the query statements are simply a typo on my part. I didn't copy and paste the actual query, I simply retyped it as I wrote the email and out of bad habit used quotes. The quotes ' or is not the problem. This query and/or using the REPLACE function works perfectly well on all my SQL datasources. This particular site (still) has an Access db. And therein lies the rub. Yes, I could, and probably should, upsize the Access mdb to SQL-2000, but for right now that's more trouble than simply making the query behave. So, any ideas on how to use the REPLACE function in a query on an Access db? Or, baring that, how would you create a similar end result of ORDER BY the mTitle column ignoring the if it is the first word in a movie title? Thanks, Dan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Chambers Sent: Sunday, November 26, 2006 4:45 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] using REPLACE within CFQUERY for ACCESS db did you try single quotes? [EMAIL PROTECTED] wrote: I have an Access datasource for movie listings. mTitle is the column/field for the movie's Title. I need to output the records ordered by mTitle, however I need to ignore the when it is the first word. For example: The Big Lebowski should be ordered by B in Big, not T in The. My solution was to create an alias column in my query wherein I REPLACEd the with and then ORDER BY the alias column. SELECT mTitle, REPLACE(mTitle, the, ) AS titleOrder FROM movieTable ORDER BY titleOrder However, when I run this I get an error that the function REPLACE is undefined: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'REPLACE' in expression. I have Googled for an answer to my problem (till I'm blue in the face) but can't find anything HELPFUL. Can anyone help OR suggest a different solution? Thanks, Dan Kaufman - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=gin.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com - - To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -
