Solved

Buttom/Combo Box To List Queries in Access Runtime

Posted on 2014-03-25
10
805 Views
Last Modified: 2014-03-25
Hi,

I was wondering if someone could help.

We currently have an Access 2007 database, which has a number of reports and forms contained with in. Full blown Access 2007 users get a Navigation pane (see attachments) on the left of the 'front screen' where if the expand it, lists a number (30+) of pre-prepared queries. We need to deploy this database to a number of other users, and would like to use MS Access Runtime, as 99% of the functionality we need is available, and it's free vs £400+ for full blown access! However the Navigation bar isn't available in Access Runtime, so I'd like to add a button or combo box to the fornt screen, which would list all the queries available, and allow Access Runtime users to select then run them.

Is this possible? Does anyone have the code? I've had a quick look online, and seen a few posts with similar questions, but I can't seem to get them to work. Not sure if its because the posts are for a newer version of Access (we're using 2007), or if I'm getting something wrong! My Access programmming is quite basic, I'm usually more a PHP & VBScript person.

Any help greatly appreciated!

Ben
0
Comment
Question by:bjblackmore
  • 5
  • 3
  • 2
10 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39952907
Create a list box, set it's row source to:
SELECT [Name] FROM MsysObjects
WHERE (([Type] = 5) AND ([Name] Not Like "~*") AND ([Name] Not Like "MSys*"))
ORDER BY [Name];

Open in new window


That will list all the queries in the database, then to run the query do something like this:
DoCmd.OpenQuery Me.ListBoxName

Open in new window

0
 

Author Comment

by:bjblackmore
ID: 39952919
Thanks for the reply.

When you say se thte row source, is that just clicking on the 3 little dots '...' in the 'Data' options (see below pic)? When I click that I get an 'Edit List Items' dialogue box. If I enter the code supplied in there, it just displays that code as 3 options in the list box?
Row-source1.jpg
Row-source.jpg
0
 

Author Comment

by:bjblackmore
ID: 39952933
Ah, I had to change the Row Source Type to Table/Query and change it to SQL view! Think I have it now....
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39952936
Personally, I don't think you really want to expose "ALL" of the queries to users of the application.  The way I do this is I create a table which contains the names of all of the queries I want them to be able to run, and a description.

I display that table to them in a listbox so they can see the description and have a button next to the list box that opens the query using.

Docmd.OpenQuery me.lst_Queries,  , acReadOnly
0
 

Author Comment

by:bjblackmore
ID: 39952997
It depends, the people who have full blown Access just use the Nav Pane, and that appears to have access to/shows all the queries. The database wasn't designed by me or even IT, some previous user in the department that I'm modifying this for, who left long ago. We now have said department asking us to make modifications to it. I really want to spend as little time as possible doing it. If they want to maintain/edit the database properly, they need to do it as a project and get a programmer in, rather than a helpdesk ticket!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Closing Comment

by:bjblackmore
ID: 39953000
Perfect, just what I was looking for!
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39953052
You're welcome, glad to help.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39953235
"the people who have full blown Access just use the Nav Pane"

is a bad idea.  In general, you should not provide your database users access to the navigation pane.  It allows them to directly modify data in your tables without using forms.  It also allows them to modify the queries in ways that will take you forever to figure out.
0
 

Author Comment

by:bjblackmore
ID: 39953336
I know, it is a concern, however as I said, the database is not our responsibility, it wasn't designed by us, and isn't managed/administered by us, we don't have the man power or knowledge with in the department to be able to program these things.

If the department wants better control over the database, they will need to create a small project, provide a budget for a contractor with a scope of work. And having spoken with them today, they don't even know what they want to do, who they want to give access, or what access they want to provide! Sadly this is the trouble with high turn over, knowledge is lost, and the people left to take over have no idea!

What I have done, is taken your advice and modified the double click command with "acReadOnly"

So it now runs:
Private Sub ListQueries_DblClick(Cancel As Integer)
DoCmd.OpenQuery Me.ListQueries, , acReadOnly
End Sub

Open in new window


This will stop people modifying data via a query. However the department haven't approved this yet, they may actually want to modify data via a query. I will suggest to them they shouldn't, but doesn't mean they will listen!

If I could re-open the question, I'd re-allocate some points for the above comment, but I can't see if that's possible?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39953429
don't worry about the points

I just wanted to make sure you understood the pitfalls of allowing anyone to have direct access to data tables and queries.

One way to mitigate that, although only limited, is to make sure everyone has their own copy of the application on their own desktop, and are linked to the backend database.  That way they will not be able to corrupt the main database that contains the queries in their original form.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Read about achieving the basic levels of HRIS security in the workplace.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

707 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

16 Experts available now in Live!

Get 1:1 Help Now