Work-offline feature?
- Thread starter wchelly
- Start date
- Local time
- Today, 16:05
- Joined
- Feb 19, 2002
- Messages
- 47,663
Not with a standard client/server database but I think this mode is available with a web app linked to sharepoint tables. You can't convert a client/server app to a web. You need to completely recreate it using macros instead of VBA. Only the data can be converted.
Rx_
Nothing In Moderation
- Local time
- Today, 14:05
- Joined
- Oct 22, 2009
- Messages
- 2,803
Pat is giving you some great advice. For 99% of the Access users out there, there is no replication since the .mdb database.
Before you read further, I suggest that you click his Thanks button.
This advice is not for a rank beginner. Warning: it contains graphic details about Replication.
It is possible to use an old .mdb for replication along-side (as the back-end ) Access 2010. That would not be my preference. But, people actually are doing that.
If your project is an absolutely "must do", here is my idea and history of replication. If it helps you understand the sheer complexity and avoid doing it in the first place; hopefully this post still helped you.
You didn't mention MS Access Version.
The older versions with a .mdb extension had a feature called Replication. Loved teaching that part of the access certified course and used it for several large applications. That said, there are a lot of rules to follow. Replication (or disconnected data) took a lot of planning. It doesn't just magically work to your benefit with the old Replication Wizard of Access 97.
As Ancient Access Lore goes, this feature is no longer supported since the .mdb extension. Some say it is because so few used it. Others say, it was the lousy documentation for a really robust feature (very true). Some say it caused many an Access Programmer to go crazy (also true in my case). I say. it was robbing Microsoft of a lot of SQL Server licenses. You would never believe some of the absolutely huge clients that made use of this feature license free. Monster Huge Access applications with replication that literally ruled our seas.
http://www.access-experts.com/default.aspx?selection=TutorialReplication
There is a lot on the subject of Replication. If I were tasked to do such a thing today, a re-visit of all the historical documentation and relics on how Replication worked would be a must.
For one of my long-forgotten project with VB6, the Access DAO and a SQL Server database (pre-internet - phone dialup) a team of about 30 programmers designed a custom replication tool for the old Mac Tools turck owners with PC and dial-up to SQL Server.
Basically, start with a Master DB. There will need to be fields in each and every table to determine if the data is dirty, version number, unique ID and more.
Any Replication (or disconnected data) must utilize a Conflict Resolution Manager in case the disconnected data was changed (dirty) while the master data was changed. Conflict Resolution is done on a record-by-record basis.
Access 2010 would basically require building all of this as a custom process.
http://support.microsoft.com/kb/182886#appliesto Here is the short list of design requirements - as questions.
If this is not enough complexity to encourage an alternative:
Here is the Access 2010 Tech Net reference to the custom code to modify and make your own custom Replication Conflict Manager.
http://support.microsoft.com/kb/158930
Used this years ago in Access 97 to create a very custom interface. It was great once it worked and passed Quality Assurance. ( I was QA manager).
Alternative:
During the last century (year 2000), the government agency I worked for needed to do on-site audits with disconnected data. The IT Staff wasn't all that <fill in the blank>. So, it was preferable to come up with a different option than Replication or networks.
My design was a system to upload all of the current tables to a laptop's local tables. It was basically a create table query of all the tables.
As the agent went on-site with a portable, they conducted observations, inspections, audits, and updates. None of this data entry was saved to those uploaded tables.
Instead, any changes / edits / additions were saved to a custom set of empty local tables that only held the changes / edits / additions along with a copy of the data that was changed, edited, added.
These could provide a custom Report of the changes - the report showed the old values and any changed (or added) values. The report could be saved to a data file and e-mailed. My choice was to save it as XML (for computers to read) and Excel (for humans to read).
Back at the main office - The e-mail was received - there was a custom tool to update the changes and additions back into the master database.
The custom tool became known to users as the Custom Conflict Resolution application.
So, basically the end result was somewhat the same. It still took considerable planning and a lot of resources to make it happen correctly.
Pats answer is exactly the right answer.
My "advice" might help provide some considerations to weigh the resources necessary should your requirement be absolutely necessary.
Regards
Before you read further, I suggest that you click his Thanks button.
This advice is not for a rank beginner. Warning: it contains graphic details about Replication.
It is possible to use an old .mdb for replication along-side (as the back-end ) Access 2010. That would not be my preference. But, people actually are doing that.
If your project is an absolutely "must do", here is my idea and history of replication. If it helps you understand the sheer complexity and avoid doing it in the first place; hopefully this post still helped you.
You didn't mention MS Access Version.
The older versions with a .mdb extension had a feature called Replication. Loved teaching that part of the access certified course and used it for several large applications. That said, there are a lot of rules to follow. Replication (or disconnected data) took a lot of planning. It doesn't just magically work to your benefit with the old Replication Wizard of Access 97.
As Ancient Access Lore goes, this feature is no longer supported since the .mdb extension. Some say it is because so few used it. Others say, it was the lousy documentation for a really robust feature (very true). Some say it caused many an Access Programmer to go crazy (also true in my case). I say. it was robbing Microsoft of a lot of SQL Server licenses. You would never believe some of the absolutely huge clients that made use of this feature license free. Monster Huge Access applications with replication that literally ruled our seas.
http://www.access-experts.com/default.aspx?selection=TutorialReplication
There is a lot on the subject of Replication. If I were tasked to do such a thing today, a re-visit of all the historical documentation and relics on how Replication worked would be a must.
For one of my long-forgotten project with VB6, the Access DAO and a SQL Server database (pre-internet - phone dialup) a team of about 30 programmers designed a custom replication tool for the old Mac Tools turck owners with PC and dial-up to SQL Server.
Basically, start with a Master DB. There will need to be fields in each and every table to determine if the data is dirty, version number, unique ID and more.
Any Replication (or disconnected data) must utilize a Conflict Resolution Manager in case the disconnected data was changed (dirty) while the master data was changed. Conflict Resolution is done on a record-by-record basis.
Access 2010 would basically require building all of this as a custom process.
http://support.microsoft.com/kb/182886#appliesto Here is the short list of design requirements - as questions.
If this is not enough complexity to encourage an alternative:
Here is the Access 2010 Tech Net reference to the custom code to modify and make your own custom Replication Conflict Manager.
http://support.microsoft.com/kb/158930
Used this years ago in Access 97 to create a very custom interface. It was great once it worked and passed Quality Assurance. ( I was QA manager).
Alternative:
During the last century (year 2000), the government agency I worked for needed to do on-site audits with disconnected data. The IT Staff wasn't all that <fill in the blank>. So, it was preferable to come up with a different option than Replication or networks.
My design was a system to upload all of the current tables to a laptop's local tables. It was basically a create table query of all the tables.
As the agent went on-site with a portable, they conducted observations, inspections, audits, and updates. None of this data entry was saved to those uploaded tables.
Instead, any changes / edits / additions were saved to a custom set of empty local tables that only held the changes / edits / additions along with a copy of the data that was changed, edited, added.
These could provide a custom Report of the changes - the report showed the old values and any changed (or added) values. The report could be saved to a data file and e-mailed. My choice was to save it as XML (for computers to read) and Excel (for humans to read).
Back at the main office - The e-mail was received - there was a custom tool to update the changes and additions back into the master database.
The custom tool became known to users as the Custom Conflict Resolution application.
So, basically the end result was somewhat the same. It still took considerable planning and a lot of resources to make it happen correctly.
Pats answer is exactly the right answer.
My "advice" might help provide some considerations to weigh the resources necessary should your requirement be absolutely necessary.
Regards
NigelShaw
Registered User.
- Local time
- Today, 21:05
- Joined
- Jan 11, 2008
- Messages
- 1,575
Hi
Ive done it before and it wasn't that bad............
My setup
I made a button to click offline. This toggled my back end. I setup two routines in vba to connect to different back ends. I set a field in a settings table to tell the FE which BE to look at.
So,
When I went 'offline', a routine would collect a predetermined history of records. I made 2 options-
Option 1 - how many projects
Option 2 - date period
I used date period. I would collect the last 2 months projects and copy them to a local offline BE. They would have 2 additional fields-
FldCopiedFromBE
FldCopiedRevised
I could then look back at previous projects 'offline' without too much difficulty. When I made a new project record, the FldCopiedFromBE would NOT be flagged.
When I needed to update an existing project that had been copied local, the FldCopiedRevised WOULD get flagged.
Note:
Only the project creator can edit a project to prevent duplications. For example, if 2 users were offline, they can only edit their own offline projects. I couldn't edit anyone else's.
When I went back online, the offline projects would be queried into 2 sections
New projects
Revised projects
The revised projects would be copied back as new records to the main BE with an updated revision number to prevent over writing.
The new projects would be written back as new records into the main BE.
To prevent duplication of project numbers, I set up a routine that temporarily allocated a forward slot of numbers. Lets say my last project was PRJ-NS-26477
I would allocates temp number of 26477 + 5 and save it to the main BE. Any live projects would be after this number. This would be updated back to normal on the sync.
There is a lot more in depth behind the scenes stuff but, it can be one quite successfully without too much issue........ Mine works for me and my team quite well and haven't hit any major snags in the last 12 months.
Thanks
Nigel
Ive done it before and it wasn't that bad............
My setup
I made a button to click offline. This toggled my back end. I setup two routines in vba to connect to different back ends. I set a field in a settings table to tell the FE which BE to look at.
So,
When I went 'offline', a routine would collect a predetermined history of records. I made 2 options-
Option 1 - how many projects
Option 2 - date period
I used date period. I would collect the last 2 months projects and copy them to a local offline BE. They would have 2 additional fields-
FldCopiedFromBE
FldCopiedRevised
I could then look back at previous projects 'offline' without too much difficulty. When I made a new project record, the FldCopiedFromBE would NOT be flagged.
When I needed to update an existing project that had been copied local, the FldCopiedRevised WOULD get flagged.
Note:
Only the project creator can edit a project to prevent duplications. For example, if 2 users were offline, they can only edit their own offline projects. I couldn't edit anyone else's.
When I went back online, the offline projects would be queried into 2 sections
New projects
Revised projects
The revised projects would be copied back as new records to the main BE with an updated revision number to prevent over writing.
The new projects would be written back as new records into the main BE.
To prevent duplication of project numbers, I set up a routine that temporarily allocated a forward slot of numbers. Lets say my last project was PRJ-NS-26477
I would allocates temp number of 26477 + 5 and save it to the main BE. Any live projects would be after this number. This would be updated back to normal on the sync.
There is a lot more in depth behind the scenes stuff but, it can be one quite successfully without too much issue........ Mine works for me and my team quite well and haven't hit any major snags in the last 12 months.
Thanks
Nigel
Rx_
Nothing In Moderation
- Local time
- Today, 14:05
- Joined
- Oct 22, 2009
- Messages
- 2,803
It sounds like a well-planned process! Planning is the key.
A way to upload the disconnected data (2nd backend), a way to identify the changes when it re-joins the connected data. Also, a method for Conflict Resolution Management.
I also realized that we did not cover one option. Take a Read-Only copy (disconnected), let the user take notes and update when back in the office. Another project used this method and is tool very little planning or maintenance.
A way to upload the disconnected data (2nd backend), a way to identify the changes when it re-joins the connected data. Also, a method for Conflict Resolution Management.
I also realized that we did not cover one option. Take a Read-Only copy (disconnected), let the user take notes and update when back in the office. Another project used this method and is tool very little planning or maintenance.
Thank you for all of the above. The thought occurred to me when I noticed the "Work Off-Line with Sharepoint". My data base tables are not lists in sharepoint, but the front-end is on a Sharepoint site and my tables update a sharepoint calendar. I first thought that this button would allow folks to work-off-line but I quickly realized that this is only a feature for sharepoint and not for Access Tables.
I've known about replication for some-time, but never really wanted to try to manage that but perhaps now is the time. It sounds like others have done this without too much headache and had success. I could probably do it too.
I like the idea of just taking a "stand-alone" copy when the user leaves the office, however, I feel folks would need to have additional MS access skills to do this so that they know how to import tables as I may not always be available to do it for them. Perhaps I could configure a button do do this for them, I don't know. But then they would have to enter the data again when they come back, or someone would have to append a copy of those records, which I don't feel comfortable allowing the users to do themselves. Plus we have one user who works from home exclusively, so her records would not be updated using this method.
I've known about replication for some-time, but never really wanted to try to manage that but perhaps now is the time. It sounds like others have done this without too much headache and had success. I could probably do it too.
I like the idea of just taking a "stand-alone" copy when the user leaves the office, however, I feel folks would need to have additional MS access skills to do this so that they know how to import tables as I may not always be available to do it for them. Perhaps I could configure a button do do this for them, I don't know. But then they would have to enter the data again when they come back, or someone would have to append a copy of those records, which I don't feel comfortable allowing the users to do themselves. Plus we have one user who works from home exclusively, so her records would not be updated using this method.
Similar threads
