Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Preserving queries in Access frontend

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
internetcreations
Asked:
internetcreations
  • 7
  • 7
  • 4
  • +2
1 Solution
 
BadotzCommented:
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
 
peter57rCommented:
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
 
internetcreationsAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Gustav BrockCIOCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
internetcreationsAuthor Commented:
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
 
BadotzCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Wellll ... yes, that requirement does complicate the issue.  Sorry, I missed that - since you added that on from the original Q.

mx

0
 
internetcreationsAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
BadotzCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
BadotzCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"*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
 
BadotzCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
BadotzCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
BadotzCommented:
>>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
 
internetcreationsAuthor Commented:
thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 7
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now