Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: opening a form across network

    I know this has been posted about before, but I can't seem to find the orig post. I've got a frnt end / bk end situation. It is my understanding that if a form is bound to table that the entire table's records will be passed to the form on the local machine. I'm interested in shrinking the size of the recordset that will be passed. Will a where condition in the docmd.openform accomplish this task? Or is there a better way to do this? Any replies appreciated. TIA

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure exactly how the front/backend schema works with access jet-sql. i can speak for t-sql dataset construction FROM access and say that the where clause WILL restrict the size of the actual dataset returned to the client. I'm fairly certain that's an SQL standard, but then again, access bastardizes SQL implementation in so many other ways that it wouldn't surprise me if it fails here too....

    Your milage may vary.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't!

    .MDB backend is a dumb file and your file server doesn't understand SQL.
    A already does it's best to cache stuff, but how good can it get with multi-users on a dumb file?

    you are doomed until you get something sql-aware on the backend (or a YottaBit/sec network, or shoot all the users).

    better way:
    mySQL (is free) and runs under Linux (is free).
    mySQL is also wicked wicked fast (in multi-user too).
    ...find a retired PC (is free maybe), feed it some RAM (a few dollars - somewhere between 512 meg and 2 x db size), and run it as a mySQL server. there's also postgreSQL (is free and can be even faster: mySQL is absolute tops for simple SELECTs, postgreSQL is reported to be faster for other more complicated SQL stuff).

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    Would it make a difference in the data set size if I changed the recordsource SQL statement on open and chgd it to an MT string on close of the form. This whole thing stems from a situation where I have different branches of the company USA and CA opening a bk end database. The CA end is much smaller than the USA end but they would use the same tables and have no use for the US data, not to mention it would eat up alot of network time because the fleet in the US is ten times the size of the CA fleet. Or am I better off with two backends and a separate front end designed to access both? Any replies appreciated. TIA

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    backendMDBsize/effectiveLANbandwidth is what sets your .MDB backend performance
    (+/- !!! table design, # of users, server speed etc are also in the equation)

    if you can split the backend into smaller chunks (perhaps consolidating just for global reporting or whatever) you will improve your performance. smaller chunks means different .MDB and not different tables!!!!!

    you can link to .MDB thru code ....maybe allow any user to see any .MDB but only one small chunk at a time.

    izy
    currently using SS 2008R2

Posting Permissions

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