Protect database against corruption?

[geralf]

I have a split database. BE located at a server and FE on each users workstation. There are 8 concurrent users at the most. This runs smoothly over the LAN.

Now I'm adding a new feature to the database. I'm using a wheel loader to weigh products on a weight scale. The operator in the wheel loader is going to use a laptop with wireless LAN to connect to the network.

In risk of loosing the connection which in worst case might corrupt the database, I want to make a new database with a BE/FE setup. The BE will be placed on the same server as the current db. The FE in the wheel loader. I can then link from the current db (BE) to the new db (BE).The new db will only require two linked tables from the current db.

If worst case happens, would my current db be more protected against corruption than if I linked directly to the BE on the current db. I know there's no 'right answer' to this one, but I'd really like some opinions on it though.

Thanks in advance.

 

[FoFa]

Not sure in this case, but have you considered just placing the DB on the laptop, and having it create a file on the server to be picked up? It would run nicer on the stand alone without the network in the way, and you could have the operator, or even an automated process create a datetime filenamed file for the other DB to pickup.
Just thought it worth a mention.

 

[The_Doc_Man]

Well, first the bad news. There is never any perfect way to protect against signal or connection loss under all circumstances. You will always have a way to bollix up your DB.

(After all, we ARE discussing a Bill Gates product... )

Now, perhaps some better news. The problems occur during a window of exposure. You cannot close the window - but you can narrow it down a lot and thereby reduce the probabilities. You can also choose a method that allows for self-checking to SEE if you got caught.

FoFa's suggestion of creating a file rather than trying to do a remote update is one way to do this. It is a good choice with minimal exposure because for a short file it doesn't take much to do the copy operation. No other application has to be open while you are dependent on the network, either. By contrast, if you had to open Access remotely, you have several steps that could go wrong and could be affected by the loss of a signal.

But I suggest you put some protective VBA code in the piece that inputs the file. You want to know that this file was not truncated by losing the connection while copying it. Perhaps you can add a checksum record at the end of the file based on some arbitrary computation. Then when you want to read the file from the place where you will do the import, read the file and recompute the checksum record. If you have such a checksum and it matches, you are clear to import.

Now, what do you use for a checksum? Heck, design your own. It doesn't have to be industry standard to be usable. You could set aside a LONG integer. As you are about to write a line to your file, convert each byte of the output line to an integer (from 0 to 127, maybe) and add it to the LONG. The checksum variable wouldn't overflow for 24-bits worth, or something over 16 million bytes. I doubt you are talking about importing a single file longer than that amount. Then when you have written all of your data to the file, add one more line (that is not part of the sum) that contains the CStr( ) of the LONG variable as the only thing on the line.

 

[geralf]

Thanks for your interest and replies FoFa and The_Doc_Man

I have considered something like you describe by using some kind of syncronization solution. My boss would REALLY like a LIVE one .

I need data from the current db to the new one as well. This is production data needed in the wheel loader when doing the weighing process.

The amount of data is very small. (This is the reason they have done this manually by HAND!!! - with all the errors being done as a sideeffect ).

I will go for the live solution first, and see how it goes. I know this might be a difficult one, but I like the challenge, and I believe I'll make it work (with hopefully somehelp from you guys in here .

I will narrow down the new database as much as possible, so network traffic over the WLAN is at a minimum. Another good thing is that the new database only will have one user at any time.

The weight data I'm picking up comes from a COM port (RS232) on a weight terminal. The weight terminal can be connected directly to the LAN via some Serial-HUB, and I thought of using an ActiveX control (TConnector2) to get the weight string into VBA in the Access database. The wheel loader operator needs to SEE the weight value the weight terminal shows and press a button on the new database FE located on the laptop to save the weight value.
The serial signal sent from the weight terminal has to be manipulated to get the weight value. When weighing the products, I also need the previous weight value to get the next vweight value. This has to do with the weighing process itself.

A couple of questions popped up in my mind:

1) Can I from Access(VBA) see if the WLAN connection is good?

2) Would using Transactions be of any help?


I really appreciate your thoughts and ideas around this.

Please feel free to ask any questions you might have regarding this.

 

[The_Doc_Man]

1) Can I from Access(VBA) see if the WLAN connection is good?

Difficult but not necessarily impossible. You need a Win32 API reference and you need to be willing to wade through the specifications that let you find out a device status through that API. Which means you will need to look up (in the Help files) how to call an external function from a specific .DLL - and the help files give you an example. Then, of course, you need to know which .DLL file holds the item you want. This is an issue of maybe doing a web search on how to find device status within Windows and then just call the appropriate routine. It will be harder to find the specifications than to do the final call you wanted.

2) Would using Transactions be of any help?

Yes.

 

[geralf]

OK Doc_Man

I appreciate your guidance here. I will do some research. If anyone has done anything like this before, it would be great to know.

Thanks for your time and interest once again.