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

x
?
Solved

Preserving queries in Access frontend

Posted on 2009-05-18
20
Medium Priority
?
256 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 2000 total points
ID: 24413891
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
ID: 24413978
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
ID: 24414637
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 52

Expert Comment

by:Gustav Brock
ID: 24414845
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
ID: 24414904
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
ID: 24415080
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
ID: 24415179
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
ID: 24415206
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
ID: 24415329
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
ID: 24415375
"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
 
LVL 29

Expert Comment

by:Badotz
ID: 24415532
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
ID: 24416511
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
ID: 24416591
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
ID: 24416657
"*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
ID: 24416678
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
ID: 24416764
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
ID: 24416851
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
ID: 24416902
"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
ID: 24417254
>>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
ID: 31582664
thx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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. …

773 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