Mobile Workers

[rambo12]

Hi,

I have a scenario where there are 2 engineers with a laptop each. They go out and about completing jobs and would like to type the details instead of writing on multi-part forms.

What would be the best way of achieving this?

I was thinking a few things including hosting the mdb on a web server and having them connect using a usb 3g adapter. Is there a way they would be able to work on a local version then when they get back to the office merge the data into the main mdb file on a server?

Thanks
Mike

 

[Rabbie]

It can be worth checking out Database Replication. This link was very useful to me when I was setting replication up.

 

[Simon_MT]

Sorry rabbie,

Forget replication go for Terminal Server. No code change or synchronisation.

Benefits:

Data source is always the same and it is real-time.

There is not reason while Mobile login into the Terminal Server and therefore a full user profile could be set-up on the Terminal Server.

All that is being passed remote are screen dumps access goes like the clappers as it's calls are all local.

All the other company documents can be made available.

Local or remote Printers can be used.

Its a very good solution, once you have used a Terminal Server you simply would not go through the hassle of replication.

Simon

 

[rambo12]

I'm thinking of having an application written in VB to interface with a web based MySQL server which would allow both of them to connect and see the data. Would this work well do you think?

 

[speakers_86]

Whats wrong with syncronization? (besides the spelling!) I was going to say to put the backend in the locally in the office, with copies on the laptops, that are linked to the backend.

 

[Rabbie]

Sorry rabbie,

Forget replication go for Terminal Server. No code change or synchronisation.

Benefits:

Data source is always the same and it is real-time.

There is not reason while Mobile login into the Terminal Server and therefore a full user profile could be set-up on the Terminal Server.

All that is being passed remote are screen dumps access goes like the clappers as it's calls are all local.

All the other company documents can be made available.

Local or remote Printers can be used.

Its a very good solution, once you have used a Terminal Server you simply would not go through the hassle of replication.

Simon

SImon, Please correct me if I am wrong but doesn't Terminal Server require that users have access to the internet or similar.

If that is the case how do the laptop users manage if they are not able to access the internet?

 

[rambo12]

Thought i might add that they do not have a server that could be used for terminal services. It's a small business and I think they have one PC which is only turned on when needed.

I already host their website so i was thinking of using a mysql database and giving them a usb 3g adapter to connect to the internet.

 

[Banana]

FYI: You can connect to MySQL database remotely with Access just fine. In fact, that's what I did for the company I used to work for where we had remote offices needing a common server. Worked like a treat. The only concern was with security, especially with a WAN connection, but that can be overcome with some precautions.

That way, you get all power and development speed of Access with the full on RDBMS strength of MySQL for nothing. (Well, Access isn't free but you already it so the costs is already "sunk", so to speak)

Though, you should want to consider whether your users will be able to count on having connectivity. If they're out in the field and it's a remote location, this is one use where replication may make more sense.

 

[dfenton]

If they have Internet access in the field and you have a pre-existing Access application, it's a no-brainer -- use Windows Terminal Server to host the app.

If the app doesn't yet exist, I'd recommend developing it in Access, rather than with VB or web-based, as it's going to be much, much faster and fuller-featured.

If they don't have Internet access in the field, then this is a perfect application for Jet replication, particularly if the workers return to the office often and plug into the wired LAN there. In that case, it's trivial to code the synch.

On the other hand, if they need to synch while in the field, Jet replication gets an order of magnitude more complicated, though it's still the easiest way to get the job done.

 

[Mike375]

SImon, Please correct me if I am wrong but doesn't Terminal Server require that users have access to the internet or similar.

If that is the case how do the laptop users manage if they are not able to access the internet?

This is something I am starting to look into because wireless internet is now very common in Australia. I use wireless and the modem just plugs into a USB port and will work on any computer.

 

[rambo12]

I never knew Access could use MySQL!! Just connected it up and looks promising.

 

[rambo12]

Ran into some troubles - Keep getting "MySQL server went away" after a while and then it just doesnt work.

Timeout on hosts server is set to 20 seconds.

Is there a way to get access to connect, do its stuff, then disconnect?

 

[Banana]

What MySQL server version and MyODBC driver version are you using?

I believe there is an option setting that ask for automatic reconnect. You can configure accordingly by going to Data Sources (ODBC), select MyODBC, and click configure. The Options should be fourth tab, IIRC.

If you are serious about this, here are some IMHO mandatory reading:

Jet/ODBC Connectivity Whitepaper (It's a bit dated, but AFAIK, still valid even for today's version)
MySQL and Access Front-End Configuration (especially useful for data types and ODBC options)

 

[rambo12]

4.1.22 Mysql with MyODBC 5.1 and i have enabled the automatic reconnect under data sources in control panel. It's 20 seconds of inactivity that does it... if i keep opening and closing the table it doesnt go off. Once it does go away i have big trouble getting it to work again and usually just reopen access.

This looks very promising indeed if only i could get rid of this error.

 

[Banana]

I'm not familiar with 4.1.22, having only worked with 5.0 and 5.1.

That said, is this server your own or a provided server from a hosting company? I understand some hosting company have a process killer in place to cut off process that takes too long.

Another thing I remembered doing is to keep a connection open at all times using OpenDatabase method. There's a article. Though it's for linked Access backend, I saw no point of re-opening & closing connection and since Access caches the connection, I made use of that method to ensure that the same connection would be used at all time.

See if this help...

PS MySQL's documentation on the error

 

[rambo12]

I think i solved it anyway.. i saw somewhere Setting wait_timeout=3D28800 would work. I couldnt find the option in MyODBC 5.1 so i installed 3.51 and did that and all appears to be working. Now I can get working on some ERD's.

Has anyone ever tested this over a usb modem? (3g/gprs) I dont know how access works but speed would be an issue.... say if the user was adding a record on a form.. would it only send that record or is there more data to send/receive?

 

[Banana]

Glad you got it fixed.

You would probably want to watch out for the amount of queries you ask for per record you visit in form.

If you've read the whitepaper I linked and written your queries correctly, Jet is quite very intelligent and conservative in its data request- it will get just enough to display for the form- provided you do not make unreasonable request (a good example is using VBA function, which would force Jet to do a local evaluation of *all* records before knowing what it needs to show. Not good)

But if your form has a lot of comboboxes and listboxes and subforms, that's more query per record to be sent.

Another thing to be aware is that Jet is chatty- which is good for concurrency but not so good for network traffic. I'd prefer to work with Jet until it's obviously a problem before I'd work around this behavior.

Last point- technically, Access isn't involved in network traffic- all it would be doing is talking to the local MyODBC driver which handle the network connection. Not a big point but just so you know.

Did that help?

 

[rambo12]

Yes Brill Thanks for your help.

I'll probably be back in a few hours with a load of new problems

 

[brickelldb]

Access with mysql over the net - Learn something new Everyday!

 

[Simon_MT]

I'm assuming if the user have a 3G capability then they will have internet access. Rather than hosted data stores, relying on on their up-speeds and performance issues a Terminal Server is PDQ. Yes a Terminal Server requires a financial it does give you control and it can be justified then I would champion a Terminal Server. If the company is serious about IT, then they have to invest.

Simon