Solved

Preserving queries in Access frontend

Posted on 2009-05-18
20
247 Views
Last Modified: 2012-05-07
We have an Access frontend that is distributed via a script to every user's local hard drive when they login to Windows. These users create and modify queries in the frontend, but these are changes are lost when the MDE is overwritten on their next login. What is a good automated way to handle this problem?
0
Comment
Question by:internetcreations
  • 7
  • 7
  • 4
  • +2
20 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 500 total points
Comment Utility
Allow the users to create a separate database for their queries, then link to that database from your frontend.

Either prompt for the name of the database or browse for it.
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
I would say that there is no 'good' way to do this.
Whatever you try will be a 'hope for the best' scenario.

I would be inclined to see if it were possible change the distribution script so that it renames the current front-end and then copies in the new frontend.
In the application you would build in an import routine to get the queries from the old database.
You could do this if each user saved their queries with a distinct prefix code (e.g "MY").

There is code in the link below which coud be modified to do this.
http://support.microsoft.com/kb/298174
0
 
LVL 1

Author Comment

by:internetcreations
Comment Utility
Badotz, you mean just make a second file with all the queries that resides on the network share?

peter57r, that's not a bad idea, but unfortunately the new/modified queries would not be available to all users. Somehow the queries would need to be aggregated and moved into the master copy of the FE, so changes to the queries would propagate to everyone in the office.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You could use replication but, boy, that could (would!) turn into a nightmare.

I would somehow collect the users' "creations" when an update approaches and copy them to your master before publishing the new version.

/gustav
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
My suggestion would also be what Badotz suggested.  Let the user create their own 'AdHoc Query' MDB - which is linked to the backend on the server.  Or, have a script that creates (and does the linking) one time - if the AdHoc mdb does not exist on a user's system

This seems to be the least complicated and lowest risk.  The user should have the responsibility of backing up the AdHoc mdb.

mx
0
 
LVL 1

Author Comment

by:internetcreations
Comment Utility
DatabaseMX, perhaps I am misunderstanding the suggestion, but how would one user's changes/additions to queries be transferred to the rest of the users if they each have a query MDB?
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
That requirement was not clearly stated in your initial question.

If the queries are to be recorded permanently, then the FE should be updated with each user's ad-hoc queries. Then the distributor of the FE would have to ensure the user's queries are included in the next available FE.

This would be w-a-y too much trouble and housekeeping; if the users must share their queries, I suggest copying the SQL as text and emailing it to whomever want/needs it.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Wellll ... yes, that requirement does complicate the issue.  Sorry, I missed that - since you added that on from the original Q.

mx

0
 
LVL 1

Author Comment

by:internetcreations
Comment Utility
Badotz and DatabaseMX, sorry for being unclear in my initial question. This group of users was previously accessing a single database file located on a shared drive, so they all shared the same queries. We recently switched them to a setup where each user gets a copy of the frontend on their local hard drive and now we need to recreate the same behavior as before where all changes to queries are available to all users. All the ideas I can come up with for this are not particularly elegant.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"We recently switched them to a setup where each user gets a copy of the frontend on their local hard drive "

Well, that is certainly a GOOD move for sure.

So, maybe for the AdHoc query scenario ... as we suggested already ... create a separate AdHoc mdb that does remain on the on the server.  However, you may run into various with simultaneous user trying to create modify queries on the same mdb ... or are you saying this is how that was being done already?

mx
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:Badotz
Comment Utility
Here's a question: if an ad-hoc query becomes permanent, is it still an ad-hoc query?

If yes, then we need to discuss semantics.

If no, then make the permanent query part of the backend.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I'm using the term Ad-Hoc to refer to a query created by an end user, as opposed to the developer.

How would you make the queries part of the BE?  That would require users to get into the backend for development, etc.

mx
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
If a query is to be saved, then save it in the backend. Send someone in I.T. an email with the SQL of the query and ask that it be (at least considered) for inclusion, either in the BE or the FE, wherever it is more appropriate. Or some more elegant solution, perhaps, but that will entail much more work.

All I am suggesting is if a query needs to be saved for future use, it could be saved as part of the database, *not* as  an ad-hoc query that must be re-created by one or more users. Get it off the user's workstation and into the database for *everyone* to use.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"*not* as  an ad-hoc query that must be re-created by one or more users."

Well, that's not what I suggested.  A common AdHoc MDB on the server - linked to the BE ... and it's only purpose is to create/modify queries.  This way, all queries are  available to all users.

mx
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
Understood - but why add a layer of complexity when simply including common queries in the distributed FE would do the same thing (depending on the distribution schedule).
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Seems to be the layer of complexity would be getting the adhoc queries into ... the FE ... and further, providing a way to access them.   With the separate ad hoc mdb approach, a user simply opens it ... adds and/or modifies queries ... and your done.  

The only thing I'm not sure about is ... if user A has the mdb open and is 'designing' a new query in design view ... if user B tries to open the mdb ... will the get the "You don't have exclusive access to the database at this time ...." message.  I'm pretty sure that is the case with forms, but not sure with queries.

mx

0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
Yes, there will be added complexity somewhere.

But how can you make User A's queries available to the rest of the alphabet *without* more complications?

Some group will have to own the process (of importing user queries). I suggested I.T., but it could be any user sophisticated enough to design a query. I suggested the FE because that seems to be "common ground", albeit I do not know how often it is refreshed to the Poor Users.

Placing all of the queries into the FE makes them available to all.

NOTE: Wouldn't it be grand if you could group your queries within Access, like a real directory? Then user queries could be kept separate from production queries...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"Wouldn't it be grand if you could group your queries within Access,"
Well, you can do that so some extent with a prefix name convention .. eg

zuqryUserA_SomeQueryName1
zuqryUserA_SomeQueryName2
zuqryUserB_SomeQueryName1
zuqryUserB_SomeQueryName2
zuqryUserB_SomeQueryName3

and so on ... this way, they sort at the bottom of the food chain in the db window.

"But how can you make User A's queries available to the rest of the alphabet *without* more complications?"

Because there is just one, common shared adhoc mdb on the server ... everyone has access to it. I don't see any complications here per se ...

mx
0
 
LVL 29

Expert Comment

by:Badotz
Comment Utility
>>I don't see any complications here per se ...

Except it is another database that must be administered...




What about it, @internetcreations - anything to say?
0
 
LVL 1

Author Closing Comment

by:internetcreations
Comment Utility
thx
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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