Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    15

    Unanswered: MS Access Frontend/ Backend MS Access

    I completed a database application, "Tech Support.mdb" However for the time being it is being shared over on the server. Other computers are accessing that application on the server. Obviosly doing this slows down the performance (but we only have 2-4 users at most accessing DB at the same time).

    I figured if I split the MS Access application it would run better over the network but it actually slows down even more, when I LINK my MS Acccess tables(Tech Support_DATE.mdb) to my application "Tech Support.mdb" All my forms are bound to queiries that join, one in paticular loads very slow becuase it joins 4 tables. I know that won't be as fast as others but its unacceptably slow when I link my tables.

    So my question in hand is, if I want to see better performance, as in quicker data retrievals, loading forms (bound), reports, would it be FASTER if i export my MS Access tables to MySQL and link them? Or would I get the same performance issue? Is linking table causing my database to be slow because it seems like it.


    I was thinking possibly using MS Access as frontend, every User have the "Tech Support.mdb" on their desktop Link MySQL tables to the "Tech Support.mdb" How does that sound? Can someone please help with this issue, giving me the advantages of doing it this way. Thanks everyone.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    As I'm sure you know, there are numerous issues with network traffic and server performance, so it may not be just Access or just your server.

    If you're going to export your tables, you run the risk of inconsistent/conflicting data updates. If your app is such that you can live with these risks (and I can't think of how you would be able to), you'd be better off by creating local tables in the front-end from your back-end linked Access tables, then running queries off those local tables.

    You may want to also try using an unbound form and using recordsets to query the data and return the results of that recordset to your form controls, again, you would need to determine what sort of recordset cursor you wanted (i.e., locking or not, etc) to ensure accuracy of data updates.

    Moving code to compile / run locally should improve performance, though.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Mar 2002
    Posts
    32
    MySQL for a backend is the way to go. This is what I did here where I work for some custom apps.

    I put the tables in mySQL and just distributed and access .mde as the front end.

    It Flys!

  4. #4
    Join Date
    Mar 2004
    Posts
    15
    Originally posted by HomerBoo
    As I'm sure you know, there are numerous issues with network traffic and server performance, so it may not be just Access or just your server.
    * I figured it is slower than usual becuase I first placed my orginal database w/o splitting. And that database runs faster than the database that i've split (putting tables on server and GUI on client desktop) And these two databases are identical.


    You may want to also try using an unbound form and using recordsets to query the data and return the results of that recordset to your form controls.
    * You mean use the Recordset object in VB and from their open recordset by using my SQL query and then set the recordset to my unbound form?


    Moving code to compile / run locally should improve performance, though.
    * You mean putting the GUI on the desktop and the Data Tables on the server would improve performance right? Thats what I thought, but what it did to my database was descrease performance

  5. #5
    Join Date
    Mar 2004
    Posts
    15
    Originally posted by BenCustalow
    MySQL for a backend is the way to go. This is what I did here where I work for some custom apps.

    I put the tables in mySQL and just distributed and access .mde as the front end.

    It Flys!

    So do you know how much faster it was when you linked yoru tables in your MS access to a MySQL server table? As in loading forms (based on query), generating reports, searching, querying..?

  6. #6
    Join Date
    Mar 2002
    Posts
    32
    Never really measured the speed, so I can't quantify it. Let's just say ALOT! Let's see across our WAN which is a T1....it used to take a couple of minutes to load a form based on a linked table. with mySQL as a backend it takes 10-15 seconds or so....so the speed was much improved. You can actually build a robust multi-user app this way.

    Also mySQL doesn't have the table size limitation either....when access get's over 10,000 records it creeps.

  7. #7
    Join Date
    Mar 2004
    Posts
    15
    Originally posted by BenCustalow
    Never really measured the speed, so I can't quantify it. Let's just say ALOT! Let's see across our WAN which is a T1....it used to take a couple of minutes to load a form based on a linked table. with mySQL as a backend it takes 10-15 seconds or so....so the speed was much improved. You can actually build a robust multi-user app this way.

    Also mySQL doesn't have the table size limitation either....when access get's over 10,000 records it creeps.
    Really I didn't know that. NOw that I've done some research it looks like their are really no advantages to use MS Access for multi-user purposes. I should've just went with all out w/ mySQL

  8. #8
    Join Date
    Nov 2003
    Posts
    267
    phake123

    Good to see you

    BenCustalow is right a MySQL is faster, but a comparison of a speed increase over WAN at 1.5MBps (T1) to a Network of 100 MBps is like comparing apples with oranges. I have several "split" MS Access DB (with well over 100,000 records) that "Fly" over a network.

    When it comes to speed issues with MS Access they are often a product of network configuration and DB design/programming. I would never use a MS Access, by choice over a Wan or Web, but for most small system enviroments Access is a very powerful tool.

    After testing you DB that you set me I would seriously look at moving to Unbound forms or try MySQL. You may like it or you may not.

    Good Luck

    S-

  9. #9
    Join Date
    Mar 2004
    Posts
    15
    Good to see you too, did you read my reply on the other messageboard? small world aint it!

Posting Permissions

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