Split Database with Runtime

[Steve@trop]

I haven't done any testing yet on splitting the database I'm working on but a question came up today about networking.

This database is going to be used by a few equipment operators to record machine readings on the manufacturing floor. There is a wireless network and we are in the process of confirming that the wireless signal reaches all the areas. The users will be using ruggedized windows tablets to enter the data.

The BE of the split database will reside on a network drive to ensure everyone that needs it will have access and it will be regularly backed up. etc.

Since there is alot of heavy machinery, there may be some spots where connectivity will be weak or non-existent while they are entering their readings.

The question is how will the runtime FE handle it if connectivity is temporarily interrupted?

Thanks,

Steve

 

[pbaldy]

Not well:

http://www.kallal.ca/Articles/split/

Near the end.

 

[spikepl]

What exactly is the DB to be used for? "Recording machine readings" only? What else?

BE of the split database will reside on a network drive to ensure everyone that needs it will have access

In other words, will the information flow be only from operator to db, or will the operators also require data from the db? The operator-to-db data - does it need to be available centrally instantaneously? Or would at end-of-day be enough?

BE/FE over WiFi sucks, but perhaps the architecture can be made to fit, depending on the need

 

[Steve@trop]

I replied yesterday but it didn't post for some reason...

Anyway, the database is intended to replace paper log sheets that the operators have been using to record machine parameters. It's a refrigeration plant with 14 large compressors and other supporting equipment that needs constant monitoring. The operators will be using ruggedized Windows pen tablets out on the floor to record the readings that they are currently taking down on 11x16 paper log sheets (which are then stored in boxes in the office area). This is the main part of it. Another part of it will be management, engineering and lead operators will be running various reports to monitor the equipment operation over time.

I'm mostly concerned about the recording of the data since they will be using the wireless tablets to do that. Once the data is in there, the people that will be accessing it will be doing it from wired networked PC's which will hopefully be less of a problem.

If we find that wireless coverage is good throughout the plant, it may not be a problem but since there is alot of heavy machinery in there I fear that interference will be a problem. They will add more access points if necessary to boost the signal. We may also add some strategically-placed docking stations as well.

Steve

 

[pbaldy]

You can test, but wireless networks are notorious from dropped connections, however brief, which Access doesn't like. At the least, I'd probably make the form used by the people on the floor unbound, so it's not trying to maintain a connection.

 

[Steve@trop]

If the form is unbound, how will it update the table?

I'm a little shaky on the bound/unbound terminology so it could be that I don't understand how this works.

Thanks,

Steve

 

[pbaldy]

This may help:

http://www.baldyweb.com/BoundUnbound.htm

Basically you would use a recordset or append query to add the data from the form to the table. If they are just entering new records, it's not that hard.

 

[spikepl]

Try answering all my questions - so far I do not see any need for 100% connectivity. This means that you can run updates - like Paul implies. You could also store all the data locally, and then run an end-of-day process, where data is transferred to central location, via wifi or wire. Finally, I believe that an SQL server would be less sensitive to com dropouts.

 

[Steve@trop]

You could also store all the data locally, and then run an end-of-day process, where data is transferred to central location, via wifi or wire..

What you said above sounds exactly like what is needed here. I really like this idea but I don't know how to accomplish it. How would I structure this? It would be great to have the operators record the data on the tablets, then transfer the data to the main database from a wired docking station. That way they wouldn't have to worry about wifi connectivity on the manufacturing floor and performance would be better this way. The tablets are powerful enough and have enough capacity to store everything they'd need locally.

Thanks,

Steve

 

[spikepl]

So your db would have both frontend and backend locally on the tablet ...and then some vba in it, that at the press of a button connects to a central table and updates it with the day's data from the tablet.

 

[Steve@trop]

So your db would have both frontend and backend locally on the tablet ...and then some vba in it, that at the press of a button connects to a central table and updates it with the day's data from the tablet.

That sounds great. You make it sound really easy. Am I going to need a programmer for this or is it simple enough for a VB newbie like me to do? I've got at least 13 tables to do this with.

Steve

 

[Steve@trop]

After doing some research it looks to be quite a challenge to do this.

I think we'll just have to test how it works as a split database across the WiFi network. I'll plan on an SQL back end in hopes that it will be more stable in this environment.

Thanks,

Steve

 

[gemma-the-husky]

you don't need a database on the tablet do you. you can just capture readings to a csv text file or similar, and then integrate them from that.

I expect that's what DHL type delivery machines must do.

 

[Steve@trop]

Thanks for the suggestion. I don't know how I would do a front-end (forms, etc) without a database. Then getting that data into the database once they are done entering it could be a big challenge.

 

[DBinPhilly]

Thanks for the suggestion. I don't know how I would do a front-end (forms, etc) without a database. Then getting that data into the database once they are done entering it could be a big challenge.

I recently had a similar problem with someone on a WIFI network who was far enough away that it went off-line with some regularity.

My solution for a relatively novice programmer would be:
1) create a format identical to the BE table on the FE program with a different name. Easiest way: While in the Front End program, open the back-end table in design view, (it will give you a message saying you can't change it), then save it under a different name. It will save just the format locally.

2) Rename the BE table in the Front End--- such as: Original name: tblName; change it to: tblName_BE. (It will still have its original name in the BE, you are just renaming the link).

3) Rename the saved FE table with the original BE table name such as : tblName --- then all programs that were meant for the BE table will run without revision, only updating the FE table

4) Do your daily data entry

5) write an append query to update all records from the FE table to the BE table (using the new BE name)

6) Run a delete query to empty the data out of the FE table.

Repeat steps 4 thru 6 daily

 

[Steve@trop]

Thanks DBinPhilly,

That doesn't sound too bad at all. I'll see if things work OK without doing this first but if I see lots of performance problems I'll try it.

Steve