How to distribute database when users are on standalone laptops & protect objects

[OneToOneTrainer]

Looking for suggestions on distributing a database when the users are in the field using a stand alone laptop.



They don't have access to Wi-Fi, network, or internet while working in the field.

And the users are "wanna be" access designers and try to make things "better".... We don't want them changing the access objects in any way. They should only be able to add or edit records.

They database is being totally updated, and we need a way to put the database on their individual laptops, but still get the updated information into the Master Database at the office.

My first thought is to split the database, but that appears to always have the users in a shared environment.

If we don't split the database, would could make the copies an .accde database to protect the objects, but then we need to deal with getting the new data added to the master database.

There is the option of using append queries to add the master, but how do we keep the new data easily available to append, yet allow the users to get to all the data if they need to query or do a report.

Any help or suggestions would be greatly appreciated!

Thank you!

[Dal Jeanis]

It's not quite a FE/BE situation. It's ALMOST a replication situation, but not quite. You can use classic replicas as a reference for what you're trying to achieve.

For convenience, we'll call the Master copy the BE, and we'll call the portable frontend a PFE, just so we mistake it for a normal frontend.

Think of it this way -

1) They need read-only copies of any reference data they must have, and they need updatable copies of everything else.

2) When they are not logged onto the network, any updates they make must be logged, so they can be applied later.

3) When they log onto the network, their copy of the references needs to be updated, and their update transactions need to be applied to the BE.

In concept, this can be achieved as easily as having two timestamps on each record, the BETimestamp and the PFETimestamp.

In the BE, the BETimestamp and the PFETimestamp are always equal. In the PFE, whenever a record is updated, the PFETimestamp is updated.

When the PFE logs onto the BE, updated records are transmitted, and conflicts are resolved. (If a record has been updated on the PFE, but the BE record has also been updated by someone else, then your system needs to report and/or resolve the issue.)

This is also where you resolve any primary key issues - a new record created on a PFE won't necessarily get the same autokey when it is added to the BE - some other PFE may have nabbed the number.

A superior option would include logging changes at the field level, so that your update-conflict resolution can deal with more of the details before calling for human intervention. But, that requires a lot more coding.

[OneToOneTrainer]

In concept, this can be achieved as easily as having two timestamps on each record, the BETimestamp and the PFETimestamp.

In the BE, the BETimestamp and the PFETimestamp are always equal. In the PFE, whenever a record is updated, the PFETimestamp is updated.

When the PFE logs onto the BE, updated records are transmitted, and conflicts are resolved. (If a record has been updated on the PFE, but the BE record has also been updated by someone else, then your system needs to report and/or resolve the issue.)

This is also where you resolve any primary key issues - a new record created on a PFE won't necessarily get the same autokey when it is added to the BE - some other PFE may have nabbed the number.

So this sounds like a good concept....

Question now would be HOW to implement this. I am not good at using VBA code yet. I have a very basic understanding of the structure of the language, but that's about it.

Would it be queries to handle this, or do we need to add fields to the tables?

Where does the a "timestamp" go?

Thanks!

[Dal Jeanis]

A timestamp is just a date/time field in each table. Let's say it was the tblStaff table, and the fields in the staff table happen to start with stf_. (Your naming conventions will obviously be different, but this is just an example.)

The name of the two timestamp fields might be stf_MasterDate and stf_LastUpdate. Or they might be stf_LastBEUpdate and stf_LastFEUpdate. They are type Date/Time. When a user updates a staff record, the form must also make sure to set stf_LastUpdate (or stf_LastFEUpdate) = to now(). This could be in the AfterUpdate event of each control on the form, which is a little cumbersome, or you can set an invisible field to do it when the user leaves a record that they have modified. Either way, the method you choose must become integrated into your entire architecture, because you have to do it the same way in every screen.

[OneToOneTrainer]

Ok... having trouble with getting the LastUpdate field to get a date in it.... have tried several ways, but it never gets the date there for us. We tried adding it as:
* form with =now() as default
* form with =now() as an event on After Update
* table with =now() as default
* form with =now() as an even on the Before Update

None of these methods even produce a date in the field either on the form or on the table

The table has the field, the form has the field... we can't get it to update at all!

[Dal Jeanis]

Please post the code you tried to use in BeforeUpdate. It should look like:

Me.MyLastUpdateControlName = Now()

[OneToOneTrainer]

The BeforeUpdate shows =now().

We tried typing in the code me.Sign_User_Timestamp = now() for the event, but it did nothing different.

It won't populate anything to the field on the form which is visible.

[Dal Jeanis]

1) Place the VBA code in the Form's BeforeUpdate event.
2) Make sure that the control on the form is bound to the underlying table field.
3) Change something (anything) on the record.
4) Move off the record you changed, to the next or previous record (or close the form, doesn't matter).
5) Go back to the same record.
6) The LastUpdate field should have been populated.

FYI - You may also need the code in the BeforeInsert event for new records.

[OneToOneTrainer]

Dal.... Ok that seems to be working if I add. However, we also need to have it update to a new time on a record that is changed.

So if the record already has a time stamp, but now it is something on the record changes, how to we get the time to update.

Guess I should have been a little more specific on that.

Thanks so much for the help on this.

[Dal Jeanis]

As long as the field on the form is bound to the underlying field in the record, those six steps cause the date/time to update at the moment that you try to leave a record that has been changed. I tested this on a fresh form on a junk database. There are no other steps needed.

Make sure the Last Updated field is type Date/Time, and that it is displaying down to the seconds. You may wish to wait a few seconds so that you can see the LastUpdate seconds are changing.