?
Solved

Executing Access Queries from ColdFusion using linked tables

Posted on 2005-05-04
11
Medium Priority
?
278 Views
Last Modified: 2013-12-24
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...

0
Comment
Question by:KoldFuzun
  • 5
  • 4
  • 2
11 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 2000 total points
ID: 13930498
This is a restriction Access places.

It also restricts calling certain functions, etc from the web.

It is strange because if it were not a linked table it would let you use it no problem.  I have not gotten it to work otherwise - maybe if someone else has they will post here.

Most queries (if access wrote them) should be re-written anyway to be cleaner.
If hand written then they may be able to be simply transported to SQL and called from there.

Honestly it is better to have the web talk to SQL directly anyway....
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 13930888
>> Honestly it is better to have the web talk to SQL directly anyway....
So True. Also there are security issues involved which we can not just start talking.
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 13931281
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! :)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 35

Expert Comment

by:mrichmon
ID: 13931395
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)
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13931414
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.
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 13931496
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 13931647
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13931679
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.
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 13931701
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!
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 13931721
Glad to provide any help we can - even when it isn't much.
0
 
LVL 2

Author Comment

by:KoldFuzun
ID: 13954393
rewriting all the queries, lol. Thanks guys
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question