Solved

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

Posted on 2014-10-22
6
59 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 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 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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

630 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