this is the first time i am using access and vba to develop an application. any comments or suggesstions will be truely appreciated.
I have a small ms access 2000 database with only one table and 2 forms and 2 reports. and there will only be 2 users accessing it.
I dont know wheter I am doing it the right way but this is what i am doing.
user 1 access the database with form 1.
user 2 access the database with form 2.
both these forms read and write information to one common table i.e table 1.
the mdb file is on a desktop computer which is accessed by user1.
the same file is accessed by user 2 from a laptop wirelessly.(i have shared the drive of the desktop where the mdb file is located)
sometimes the records added by user2 does not appear in the table 1 atall.
Am I doing it the right way? If there is a better way to enable multiuser please suggest (which i think there should be).
Or is it possible that the database is at one place and the clients access it using just the forms? without getting vb in the picture to develop the front end?
Or is it possible that the database is at one place and the clients access it using just the forms?
is exactly it.
split your database into the user-interface front-end and the data back-end. menu: Tools ¦ Database Utilities ¦ Database Splitter will do a reasonable job. place the backend on a file-server somewhere that all users can access.
without getting vb in the picture to develop the front end?
is unlikely unless you have real simple data and are happy with a limited interface. you will most likely end up with vb behind your interface anyway.
thank you for your quick response!
the forms have a lot of code that is manipulated using vba..but what i meant was that I did not want to start over by just designing the front end forms in VB 6.0 and connecting it to the backend database.
I wasnt aware of the databse splitting option..looks promising!
but does it require that the database should be on a file server? will it be ok if I have have the backend on the network share folder the way i have it now?
but i wonder whats wrong wiht my current setup because I have a button "requery" on my user1 form which is suppose to get the latest info on the database.
but sometimes the data entered by user2 absolutely vanishes. plus user 2 does not enter the data continuously..when form 2 is opend by user 2 he just enters the data and after pressing the finish button on that form the form closes. and the form 2 is opened just say about 6-7 times in a day.
anything that can act as a dumb file server can be the "server", including the user1 client. both users need ms-access installed and their own copy of your front-end.
your forms & code (& data) will not be harmed by the split (but backup first).
if you still have vanishing records after the split, check out record locking ("optimistic", "pessimistic") in A-help. also try to discover what user1 is doing when user2 loses entries: working in the database? ...in a record close to the user2 record? ...in the same record as user2
if updating the records still becomes a problem - you may look into replication but I only suggest this for power or advanced users - but it is a solution if the laptop user is "disconnected " from the server data source but needs to view and update data off site.