How to use queries in Access Services 2010

Question

I'm designing what should be a fairly straightforward database. One of the functions I need is to be able to populate a table, via a query, among other similar functions. I need to be able to perform these actions in a queries within a macro.

Normally I would create a macro with a number of open query lines and stick it on the dashboard.

However, there is no open query option in the macro setup for a web database. This is shocking to me because it feels like a really core access functionality and not having it is like... not having a stick shift installed in a manual... (I guess you're cruising in first and never backing up?) 

I'm assuming I'm not the only one who's run into this problem, and I assume there must be an alternative functionality that allows you to perform the many tasks normally in queries in access macros, but I can't seem to find out what it is.

One rather vague post on this forum refers to data macros - but my impression was that those were in VBA and I can't really believe you'd need to know another programming language to integrate this kind of functionality with sharepoint.

Please tell me I'm missing a big "put your query here" sign?

Any help is appreciated!

Thanks,

Matt

As a side note, does anyone knwo WHY they can't support (I assume?) the openquery function within Access Services macros?

Answer 1

Hi,

Thank you for your post.

I am trying to involve someone familiar with this topic to further look at this issue.

---

Xue-mei Chang

TechNet Community Support

Answer 2

Thank you!

Answer 3

Matt,

The first item I want to point out all Web Queries are limited to select queries. You cannot create an action query (insert, update, delete) as Web Queries. Without being able to create action Web Queries there is little need for the OpenQuery macro action in a Web Macro.

That being said, you can still create action Client Queries and call them from client Macros in a web database but, as you probably guessed, this only works when you run the database within the Access client.

There are two different types of Data Macros. The first are the "equivalent" of triggers in SQL Sever they are going to fire when data is Inserted, Updated or Deleted. You can also create Named Data Macros that can be called from a web form or web macro. While the data macros are written in VBA, there are a number of "VBA Like" commands.

Given what you are trying to do, I suspect you can do this in a named data macro. My suggestion would be to:
Use the the "ForEachRecord" command with a filter/where condition to select the records you want to copy (this is the select part of the query).
Within the "ForEachRecord" command block use the "CreateRecord" command to insert the record into the table (Part one of insert command)
Then within the "CreateRecord" command block use the "SetField" command to set the fields within the table.

I will admit that this is more work than using a client action query but that is my best suggestion but hopefully the articles below will help you understand this new functionality.

Create a data macro
http://office.microsoft.com/en-us/access-help/create-a-data-macro-HA010378170.aspx?CTT=1

Video: Create a data macro
http://office.microsoft.com/en-us/access-help/video-create-a-data-macro-VA100305331.aspx?CTT=1

Create a user interface (UI) macro
http://office.microsoft.com/en-us/access-help/create-a-user-interface-ui-macro-HA010341573.aspx?CTT=1

Access 2010 data macros (similar to triggers)
http://blogs.office.com/b/microsoft-access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx

If that that does not resolve the issue and you wish to pursue this further you should open a support incident. The following article should provide you with instructions on how to do this.
http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Best Regards,

Donald M.
Microsoft Online Community Support

--------------------------------------------------------------------------------

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

Donald.

Thank you for your detailed reply. While this isn't exaclty what I was hoping for, it does give me a much better understanding of the available functionality and a road to implementing a solution.

I appreciate the information.