Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-22
6
Medium Priority
?
61 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 28

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 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