?
Solved

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

Posted on 2014-10-22
6
Medium Priority
?
60 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 672 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 1328 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 85
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1328 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

719 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