KoldFuzun
asked on
Executing Access Queries from ColdFusion using linked tables
Research indicates a big no, but i would like to put this out there for all the gurus!
I have a ColdFusion application which runs on a MSSQL database. The MSSQL has an Access front-end which has a large number of queries used to run reports. I created a datasource to the Access front-end and can list these queries but cant seem to exec them because of the linked tables. Is there any way around this? Otherwise we are looking at rewriting hundreds of queries...
I have a ColdFusion application which runs on a MSSQL database. The MSSQL has an Access front-end which has a large number of queries used to run reports. I created a datasource to the Access front-end and can list these queries but cant seem to exec them because of the linked tables. Is there any way around this? Otherwise we are looking at rewriting hundreds of queries...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wholeheartedly agree with these points... in regards to security, its not really an issue since its an intranet application completely inaccessible by the outside world. The major issue being the amount of time required to rewrite hundreds of queries! :)
Well I don't think pinaldave meant security in that sense (although you have to consider that too), but he was referring to the security that access sees.
Access sees that it is a possible security problem because it doesn't know where the linked tables are actually located. In fact anything outside of the access file itself access considers a security problem (hence access not allowing you to use functions that are completely contained within access if being called from an outside application like the web)
Access sees that it is a possible security problem because it doesn't know where the linked tables are actually located. In fact anything outside of the access file itself access considers a security problem (hence access not allowing you to use functions that are completely contained within access if being called from an outside application like the web)
My point was that you may not need to rewrite the queries depending on how they were initially written....
1) If the linked tables in acess were named the same as the actual tables
and
2) If the qeries were written by people as opposed to by access
then they will mostly convert accross easily
If there are access specific functions then you will have to convert the functions, but you shold anyway as you will be getting more efficient coding.
1) If the linked tables in acess were named the same as the actual tables
and
2) If the qeries were written by people as opposed to by access
then they will mostly convert accross easily
If there are access specific functions then you will have to convert the functions, but you shold anyway as you will be getting more efficient coding.
ASKER
unfortunately, many of the queries query queries... thats a mouthful. However, it makes it impossible to simply cut and paste them as stored procs... not the way I would have chosen to build my queries but then again the entire Access interface wouldnt have been my choice either. Which is why I migrated the db to MS SQL last year but now I have this issue, haha.
Thanks for the input guys. Im gonna leave this open for a couple of days and pending no feasible solution I will award the points to mrichmon
Thanks for the input guys. Im gonna leave this open for a couple of days and pending no feasible solution I will award the points to mrichmon
I just got here - busy with work. mrichmon deserves all the respect here as he has explained you very well. Regarding security Mrichmon has explained you what I intended, access sees everything outside as security threat and creates many problems. Thank you.
Yes migrations are always painful and slow - especially out of access ( I fell for you as I am doing that myself right now on several projects)
But in the long run worth it.
But in the long run worth it.
ASKER
the amazing thing was the initial migration was easy and only took two days. Unfortunately the database is 8 years old and poorly written to begin with. I wanted to start fresh but they would not allocate a budget for it, which I can understand. So I have to cope with this clumsy access front-end with its bulky queries and reports. Im hoping someday they will let me at least rewrite the interface in CF/Flash (or just CF/HTML!). Oh well it is what it is, maybe complications like this will help open their eyes. Again, thanks guys!
Glad to provide any help we can - even when it isn't much.
ASKER
rewriting all the queries, lol. Thanks guys
So True. Also there are security issues involved which we can not just start talking.