Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    60

    Unanswered: Multiple Users, QueryDefs, What the???

    hi, i have some spare time at work at the moment (gotta love the gfc) so im going over a database i made. over time i have tacked things on to it, so now i am starting fresh and re-doing the whole thing, improve efficiency etc. im building it in access 2007 with its new native format. i have a back end on the server and the front end was a mde also on the server. never had a problem with this. the basics of my database is a form with a combobox, which chooses the job. there is also a list of documents, which gets its values from a query. the query is filtered per job by reading the combobox. there is then a bunch of fields that filter the list/query further using CurrentDb.QueryDefs("neRoksQuery").SQL = ???. basically it re-does the query every time. at first i had the list running its own 'custom' query (not a saved query, one in the control), but i wanted to run a report from exactly what was in the list. this was possible but not to the extent i needed. each document has multiple questions, which are all stored in a seperate table. i need a filter to go through all these question and find specific words, then only shows these docs. the report was limited in how i could pass the filters to it, and i could not work out how to change its recordsource on load. so the solution i found was to make a common query that both read from. all is working well.

    now my question is, if multiple people open the same accdb and change the filters, is 1 persons change to the query gonna overwrite the other persons? what if i change to a common accde, like it used to run? i tried making a accde but got an error, think its coz i havent fixed all the code yet (compile error). or, will each user have to run a local front end. i wanted to get away from this, because it makes keeping everyone up-to-date that little bit harder (unless everyones 'short cut' is a batch file that copies latest version at start-up, but its just a little bit more of a headache, especially with vista always wanting things controlled).
    Last edited by neRok; 07-21-09 at 21:34.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so either write to the querydefs collection or deploy a front end to each and every desktop

    in terms of deploying a specific front end, there is code in the code bank which checks to see if the user has the most up todate version and if not downloads a copy. I'm pretty certain it was contributed by PKStormy
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2006
    Posts
    60
    i didnt think 'updating' the front end would be a problem, just a slight inconvenience. is there speed improvements having the front end on the workstation rather than a network drive?

    when i read your first line, i had no idea what you were talking about (im a drafter thats made a database, so its all trial and error!). i got the querydef command from searching google and it did what i needed (updated the query). so i have gone and done some reading and kinda understand querydefs. the named querydefs are what appear in the 'list' along with tables, forms etc. then there is also termporary (un-named) querydefs, which from my reading are usually opened as a recordset. so, can the recordset be used to populate the list and also the report? im not sure about 'public' things like this, most of my experience is private subs in forms with a few common functions. i think i have jumped in the deep end here, from my puddle lol.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there may well be a benefit for using local copies of the front end, but I doubt it will be that significant. your users will find it quicker to start, your netwrok trolls will like it as there should be less traffic on the network.

    it allows you to have local tables which are specific to the user

    it allows you to make local copies of static information. say your you have masterfile data which doesn't change you could copy that to the local machine once and run it form there. however its a dangerous strategy unless you as developer understand what happens. its not something I'd do unless I was absolutely certain there was a performance benefit to be had.

    as regards the querydefs comment in the first post, its someitmes a little hard to understand what experience people have, and I don't have the time that often to go into the gruesome details. but I'm glad that the querydefs approach worked for you. and +1 for taking the time to pickup on what was suggested and do some work on your todd to make your app work. sadly there are too few here prepared to do that, the cast majority deamnd a solution to their problem right now...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •