Offline mode for multiuser Database application (2 Viewers)

[fjell]

Good morning everyone. I am fairly new to working with microsoft access and only used it during my internship so far. my task is to develop a tool that will help to electronically collect data from some of our operating machines. so basically what is happening is this. in the future the operator will no longer fill out a paper sheet in order to track the Manufacturing process but will just enterthe data into the created access form which will then be uploaded to an excel sheet that is saved on sharepoint. now here is my Problem. It might happen that due to some problems the interent connection on our shopfloor will break down. i have come to notice that when this happens so access and sharepoint get disconnected i wount be able to keep working with my application. my question is this now. is it possible to add some sort of offline working mode? I thought about kind of having a local back end that will for one first step save the data locally then on a timer based event checks if there is a stable wifi connection and copys the data into my linked sharepoint table. but i dont exacly know how this would work.\ if you have any experience regarding this problem some hints would be most apppreciated for i kind of dont know hoe to pursuit from this point on kind Regards Fjell

 

[theDBguy]

Hi. Welcome to AWF!

If you're using SharePoint as a backend to an Access application, it should already come with an "offline" mode. When there is no Internet connection, what happens if you continue to add records in Access? As far as I know, it should continue to accept that and simply send it to SharePoint when the connection returns.

 

[CJ_London]

What is the benefit of using access? Why not just create an excel form and communicate directly with sharepoint?

 

[Gasman]

Crossposted (twice) at https://www.accessforums.net/showthread.php?t=88435

 

[GPGeorge]

Good morning everyone. I am fairly new to working with microsoft access and only used it during my internship so far. my task is to develop a tool that will help to electronically collect data from some of our operating machines. so basically what is happening is this. in the future the operator will no longer fill out a paper sheet in order to track the Manufacturing process but will just enterthe data into the created access form which will then be uploaded to an excel sheet that is saved on sharepoint. now here is my Problem. It might happen that due to some problems the interent connection on our shopfloor will break down. i have come to notice that when this happens so access and sharepoint get disconnected i wount be able to keep working with my application. my question is this now. is it possible to add some sort of offline working mode? I thought about kind of having a local back end that will for one first step save the data locally then on a timer based event checks if there is a stable wifi connection and copys the data into my linked sharepoint table. but i dont exacly know how this would work.\ if you have any experience regarding this problem some hints would be most apppreciated for i kind of dont know hoe to pursuit from this point on kind Regards Fjell

Perhaps there's a better way to get to the desired end result.

First, Excel workbooks are probably the last choice one would consider for permanent storage of data used for tracking purposes. As a matter of fact, before considering that option, one would need to provide a convincing case for avoiding all other possible solutions. "Everyone is familiar with Excel" is not a convincing argument.

Second, SharePoint does indeed support automatic synching when using an Access interface with SharePoint lists. So do Dataverse tables.

Third, there is another significant factor to be considered; the difference between a "Kiosk" location for a database interface, and a "Mobile" location for that interface. In a Kiosk interface design, you bring data to the location where the interface sits. In a mobile interface design, the user carries the interface to the data and enters it there. You mention a shop floor. I can see that being either a kiosk or a mobile environment, depending on how the work gets down. When you further mention wireless, it also suggests you might need a mobile component to the database to support remote locations at different places in that shop.

You might invest an hour or so in this video to get an idea about alternative ways to think about implementing a hybrid solution in which your users combine the power and scope of a kiosk interface with the remote, mobile flexibility of a mobile interface. Both of which can take advantage of the auto-synch capabilities of either SharePoint or Dataverse.

And again, it's not clear how exporting data to Excel worksheets adds any value to the proposed solution.

 

[Pat Hartman]

SharePoint has a lot of cons but it's one pro is that it can support off line data entry which for remote users or a location with a flaky LAN can be a benefit.

So, as George pointed out, you need to look at the two styles of connection. A permanent LAN connected PC, tablet, or Laptop; or a mobile device such as a phone or tablet or laptop that is carried around and connects via the internet.

If the shopfloor already has workstations whether they be PC or tablet, you should plan on using those since they will have LAN connections and I would bypass SharePoint entirely.

 

[fjell]

Welcome, thank you for your answers so far.
Here is a more detailed description of my project, maybe I did not focus enough on it.
We have several machine locations on our shop floor (for example, friction welders, hot crimp stations, etc.).
Currently, these stations use a paper sheet to track their progress for each work order identified by a unique order number.
This sheet contains several characteristics that the operator must test at a predetermined interval to ensure that the parts meet the specifications.

Now the problem is this.

1. It can happen that a part does not meet the specifications, but for some reason the operator does not recognize it as such and continues working.
2. the wrong components for the part are delivered to the station and the operator simply builds a part with the wrong specifications.
3. not every part can be produced on every friction welder, which can cause problems.

To prevent this, I have programmed a form that represents the inspection record. I have a table that holds the parts with all the necessary information. (length, length after welding, sub-parts, etc.).

When the operator enters the data, the system checks against the data in the table and makes sure the part meets the specifications.
Back to my problem

I have seen that there is an offline mode offered by SharePoint, but my problem is rather that it can happen that the computer simply loses any connection to the Internet due to lack of a bad connection. Unfortunately, there is no LAN connection to the kiosk station where the operators enter the data. When I tried to enter data into my form and then just disconnected my computer from any internet connection I got an error saying "Access cannot connect to SharePoint list" which makes sense since I just turned off the internet. also I came to notice that the SharePoint connection requires quiet a good internet connection as otherwise the working speed suffers severely.

What I would like to have is, and sorry if I use the wrong terms to describe it properly, I am still learning, almost like a local backend that stores the data that is typed in and also the tables that hold the data that is necessary to complete the compare operations.

then every for instance 15minutes i want the computer to check if its connected to the internet. if yes, upload the data to a sharepoint list (i dont necessairily use sharpoint, that was just one of the first online sharing methods i came across.) if it is not, we just dont upload the data and wait 15minutes.



The data entry should not be too frequent, maybe 7 entries per hour. so we might only need to upload like a few records each time.

The excel sheet is just used to have a nice tool to look up the data because it offers creat filter capabilities.



Thanks again for your help, it's much appreciated.
Regards.
Fjell

 

[GPGeorge]

Welcome, thank you for your answers so far.

Spoiler: etc

Here is a more detailed description of my project, maybe I did not focus enough on it.
We have several machine locations on our shop floor (for example, friction welders, hot crimp stations, etc.).
Currently, these stations use a paper sheet to track their progress for each work order identified by a unique order number.
This sheet contains several characteristics that the operator must test at a predetermined interval to ensure that the parts meet the specifications.

Now the problem is this.

1. It can happen that a part does not meet the specifications, but for some reason the operator does not recognize it as such and continues working.
2. the wrong components for the part are delivered to the station and the operator simply builds a part with the wrong specifications.
3. not every part can be produced on every friction welder, which can cause problems.

To prevent this, I have programmed a form that represents the inspection record. I have a table that holds the parts with all the necessary information. (length, length after welding, sub-parts, etc.).

When the operator enters the data, the system checks against the data in the table and makes sure the part meets the specifications.
Back to my problem

I have seen that there is an offline mode offered by SharePoint, but my problem is rather that it can happen that the computer simply loses any connection to the Internet due to lack of a bad connection. Unfortunately, there is no LAN connection to the kiosk station where the operators enter the data. When I tried to enter data into my form and then just disconnected my computer from any internet connection I got an error saying "Access cannot connect to SharePoint list" which makes sense since I just turned off the internet. also I came to notice that the SharePoint connection requires quiet a good internet connection as otherwise the working speed suffers severely.

What I would like to have is, and sorry if I use the wrong terms to describe it properly, I am still learning, almost like a local backend that stores the data that is typed in and also the tables that hold the data that is necessary to complete the compare operations.

then every for instance 15minutes i want the computer to check if its connected to the internet. if yes, upload the data to a sharepoint list (i dont necessairily use sharpoint, that was just one of the first online sharing methods i came across.) if it is not, we just dont upload the data and wait 15minutes.



The data entry should not be too frequent, maybe 7 entries per hour. so we might only need to upload like a few records each time.

The excel sheet is just used to have a nice tool to look up the data because it offers creat filter capabilities.

Thanks again for your help, it's much appreciated.
Regards.
Fjell

Thanks for the clarifications. I see that the situation is less complex than I had surmised.

SharePoint DOES indeed support offline synching. In earlier versions, IIRC, it was automatic. As soon as a valid connection was restored, the synch began. I believe in more recent versions, though, you may have to request the resynch manually.

Click Synchronize and Access reconnects to the linked SharePoint lists and then tries to merge any data changes.


This article describes it in some detail.

It is possible to export data from the Access application into Excel Templates that can then be printed for use by the various processors. On the other hand, Access reports could also be printed for that purpose. It would depend, in part, on whether the printed version would require image or other features that might be easier to produce in an Excel worksheet. I've done it both ways for clients in the past.

 

[fjell]

Hey Georg, thanks for your quick reply.

I when i suddenly lose internet connection or rather when i turn it off i still cannot keep working with my database. and get the error message cannot connect to linked tables. maybe i understood the article wrong but isnt access supposed to enter offline mode automatically? do i have to activate this automation somehow?

Thanks in advance, I look forward to your reply.

Fjell

 

[The_Doc_Man]

This is more complex in one way but not so bad in other ways. You already expect to have bad connections. (i.e. when it happens, it is not "news" - to you.) Make a data gathering app that can do simple checking that doesn't require it to be connected to anything. Just have a stand-alone gathering app. Have it include a function to create data export files on demand, and export them when you are connected. Have your central data system have a function to import these files, treating them as transactions.

If your "gatherer" doesn't expect to be connected in the first place, loss of connection doesn't matter to it. And that export file, being done on demand, should be OK since you know when you connected and when you disconnected. The import operation would also be local.

THIS IS A CLUNKY SUGGESTION so it wouldn't hurt my feelings if you picked something else. But this is very similar to the way the U.S. Navy handled data updates from hundreds of field units of the Navy Reserve on a daily basis. I cannot, for non-disclosure-agreement reasons, tell you how many reserve units there were or how many people were in those units, but let's say it was bigger than a lot of corporations you could name. And it worked quite fine for a long time, over ten years, before new technology became available to change how we did things.

 

[Pat Hartman]

like a local backend that stores the data that is typed in and also the tables that hold the data that is necessary to complete the compare operations.

I'm still confused regarding the hardware you are using.

1. Is there existing hardware at each workstation? Because if no hardware is currently installed, your project would need something that doesn't exist and you would need to get it into the IT plan for the shop floor.
2. Does data need to follow the part as it moves from station to station? Because if it does, a flaky connection won't work. Station 2 needs the data collected at station 1. If there was no internet connection, it is not available at station 2 UNLESS there is a portable device with a local database and that device is moving along from work station to work station with the part being manufactured which I find extremely unusual.