Results 1 to 12 of 12
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: Advice, Do's & Donts needed before I network..

    Hi guys,

    Im about to put my database on a shared drive at work. I know i have to split the application from the table. I have never done this before.

    Just so i keep everything in check its an XP network that we use. What advice can you all give me on what i need to do.

    Thanks


    Aboo

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Splitting the mdb is a fairly easy process. Here's some things I personally do but others may do it differently.

    1. Keep the backend and frontend in the same folder(s).
    2. If backend is in a different folder, make sure the user also has read/write permissions to that folder.
    3. If any table changes are made, refresh (or relink) the tables in the frontend.

    You'll notice that splitting an mdb (via the wizard), is the same as if you were to go to: Tools-> Get External Data -> Link Tables (and then navigate to your backend mdb). If you're using some kind of permissions, I usually check the "Save Password" box when linking an external table (along with the other options).

    4. Make sure both the frontend/backend are on a decent fast network drive. A slow network drive (or a drive which is hounded by antivirus software continually running scans), can cause problems. I tend to avoid having the backend on a totally different network drive.

    5. Make sure all users connect to the backend via the same drive letter. An F: drive for one user might be a G: drive for another user. Usually MSAccess can keep track of this but it's something to consider. UNC names are ideal to use.

    6. Don't forget to compact/repair the backend as well as the frontend.

    7. After splitting the frontend/backend, I then like to use the "cloning" method in the code bank. This clones the "source" frontend mdb so that no 2 users are in the same frontend mdb (which will prevent errors like "the database is currently locked by another user....") - http://www.dbforums.com/6274786-post19.html
    Last edited by pkstormy; 04-19-09 at 11:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2006
    Posts
    178
    Thank you PKstormy, I really appreaciate all this help. I have some more question though.

    the first being that i have read somewhere that only 10 people can access the database at a time. if it is the case. how can i make the database automatically kick out idle users? or have some way of finding out who is still using it?

    2nd question, once i have split the databse can i stop people from messing withe the tables if it is in the same shared folder?

    Thanks

    I hope you guys can help..

    Aboo

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I like to do is

    map the backend by it full pathname

    ie \\servername\sharename$\data.mdb

    doing it this way the user can't see the map drive there for what they can't see they can't delete.

    and the $ make it a hidden shared ask your IT to set that up
    I always put me frontend on the local PC in a folder call C:\Screens\databasename.mdb
    and I always have a backup copy on the server
    \\servername\sharename$\screens\databasename.mdb

    then if the user delete the local copy now thing is lost
    if you have a spare licnece of msaccess put that on the server

    on the back end create a autoexec macro that close the database
    them tick the setting in access that compact on close.

    then make a "Scheduled Tasks" that open the database say 2.00am in the morning the autoexec will closeit then the compact kick . that that part of the job done
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    May 2006
    Posts
    178
    Hey Myle


    Thank you for your response, I have a question or two regarding your technique.


    always put me frontend on the local PC in a folder call C:\Screens\databasename.mdb
    and I always have a backup copy on the server
    \\servername\sharename$\screens\databasename.mdb
    Are you talking about a back up copy of your front end?


    Also i havent used scheduled task, is this a simple tick in the box somewhere?

    How can i get the program to back its self up on anohter folder?


    Thanks


    Aboo

  6. #6
    Join Date
    May 2006
    Posts
    178
    One more thing, how can i keep track of who is currently using the database?

    Thanks

    Aboo

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    If it's an Access back-end you can use the Jet UserRoster to find out who's in at any given time (provided you have persistent connections). Though I usually use some kind of a login log where the startup form writes to a text file who logged in from where and at what time, etc.
    Me.Geek = True

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    you can put the database anywhere its just what i do.

    the trick with "Scheduled Tasks" is making shour the account name and password is right.

    backing up hmmmmmmmmmmmmmmmmmm
    all I do is write a batch file and make Scheduled Tasks to exe that batch file
    1 thing when backingup Don't copy the backup file to the same drive as where the live one as it not a backup then is it.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    May 2006
    Posts
    178
    nckdryr,

    I am using the getuser function to retrieve the login, how can i write to a txt file?

    also do you know if it is correct regarding the more than 10 users on an access 2000 database will cause it to crash?


    Thanks

    Aboo

  10. #10
    Join Date
    May 2006
    Posts
    178
    myles,

    thanks for your help. Is there any other things i need to take into account? sorry for being so persistant i just dont want anythign to go wrong.


    nckdryr

    ive looked at the user log, i like the style. i looked at the design but i cant see any code, all i see are 2 forms a splash and the user log. am i missing something where is the code?


    Regards


    Aboo

  11. #11
    Join Date
    May 2005
    Posts
    1,191
    To look at the code you can go to the microsoft page that is linked on that page, I just took that and adapted it to output to a listbox instead of the Immediate window.

    To write to a textfile involves an fso and a light-moderately complex code. The alternative is to simply write to a table that only the admin can see, but this can bloat the size of the database with unnecessary data.

    As for the max number of users in an Access BE, in my limited experience it really depends on the network and the pc's. I've had it where 3 or 4 can be in no problem, but I've also had where you can instantly tell when anymore than 1 is in. Trial and error I guess. Else you'll need a BE server (Oracle, SQL Server, or something).
    Me.Geek = True

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Aboo,


    If you use the Cloning technique here: http://www.dbforums.com/6274786-post19.html

    You won't have to worry about kicking any users out. You can have hundreds of users user your application at the same using this technique.

    Using this technique, you can also VERY easily see who is currently using the database (and even when they last used it).

    You may want to give it a try (it's easy to implement) and there's a lot of benefits to it.
    Last edited by pkstormy; 04-22-09 at 21:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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