Providing the laptops have the same version of Access and are able to link to the the server i would create a back end of the Tables , which would be placed on the server and link the tables to you front end ,and give each manager a copy . That is all very simple but there are many pit falls and you would be best advised to search this forum for exampes and further advise.
Ok.
I made a copy of datebase on one of the laptops and separated tables from forms and queries. Now I have linked tables on server.
But, i need same tables on the laptop because men on the field wil not have connection with the server and they will enter some new data in the tables.
I want to write some code or make a query that will: update server table with new rows that is collected on the field and update laptop table with rows which other laptops collected and copied to the server.
No sure what you mean by you have the tables linked on the server. If you have the tables on a back end on the server the next step to establish a link from the manager’s pc to the backend on the server. I will try and guide you through the sequence. If you already know this my apologises
1. Delete the tables in the Db “front End “situated on the laptop that is going to be linked to the backend on the server.
With the front end opened, Select F11, next File, then Get External Data: Link Tables. You then should be given the option to search for the Database on the Server. Once found, highlight it and click the Link option button.The tables available on you backend will be listed. Using the shift key highlight the tables you require to be linked. Select OK. You should now be linked.
The normal practice is to have a Master copy of the front end which is the Admin copy and you then copy to the managers.
Once this link has been established you will need to consider what locking you require to avoid manages editing the same record.
There are othe considerations but suggest you tread one step at a time
I used to add a couple of fields to my tables to make this sort of interchange easier:
1) Datestamp. A bit of code in the Form_BeforeUpdate events to note when changes were made to records.
Code:
Form_BeforeUpdate(Cancel as Integer)
Me!dateStamp = Now()
End Sub
2) A Deleted flag (Yes /No field) represented on the form by a checkbox. If I physically deleted a record from a table there was always a chance that I could re-instate the record by accident from somewhere else. With the flag, the latest record (with the deleted flag set) wins.
3) Add something to the record that identified the computer that created the record. In my case the range of IDs generated was decided by what building the database was in. Building 1 generated IDs 10,000,000 - 19,999,999. Building 2 generated IDs 20,000,000 - 29,999,999.
In your situation it might be a matter of using a simple incrementing Long Integer for the ID and a second Integer to represent the computer.
Instead of having a single autonumber generated ID (Long Integer), to uniquely identify the record, you would have a pair of fields; ID (incrementing Long Integer) and compId (Integer). When the person uses the database for the first time it could prompt them for a number, just assign them each a number between 1 and 32,767. This could be achieved as follows:
Code:
Public Function getComputerId() AS Integer
getComputerId = GetSetting("MsAccess","Computer","compId",0) ' Get a saved value from the registry.
If getComputerId = 0 Then ' A computer Id has not yet been entered
getComputerID = InputBox("Please enter your Computer Id") ' Give each computer a number
SaveSetting "MsAccess","Computer","compId", getComputerId ' Save the value to the registry.
Endif
End Function
Private Sub Form_BeforeUpdate(Cancel as Integer)
If Val(Me!ID & vbNullString) = 0 Then
Me!compId = getComputerId()
Me!ID = Nz(DMax("ID", "tableName", "compID = " & Me!compId), 0) + 1
End If
Me!dateStamp = Now()
End Sub
With this setup you could bring any two sets of data together, match the records by ID and compID, and know which was the latest record by using dateStamp.
This is probably overkill for what you want but it may be of use to someone.
No sure what you mean by you have the tables linked on the server. If you have the tables on a back end on the server the next step to establish a link from the manager’s pc to the backend on the server. I will try and guide you through the sequence. If you already know this my apologises
1. Delete the tables in the Db “front End “situated on the laptop that is going to be linked to the backend on the server.
With the front end opened, Select F11, next File, then Get External Data: Link Tables. You then should be given the option to search for the Database on the Server. Once found, highlight it and click the Link option button.The tables available on you backend will be listed. Using the shift key highlight the tables you require to be linked. Select OK. You should now be linked.
The normal practice is to have a Master copy of the front end which is the Admin copy and you then copy to the managers.
Once this link has been established you will need to consider what locking you require to avoid manages editing the same record.
There are othe considerations but suggest you tread one step at a time
I created front end database on laptop. It does not have tables on it. It only has links to the server tables. When the laptop isnt conected to the server i cant use the tables. (There are small arows next to the table names in the navigation pane).
Tried to create append query. But there is a problem:
Tables Customers and Visit are in relationship. Tbl Visit has Customer ID field.
Append query copies data but Customer ID key is changed.. When the data is copied Customer ID is not 1005 for example, but 23(next available ID in the server Customer tbl)
I tried something similar, but i need unique Customer ID in the Office Customer tbl.
There are 5 laptops in the field.. and i dont have an idea how to make it work..
Data must be copied from laptop, added to office customer tbl and then completely rewrited on laptop with new tbl. Because there will be changes made by other laptops.
I moved data around, in both directions, between over 30 separate databases using a similar method to my example.
I quickly found that the autonumber wouldn't do what I wanted it to do, so I needed to take control and create my own Long Integer keys.
These were based on what building / computer was running the database. Let's call it, for the sake of argument, an instanceId.
I did it using a counter table but, looking back, I could have done it easier by using a bit of code like this.
Code:
Public Function nextId(byval tableName as string, byval instanceId as String)
Dim strMinId as String, strMaxId as string
strMinId = instanceId & "0000000"
strMaxId = instanceId & "9999999"
nextId = Nz(DMax("ID", tableName, "ID BETWEEN " & Val(strMinId) & " AND " & Val(strMaxId)), 0)
If nextId = 0 Then nextId = Val(strMinId)
nextId = nextId + 1
End Function
This would create a simple incrementing Long Integer, like an autonumber, but within a range of xxx0,000,000 - xxx9,999,999 where xxx (up to 214) would be the database instance.
