I finished setting up the linked tables databases. So, like I told you earlier, I put the originalDB (the one with tables only) in server and linkedDB (the one with linked tables and forms etc) in local machines. I tested it, but I found a problem. When I test it, I use 2 local machines, both accessing the same table in originalDB. In the form to access the table, I use RecordLock-EditedRecord. When I try to edit the same or record or different record in that table, sometimes the program hangs/clashes. Do you have any idea why this occurs ? I thought by setting it to EditedRecord, the record currently edited will be locked, not crashed. Thanks.
What kind of error do you have!? or , does any error message appears?
You have done the right thing, setting to edited record, so when a user starts editing the other must wait to edit it, if he wants.
I don't know for shore what is happening, maybe the timer interval for ODBC updating!???
It does not give me any error, it just freezes, so when I click on Task Manager, it says, not responding. Once one of the users quit, then it becomes fine. But, that's not what we want. It should support online multi-users. Do I miss anything ?
How much memory has the server!?
Because i have a database that i've made and it works perfectly. I must say that the server has 1G of memory and Windows 2000 and SQL Server, but anyway the database is not in SQL Server, but uses the recent ODBC drivers.
Thanks again. I test it again, it actually did not crash/hang, it just looks like freezing, and after maybe 10 seconds, it works again. I am just wondering why it takes so long. I think my server is pretty fast and has a lot of memory. If the users are in the same record, it does not freeze, because it just locks the record. Should I just lock AllRecords instead of EditedRecord ?
Don't worry, WC is coming, should watch plenty of games
I think i know why it freezes. Does your network connected with T-cables!? or with a cable that looks like a phone cable!?
Because T-cables are slower in sending/receiving data. it's pretty normal that the server take a wille to right to the ldb file the information about the user that are changing the record. My databse is connected in to ways, one with the older cables (T ones) and other with the new ones. T-cables can aford much longer distances that those i'm talking, that's why they are slower, in my company it is needed because that user is well distanced from the server. When, and i've experienced that, the user changes a record, (my DB have no restrictions to record locking, it's linked with optimistic locking), takes a little while to their database respond. But if i change a record, rapidly i receive a message that i'm begining to change a record that other user...., and no delays in response are feeling. Because of distances and cables the time to receive data or send is very different.
If your database is for many users, why don't you replicate it!?, it's easy and better, because the data stills in the tables of the end users database replica and only is transmited to the server, when the user choose to synchronize. This means that the time and resources of network reduces a lot. Plus, if you want to add one more form or table, it's only need to make them in the master replica, the that stays in the server, choose synchronize and all databases are updated.
I figured out the problem, you were right, I was using slower server previously, now I am using the fast one and it works fine I still use lock-EditedRecord btw. I have one question for you. With EditedRecord, when I edit record 1 for example, other user cannot edit record 1. But if my form points/just moves to record 1 but I don't edit it, other user can edit it. If I am editing record 1 and the other user in record 2 for example, when I finish editing record 1 and move to record 3 for example, then the other user moves to record 1, he will see the changes I made to record 1. Are these how it should work ?
I also try to add new record or delete a record. When I add a new record and move to a different record, the other user cannot see the new record, he has to close the form and open it again. When I delete a record, the other user can see that record is being deleted. How do I refresh so other user can see the new record for example without having to close and open the form again ? I also never get the message "you are beginning etc", it that because I use EditedRecord instead of NoLock ? What happens if you use NoLock and multi-users edit the same record ? Which modification is going to be saved in the server ?
My user is going to be less than 5, so I thought I don't need to use replica, and I don't exactly know how replica works. In replica, if multi-users edit the same record and you synchronize it, which modification is saved in the server ? You say if you want to modify a form using replica, we can just modify the master replica ? So what is actually saved in the local machine ?
Yes, that's how it should work, when you edit a record, if other user moves back to see the record that he have just entered and you edited, he will see the changes.
Now, to refresh forms, you can use code for example in the add button:
after the code line thet makes the table move to new record.
This will make the form to recollect the data. in subforms based on querys or tables it's usual to use [Subform Name].Requery, just for the query of the subform show you the new data.
Replicate is the best way to work with 3, 4, 5 users, because it saves time and network resources.
Here's how a replica work:
-Create a database, as if it was only for 1 user.
-Chose replicate, this will create 2 databases, one is the master replica and the other a replica or a copy of master replica, The master replica will be the same name of the original database, the other will be an extended name unless you chose a name for it, (When creating the replica chose the directory you want for it, because it will be the front end database for the user).
-Open the master replica and replicate it again, chose another computer directory, do this for each user
-If a user is entering data, he never sees the data of other users, until he synchronizes with them. Synchronization only happens with the database you chose for, in other words, you can not synchronize with 3 databases at same time, you have to synchronize with each database. Of course you can set statments in code to make synch with all, by putting in a button or OnClose event:
Dim dbsMilan As Database
Set dbsMilan = OpenDatabase("C:\DB Directory\Milan.mdb")
' Sends/receives changes made in each replica to the other.
dbsMilan.Synchronize "\\server name\DB Dir\Milan_Rep.mdb", dbRepImpExpChanges
'Sends/receives changes to other database
dbsMilan.Synchronize "\\computer user\DB Dir\Milan_Rep.mdb", dbRepImpExpChanges
-For example, if you synchronize with a user and not with the server only you and the user will exchange data, that's why, basicly you don't need to have a database in the server, just keep for yourself the master replica and put the replicas of your master replica for the rest of the users.
-This reduces time in network, because the query don't need to ask for data to the server, they have their data on the local machine, and if is needed, just synchronize and new data is added from others users.
The optimistic locking, or no Lock, is bad for your purpose, because when a user is editing the same record as you, you receive that message that i said. and it's very anoying work in a database and constanctly receives that. Because it is very probable that you are adding a new record, and see that other user just entered a wrong record, you have the intuition of edit it...
With the button of adding a new record or moving to different record, I actually just use the one from Access (navigation button), so I don't create my own code. What should I do to make it automate to recalc then (user does not have to click on anything to recalc) ? I don't have subform. What's the difference between ReCalc and Requery ? I know requery will cause to jump to the first record, it's annoying. My form is based on query. Why do you say that the code "Me.Form.ReCalc" to be put in the add button? Let's say I am adding a new record, the other user does not have to click on 'add new record' if he does not want to add new record, he just wants to see if I am adding a new record. Should the code be put in 'move to record (previous or next)' button instead ? I am not sure myself.
Replication seems interesting, I will try it. With the linked-tables I am currently using, in what situation then it is used ?
So you have a normal form, ok, type the code in the last field on AfterUpdate event, so that, when the user enters the the last data for the record the form recalcs. Note: If your using a form that reads all records, after the the recalc statment, use Docmd.GoTorecord, ,acLast
so the user can see the last posicion in the table, and ready to add a new record.
Personaly, i use dataentry property set to yes, because the form doesn't have to read all records each time it opens, recalcs or requery,means that, when you have too many records, you spend the same time watching the form opens as if it had 1 record and if you want to search for data in the form, you set the property to no in code, just like this:
Create a button to look up, using the button's wizard, then open the envent, and before the statments that access created for looking up, put: Me.DataEntry=False and after this statment put Me.Form.Recalc
so that the form could read all records and recalc, and be ready to search.
For automating, without AfterUpdate or OnClick events you have to use OnTimer event, but this only spend more memory to your computer, and you do not have control on recalculation.
Requery is the same thing that you open a query, close it and then open it again. Recalc is similar, but saves your record.
The users can't see if you are adding a new record, only after you enter it.
Note About Replication:
Do not Link tables to another database it's not necessary, just create one database with local tables and replicate.
Have you take on a look on the forms of the example i sent!?
See that in the forms of Clients Personal data, there are no buttons only labels, but they work like buttons, take a look at the code. There are three events for each label:
1- OnMouseMove, makes the label raise
2- OnMouseDown, makes the label sunk
3- OnMouseUp, makes the label goto normal and move to a new record
What if I do this ? In the form's On Current event, I put Me.Form.Calc code. So, basically, using this code will show if other users add new record(s). If it does then:
User 1 : let's say his form shows 12 records and then he is entering a new record (his form shows 13 records now) and then he moves to record 8.
User 2 : his form has 12 records, and he is in record 7 now. So when he moves to another record, his form should show 13 records because On Current event fires.
Does this sound right ?
With the idea of syncronization:
database 1 : has a record ClientID(001) and ClientName(Miguel)
database 2 : has a record ClientID(001) and ClientName(milan)
What happens when database 1 and 2 are synchronized since the records has the same ClientID ?