Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81

    Unanswered: MDE files with multi-user database

    I have a database with about 100k records and 20 users in Access 2000. Performance has slowed lately, and I am wondering abotu making the front end into an MDE file. So far, there is no VB in the database and both front and back ends are MDB files.

    I tried to make an MDE from the front end and got an error
    "Microsoft Access was unable to create and MDE databse" Could this be because there are no tables in the database (all linked tables) or no VB?

    I need to figure out why I couldn't make an MDE file, but I guess first and perhaps more important, does anyone think this will help performance? Will it reduce the size of the front end? (2 MB when compacted)

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try to make an new DB Access 2000 (Empty), then copy all your objects to it then make your MDE file

  3. #3
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68

    Re: MDE files with multi-user database

    MDE can increase performance in a network environment, but I don't think it will be anything spectacular. Did you consider splitting your database? Since you are working only with attached tables anyway, you could move all your other database objects (forms, macros queries etc.) in an "application" database which would contain only links to the "data" database. (There is even a wizard that helps you to do that). Then you can copy that DB on each end user's machine. This setup can cause maintenance problems, but it is an acceptable solution if your application is relatively stable and you don't have to make frequent changes in your code or in other database objects.

    I think with 20 users your are on the upper limit for Access in a multiuser environment. Access is a great desktop database, but it can't replace a true database server. Trouble starts as soon as the number of users increase. If they mainly read data, it is OK, but if they do frequent inserts, updates and deletes, one of the very likely problems is database corruption.

    Another solution could be to use an Access Project.

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    perhaps I wasn't clear enough in my original message. the database is already split into a front and back end.

    There are soem fairly frequent code changes making it hard to keep the front end on users machines (as opposed to one copy on the network).

    is having the front end on the network (as opposed to users machines) going to kill performance? The Access documentation about splitting the database didn't make any recommendations one way or another, but that wouldn't be the first thing Microsoft didn't documen.......

    I'm not familiar with Access Projects so not sure if extra software, etc. would be required. We may move the database to SQL Server eventually so am not sure if this would be too time consuming or costly of an
    Last edited by mdr02125; 10-29-03 at 12:20.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about a table in the backend (maybe you have one already with some system info etc) where you store the current front-end revision number.

    the frontend checks it's own revision against the server on start-up and updates itself if necessary.

    i've had this as a "one of these days" project for some time. it would be interesting to try.

    open questions include:
    --copy the entire new .mde frontend or what??
    --do you need a "loader" before the frontend starts (loader updates if necessary and then starts the frontend)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Aug 2003
    Location
    Belgrade
    Posts
    68
    Originally posted by mdr02125
    is having the front end on the network (as opposed to users machines) going to kill performance?
    Not necessairily, performance may still be acceptable. It all depends on how fast is your network and the machines involved. The only way to know is to try.

    As for Access Projects, all you need in addition of Access is SQL Server. You can start with the free version, MSDE (or whatever is called now), and build a test sistem (and learn the basics of SQL Server if you need to). MSDE is purposely crippled to support only five simultaneous queries, otherwise it is the standard DB engine. When the company purchases a regular version, you will easily switch.

Posting Permissions

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