Mess Up shared Folder in LAN / WAN

[FuzMic]

Hi guys

I keep my msaccess data in a UNC Shared folder eg \\PCDATA\SIMPLE in a LAN.
The LAN is connected to WAN via my ISP Provider that start with IP 202.

When i ping PCDATA it goes through quoting the local IP Address eg 192.168.1.77

If i add a wireless router to the modem, when i ping PCDATA the IP Address comes up as my ISP 202 causing confusion for the front end.

What is happening, what mistake did i do; so ask the Novice.

 

[bob fitz]

I may have misunderstood your question but Access, as the backend, does not work on a wireless network.

 

[FuzMic]

Bob do you mean that a tabloid cannot connect to a BEnd on a pc via wireless peer to peer. Is it really so?

I frequently have pc connecting to another eg the pc holding the backend where the communication is by wireless. I don't seem to have problem with these. This may not be the best option but it seem never to fail for all practical purposes.

I may have mislead you about the reference to WAN. I am talking about all pcs within the LAN.

 

[bob fitz]

You can not use access as a back end on a wireless network. Data will become corrupt.

 

[FuzMic]

Bob so in an wireless environment, i presume the FEnd can be Access while the bEnd should must be SQL, mySQL, FireBird or the like for avoidance of data corruption.

In short accdb or mdb is no good for wireless BEnd. Moreover they are a flat DB that is drive read/write intensive plus relying on file/record locking to share the DB.

In your opinion which is the easiest & optimal bEnd server to use. I am thinking of using FireBird with ODBC to link, while Access remain as the FEnd.

 

[bob fitz]

I have only ever used access as the back end on a wired network. However, if I were you I'd try SQL Express for the back end. It is free and is another MS product.
Good luck with your project.

 

[FuzMic]

Bob Fitz, THANK YOU.

Ii have been reading some good advice in UtterAccess written way back in 2012 http://www.utteraccess.com/forum/index.php?showtopic=1986467

Incidentally there is a Bob G (probably not you) & also Galaxiom whom have helped me many times in this forum. Do anyone of you know of a good FireBird forum like this to join?

I have had my hands on SQLExpress 2005 years ago and forget some of the insides. I will certainly have another deeper look into it. Which has a easier learning curve, 2012 or 2014 version.

Recently i come across FireBird, i like the latter because all the tables is in one database. Unfortunately the .FDB can be quite large. Some questions on SQLExpress

1. Is all tables in one database?
2. Can database be portable for zipping or transfer without constraints?
3. What is the size of a normal database (ie if it is in one file)

 

[FuzMic]

Bob

Are you saying that only Access cannot work with a BEnd in Wireless or it applies to all other database programs.

I read that using a proper server as BEnd will help plus using tricks to avoid the inherent problem of "JET file share corrupt when the connection breaks".

Have you experience in using Thin Client Tech.

Your opinion and advice much appreciated.

 

[The_Doc_Man]

FuzMic,

The problem with wireless networks is that while they break and re-make peer-to-peer connections automatically, it is the virtual-socket-based connections that are at risk.

Access tries to use something called Server Message Block (SMB, a.k.a. "Samba" in the UNIX world) connections. They are based on an established channel through the peer-to-peer connection that is part of your wireless network. The problem is that if the SMB connection breaks and later re-makes, the re-make (like many bad movies) essentially starts over again from scratch. It does not pick up where it left off before the break.

Therefore, if you had a multi-block transaction that wasn't protected with a Begin Transaction, Commit sequence, you could have tables partially but not completely updated as the result of one of these break/make events. In other words, you would have half-update table. Usually not considered a good thing to have, you know.

If you have no choice but to use wireless networks, I advise that EVERY TRANSACTION that can update the BE file should be protected with a Begin Transaction / Commit sequence. Also, you should error-trap that sequence because you will need to catch any errors - and in that configuration, you WILL have them. It's just a matter of time.

 

[FuzMic]

THANK YOU DOC for the showing your understanding of the issue.

I will certainly put any update query to the BEnd with a Begin Transaction / Commit sequence as you advised. I remember reading that to use this i need to work with DBEngine.Workspaces, rollback on error and more. Some code examples will help alot.


Meanwhile what if i use SQLExpress or Firebird as the back end will this problem still exist in the wireless environment. Appreciate & eagerly waiting your direction.

 

[static]

Anything that uses a database server will be fine.

A server takes commands, executes them server side and sends back a status or result to the client.
If the command is broken, due to connection drop out or whatever, it will error and data won't be updated.

Access can only work client side. It writes to the database file directly and has no way of knowing if the file is written to correctly or not so any lost data can corrupt the file. Use of transactions would make no difference.

 

[FuzMic]

Thanks Static for words of assurance of how server act in the server side.

I just wonder if it is true that when Commit.Trans fail within the client side block, i do see the response within the client side, is it not. Then i can either give i another try or exit the commit block after the rollback.

Or you saying that the commit block does not tell us anything and that only the database server can response?

Of course with a server, this is not necessary as as the server is design to do it. Chance of failure is minimal, is it not.

 

[The_Doc_Man]

static, if you use transactions, I think it is easier to recover a failed connection and more likely to succeed. I wasn't suggesting that you would guarantee better performance. I was pointing out that transactions would address the recoverability, but might not have stated that clearly enough.

However I also absolutely agree that using an active BE server is a better solution.

fuzmic, using an active BE server works better because if you are running SQL transactions, the "window of opportunity" to suffer a connection loss is MUCH narrower. For SQL-based active BE servers, all you send is a short text string (the SQL query) plus any overhead network overhead for security and message sequencing. Here, you are playing with probability of network loss interfering with message traffic.

Since Access has to move lots of traffic around because the FE does the work, you have a higher probability of loss of signal. For an active BE server, less traffic and smaller overall messages means lower probability. Unfortunately for wireless networks, nothing I know will reduce probability of signal loss to zero.

 

[static]

Chance of failure is minimal, is it not.

Depends what you mean by failure. Obviously there will be the same amount of data errors, but a server deals with them better.

If an instruction to a database server is garbled, the server will ignore it and send back an error. If the server receives and executes the instruction ok but the status or result is garbled at the client end... well who cares? The application will either deal with it and try again or the user will get sick of waiting and try again. It doesn't matter. What matters is that the database maintains its integrity.

But you can't guarantee that with Access. Access can only write to a file and hope everything is ok. When it reads it back it expects everything to be ok and when it isn't it usually crashes.

@The_Doc_Man

All I can say is Maybe. I would have no interest in dealing with such a setup and have no background experience. I doubt any errors generated would be very useful.

 

[FuzMic]

Doc again thank you for the clarification, the final verdict is out, use a BE Server. Meanwhile for a quick fix for running BE .mdb i am starting to use the Transact.Commit rollBack sequence.

Static thank you for your follow through; ideas well noted.

 

[The_Doc_Man]

static:

I doubt any errors generated would be very useful.

It isn't that the specific error would have been useful but rather it would be useful to have immediate confirmation that your transaction didn't make it AND your code would have the chance to do something rational before dying.

We do not disagree about the better response and reliability of having a BE server rather than Microsoft File Sharing via SMB on the FE side of the net. My comment is simply this: Access will STAGE a transaction that follows a Begin Transaction and will not apply it until it gets the COMMIT command. During the time that the COMMIT is being applied, you are still highly vulnerable, but at least part of your "window" is being used with maximum efficiency for that type of network. I wouldn't want to design it this way either, and might even refuse to take the work if forced to live in an Access wireless environment. (Particularly since I'm retired, I almost guarantee I would decline the work.) If a wireless network is the only answer then someone isn't willing to spend enough money to adequately solve the problem, and who wants to work for that kind of boss? But I was (and still am) able to say that if you are going to do this crazy thing, this is the best way to approach it without changing the network setup.

 

[FuzMic]

Yes, yes New Orleans Doc.

To minimize the wireless caused errors i have now done most the work within the front with links to .mdb tables only at the front tabloid; ie without calling up the wireless transporter.

Only when a real update to the main data is required, i had to use the Begin trans to mitigate the problem. It seem to have cut down data corruption.

But your statement

During the time that the COMMIT is being applied, you are still highly vulnerable

rocks my confidence. Are you saying with a BE Server this issue does not occur.