Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2004
    Posts
    28

    Unhappy Unanswered: suggest me in changing a normal Access databse to a muluser system.

    Hallo this is Kiran,
    I have a MS-Access database which I have been creating since 3 months.
    When we have started this project we did not know that this could be a multi user system, but now they are asking this access database in a multi user environment.
    Our IT professionals have suggested me that Access database can work as a multi-user database. So I want to use this is as a multi user database.
    One of the requirements for my multi user database is – there should not be any difference from working online and offline.
    So I have to copy the required data to the client system and show that data onto the form.

    NOW I have a problem, Selecting the data from the server and inserting it into the client system is ok.
    In my stand alone Access database the forms are directly connected to tables so I did not write any code to show data onto the form. Even now If have to write I know how to show one record onto the form.
    But the problem with my main form is this form has another sub form on it and that sub form is in datasheet view to show the 1 to N relation between the main form and sub form.

    How can I show all data’s onto a datasheet form.

    So can any one suggest how to proceed with my MS-Access database in converting it as a multi-user system?

    On Monday I have to start this, if any one can suggest me about this that would be great for me.

    I am attaching my main form which shows you the sub form on it. If you want to have a look how it looks? Please have a look. And please suggest me how to proceed.

    Any way thanks for paying attention on my problem.

    Kiran Karnati.
    Attached Thumbnails Attached Thumbnails mainformshot.JPG  

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    An Access database is multiuser by nature. You don't do anything special about it. Anyone have access to database can open and work on it freely, wihtout interrupting others.
    ghozy.

  3. #3
    Join Date
    Aug 2004
    Posts
    28

    Question ya , I read it as well, but have some problems !...

    Haiii
    Thanks for your reply...
    I hope , as you so strongly that there is no need to change anything and access can work perfectly in multiuser environment, you have already tried such type of things with your work ya?...
    My problem is my forms are directly connected to the underlying tables. So if I open the access databse which is kept in the shared folder and has access from every computer, the that Access file is getting locked and no other user can open that from other computer it is saying that some user opened and locked the data.
    May be it is possible to open Access database and work on it in multiuser environment but I think I have to adjust any property of the databse or forms to make it work , If you already did it can you pelase help me.
    Thank you ,
    Kiran Karnati.

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    im not sure if this works, but you could create another database that contains the interface for your database, and link it to the source database (where your information is). Im thinking that this could sort the database locking problem out. Im just not sure if it does actually work.

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    Open your database, open the tools menu==> Option, in this find the page "advanced" look this option :
    Default open mode , check "Open Shared "

  6. #6
    Join Date
    Aug 2004
    Posts
    28

    Smile Thank you it is working, xome more information needed please !...

    Hallo JEPI,
    Thanks a lot,
    I have changed the Openmode to "Shared" and one more thing that I had to do to make work is,to change the 'Record Locks' property of every form into "No Locks". now it is working.

    Now Its Ok if the users want to work online but now users of this system must wotk Offline too .

    So I have to copy the required data to the local database which will be in every client system.

    So can you please tell me , how to select and insert into the local database by using ADO's.

    I tried some things and I think connection string with "Microsoft Jet 4.0" driver is working.

    If you have don this before can you please tell me the excat systax for selection from the remote databse and insert into the local database.

    Thank you in advance Jepi.

    Kiran .

  7. #7
    Join Date
    Sep 2004
    Posts
    161
    Can you explain more.
    you want make replica ? copy by code ?

  8. #8
    Join Date
    Aug 2004
    Posts
    28

    Question Copy by VBA Code of MS Access

    Hei hei
    Kiran again,
    felt happy for your intrest.
    Ya, I will explain a bit more about my problem.
    Now I have a MS Access 2002 Database which is working perfectly in one system.
    Now I have splitted this database with the 'Database splitter' tools menu.I put the backend in a shared folder and front end in every client system and it is working perfectly, without doing any changes.
    But as the databse is in the shared folder and the user will have access to this folder only when he has net connection but most of the time they have to work offline.
    The user must have access to change only one set of data at a time.
    So I have to copy only that set of data from the database in the shared folder to the tables which are in the frontend MDB file.
    I think as I don't need to copy all tables, I have to create empty tables which I need to copy and fill the required data into them with VBA code.
    for this I need to kow how to connect to the databse in the shared folder and insert into the local database.(of course I have to use the style of connection to connect to the local database as well.)
    Is it comprehensible to you , if not i am sorry , please ask where did u not understand.
    Any way Thanks a lot for your intrest JEPI.

    Kiran Karnati.
    "One who Gives when u ask is Ur MOTHER but, one who always tries to know your needs and Gives what ever one can Give for U is Ur FRIEND.( SO Try to BE and get such a friend GOOD LUCK MY FRIEND )"

  9. #9
    Join Date
    Sep 2004
    Posts
    161
    I'm not a specialist for tihis problem, but i imagine two solutions based on this shema :
    Shared back-end base
    Offline back-end base
    Front end base

    1 ) On the open, of the front end, test the existence of your shared back-end, if yes change the connect property of your linked tables with the path of your shared base. Same thing if not exist with the path of your offline base.
    Run a ".bat" program to copy the shared on the offline (at the open , or close database)
    2 ) Same thing, but copy with code (ADO, DAO what you want) the tables you need.

    The advantage of the first, is transparent for your user (He can work without waiting) , but you copy all the base.
    The advantage of the second, your user can see the update and Should not interrupt it, and you copy only the file you need.

  10. #10
    Join Date
    Aug 2004
    Posts
    28

    Smile Thank you Jepi

    heiii Jepi
    Thank you
    I will try it and it is taking some time and I will inform you the result.
    Till then have nice time there.
    Babaiiii
    Kiran.
    "One who Gives when u ask is Ur MOTHER but, one who always tries to know your needs and Gives what ever one can Give for U is Ur FRIEND.( SO Try to BE and get such a friend GOOD LUCK MY FRIEND )"

  11. #11
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    here's another method to try.

    If i understand this right, each user will have a copy of the back end on their own machines. What you could do is to let the users make changes to the offline database, then use a validation upload routine every time the computer is online to update the back end, and then re copy the completely updated database back to their client machines.

    Are all the machines in an ethernet network, or do the clients just choose when to go online?

  12. #12
    Join Date
    Aug 2004
    Posts
    28

    Exclamation hei thank you ...

    hei hei cruickshanks
    thanks y lot for your intrest.
    If I do it like that Every time I have to move the entire database and then the users can see all information , as my data is about cars , ti means every user can see all data even he is online or offline.
    But my bussiness case is to allow the user to see only a special car info, and work on it when he is offline, but when he is online he can have a look over all cars but can work on ( I mean change ) only a special car info.
    any way If I explained you correctly and If you understood correctly , what do you think the best way?
    Thank you and have fun there
    Kiran.
    "One who Gives when u ask is Ur MOTHER but, one who always tries to know your needs and Gives what ever one can Give for U is Ur FRIEND.( SO Try to BE and get such a friend GOOD LUCK MY FRIEND )"

  13. #13
    Join Date
    Sep 2004
    Posts
    161
    this solutions, i give you, are good only if your users dont make change in your base, otherwise is a little more complex, but the philo can be the same.

  14. #14
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Quote Originally Posted by kirankarnati
    hei hei cruickshanks
    thanks y lot for your intrest.
    If I do it like that Every time I have to move the entire database and then the users can see all information , as my data is about cars , ti means every user can see all data even he is online or offline.
    But my bussiness case is to allow the user to see only a special car info, and work on it when he is offline, but when he is online he can have a look over all cars but can work on ( I mean change ) only a special car info.
    any way If I explained you correctly and If you understood correctly , what do you think the best way?
    Thank you and have fun there
    Kiran.
    ok, i understand the part about the users only being allowed to see specific information, but if you have the interface installed on each of the clients machines, the permissions and other security measures that you have put in for the online database could also be applied for the offline database, so it doesn't matter if the user is online or not, they can only ever see the information that they have access to.

    What i meant by having a "copy" of the online database is that it would allow them to go about their work and then the database would then be updated every time that user went online (in this case when each user goes on line).

    What i think would be the hard thing to do is to get access to switch from online to offline mode. i.e. it would have to constantly poll the connection status in the background until the connection went online, but it would also have to automatically update the backend without the client telling it to do so, and once that's done, keep updating both the online and each clients offline database until the user is offline again. then it would go through the same process again.

    If all of the clients computers are connected to a LAN, then this would become much simpler, as that way there would only be two databases to worry about (the WAN one and the LAN one). Each client could just connect to the LAN database when offline, and the server would then publish the new data to the WAN database...

    Im not sure if im explaining this right, but i hope you understand what i mean.

  15. #15
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    the only other alternative is to put all of the updates into a frequently updated dummy database, and then upload all the new data that way.

Posting Permissions

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