Solved

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

Posted on 2014-10-22
6
50 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
6 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 168 total points
Comment Utility
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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 332 total points
Comment Utility
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
Comment Utility
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 332 total points
Comment Utility
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
Comment Utility
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 26

Expert Comment

by:MacroShadow
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now