Multi user network & local database

[adh123]

With the help of suggestions from users on here we have been running local copies of an access CRM system for the past year or so.

Now that I have a bit of time to look at this again I am trying to identify a way of setting up a single database with multi user access on the local network.
A quick google search and I think I can get my head around how to do this.

The next stage is the tricky bit which if anyone has any suggestions would be great...

Some staff work remotely a couple of days a week. Due to security requirements remote workers cannot connect to the work network off site.
In a previous employment the system in place worked like this:

Database stored on server, a copy of the database stored locally.
User logs in and the application updates the local copy.
System in the background updates at regular intervals by updating the database on the server and then refreshing local copy to match server copy (so input from other users is also synced).

Hopefully makes sense?! Any suggestions as to where to start would be fantastic

 

[CJ_London]

depends which version of access you are using. If it is old (2003 and earlier) you could use replication. This was deprecated in 2007 so no real alternative but to write your own along the lines of your previous employment.

It would involve having numerous queries and needing to have replicationID and timestamp fields in each table. Also records would never be deleted from either side, you would used a 'deleted' flag to filter them out and the autonumber field needs to be set to random. For each table you would need to check for

records in local, not on server
records on server, not in local
records in both server and local where fields have changed and local has the later timestamp
records in both server and local where fields have changed and server has the later timestamp

 

[Ranman256]

no. put the full db on the server folder.
split the database. The backend (tables) remains on the server.
The Front End (queries/forms) is given to EVERY user. Each user gets their own copy.
No updates needed.

Now when you make changes to the FE, (new queries,forms,or other changes)
THEN, you must deliver the FE to all users again.
The backend remains alone.
If you make changes to the BE tables, then open the FE, relink all tables,
then either ,
have a way for end users to relink all tables in their copy
or
relink the FE master copy and distribute FE to all users again.

 

[adh123]

no. put the full db on the server folder.
split the database. The backend (tables) remains on the server.
The Front End (queries/forms) is given to EVERY user. Each user gets their own copy.
No updates needed.

Now when you make changes to the FE, (new queries,forms,or other changes)
THEN, you must deliver the FE to all users again.
The backend remains alone.
If you make changes to the BE tables, then open the FE, relink all tables,
then either ,
have a way for end users to relink all tables in their copy
or
relink the FE master copy and distribute FE to all users again.

The database has already been split like this on each local install. FE contains forms and queries only, BE is database.
When a user is working remotely with your suggestion the remote users would not be able to access the database?

 

[adh123]

depends which version of access you are using. If it is old (2003 and earlier) you could use replication. This was deprecated in 2007 so no real alternative but to write your own along the lines of your previous employment.

It would involve having numerous queries and needing to have replicationID and timestamp fields in each table. Also records would never be deleted from either side, you would used a 'deleted' flag to filter them out and the autonumber field needs to be set to random. For each table you would need to check for

records in local, not on server
records on server, not in local
records in both server and local where fields have changed and local has the later timestamp
records in both server and local where fields have changed and server has the later timestamp

Thanks CJ, unfortunately it is in 2010.

Currently users are not allowed to delete records anyway (as for example a customer who leaves may come back so we would need to keep their history) and so already have a delete flag.

 

[CJ_London]

unfortunately it is in 2010.

so you would do this

It would involve having numerous queries and needing to have replicationID and timestamp fields in each table. Also records would never be deleted from either side, you would used a 'deleted' flag to filter them out and the autonumber field needs to be set to random. For each table you would need to check for

records in local, not on server
records on server, not in local
records in both server and local where fields have changed and local has the later timestamp
records in both server and local where fields have changed and server has the later timestamp

 

[adh123]

Thanks, will see how I get on!