Solved

How can a send parameters to a query contained in a Access 2013 custom app?

Posted on 2014-10-22
6
57 Views
Last Modified: 2016-07-05
How can a send parameters to a query in a access 2013 web app (i.e. one that has been deployed to SQL azure) from Excel 2013? When I use the data connection wizard using native sql 11 client it only shows non parameterised queries. When I try use Microsoft Query is complains about connecting to Azure due to firewall rules. Are there any other ways?
0
Comment
Question by:james saulez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 168 total points
ID: 40399092
As far as I know, you can't. You cannot interact with Access web apps from a desktop app, and a Query (or View) is part of that web app.

If you can change the Firewall rules to allow you to interact with the Azure database, then you might be able to do so - however, you'd have to ask the SQL Server or Network guys how to do that.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 332 total points
ID: 40400226
You can however ... Link to the Azure SQL tables from the Desktop side via VBA code ... and consequently construct queries against those tables ... again on the desktop side ... fwiw.  This allows you to build ... for example ... a tool to manage tables on Azure - kind of like a back office tool  or whatever.

I highly recommend Jeff Conrad's (Microsoft) book Access 2013 Inside Out ... available on Amazon. He has multiple sample web apps that give a pretty good idea of what can be done :-)

mx
0
 
LVL 84
ID: 40400510
Link to the Azure SQL tables from the Desktop side via VBA code
Right, but he has firewall issues. As I mentioned, he'd have to get those issues straight first.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 332 total points
ID: 40400668
I pinged Jeff Conrad and here are two responses:
===

[Jeff -1]
Off the top of my head, it might not be possible to pass in parameter values directly from Excel 2013 to parameterized Access web app queries through the external connection links.
I’ll ask around the team to either get confirmation of that or to see if there is a workaround.


[Jeff -2]
If he was having firewall issue, the poster wouldn’t be able to see the non-parameterized queries in Excel 2013. So I think the poster is following the connection steps correctly.
To be sure, point them to this blog post: http://blogs.office.com/2013/01/22/visualize-your-access-2013-web-app-data-in-excel/
Make sure they have clicked “From Any Location” under the Manage options.
If they can make a connection to the web app tables using Excel, then there are no firewall issues.

I still think the end goal might not be directly possible – passing in parameters from Excel.


====
0
 

Author Comment

by:james saulez
ID: 40400684
Hi Guys

I appreciate the response. So I can connect fine (no firewall issues). The problem is that the only queries that show up to display via PowerPivot are the non parameterised ones. I then tried the "old" Microsoft Query method in Excel. That complains of firewall issues when trying to connect to the deployed web app db on Azure. its almost liked the Microsoft Query method can only connect to a desktop Access db.

But I think Jeff answered the question of can you pass query parameters to via Excel to a access 2013 web app. Seems the answer is no.

Would be very useful.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41691624
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Scott McDaniel (Microsoft Access MVP - EE MVE ) (https:#a40399092)
-- DatabaseMX (Joe Anderson - Access MVP) (https:#a40400226)
-- DatabaseMX (Joe Anderson - Access MVP) (https:#a40400668)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

738 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