Linking Access across the internet

[Mike375]

Basically the situation is people in the field with laptops and also from "office to home"

I have an involvement with someone who is a website person but not a data base person as such and I am going to show him this thread as my knowledge on webs, servers etc is something less than zero.

The situation with people in the field (perhaps from 1 to 5) would be mainly updating a record such as a job is completed etc.

 

[Atomic Shrimp]

I think there are basically three choices:

Develop a web front end for your database application - either using a third-party tool, such as Whizbase, or by hand in ASP or PHP or something.

Set up some kind of remote terminal/desktop arrangement whereby remote users can connect in to your local network and access applications/files/services as if they were in the office. There are any number of different flavours of this, including remote desktop solutions such as GoToMyPC (or Windows Remote Desktop) - which require an actual PC at the office end, or desktop virtualisation solutions such as Citrix Xendesktop, which are server-based.

Develop your application so that it communicates with remote users by sending them files (say, Excel spreadsheets), which they update and send back, to be imported and processed back into the system.

 

[gemma-the-husky]

Access doesnt work very quickly over a WAN - but even so, for very small processes, such as you describe, it may still be workable

However, if your users are off-site, then you don't even have this luxury

The easiest way is definitely to have a user remoredesktop to a PC that HAS the database.

Note any potential problems with multiple users sharing the same dbs at the same time. - which is not a good idea.

 

[Mike375]

Develop your application so that it communicates with remote users by sending them files (say, Excel spreadsheets), which they update and send back, to be imported and processed back into the system.

Initially this will probably done but using a second small DB and when the bloke in the field updates a record it will make a table with that record and then email to DB to head office. I can put an autoexec in the little DB to export the table and then the main DB to do the rest.

This is a plumbing business and the updates refer to jobs that are completed. Currently the plumbers show up each morning with paper work from the day before.

 

[Mike375]

The easiest way is definitely to have a user remoredesktop to a PC that HAS the database.

I found this link.

http://www.microsoft.com/windowsxp/using/mobility/getstarted/remoteintro.mspx

Is remote desktop like opening the DB on another computer that is on the network as opposed to the normal back end and front end setup, except the route to the other computer is via the internet?

 

[SOS]

For small bits of data, if you had access to SharePoint Services (free if you have Server 2003 or above - or you can get a free account with Office Live and use that SharePoint), you could use SharePoint with Access (better with 2007 but still doable with 2003 I believe). With 2007 I know you can "synch" your data so you can be offline and then synch (as with replic*(##$ - sorry bad word).

 

[dfenton]

suggested three approaches:

[1] web front end...

[2] remote terminal/desktop...

[3] Develop your application so that it communicates with remote users by sending them files (say, Excel spreadsheets), which they update and send back, to be imported and processed back into the system.

This last one is usually very hard to implement, particularly if there are updates of data in multiple locations. You left out:

4. Use Jet replication.

This works very reliably and is very easy to set up for users who have laptops, edit data in the field and then come back to the office regularly. A simple direct synch (about 5 lines of code) does the job. This kind of scenario is an ideal environment for using Jet replication.

However, if users need to synch with the mother ship while in the field, Jet replication gets an order of magnitude more difficult, as you can no longer use direct replication (the simple method), and need to implement indirect replication over a VPN or Internet replication. Both of the latter require significant server-side setup in the home office, and significant programming.

For details on Jet replication, see the Jet Replication Wiki:

http://dfenton.com/DFA/Replication/

 

[Mike375]

The updates need to get back to head office not long after they are made so it needs to be "live" or at least as live as an email sending what is required so I think replication is out???

Develop your application so that it communicates with remote users by sending them files (say, Excel spreadsheets), which they update and send back, to be imported and processed back into the system.

I am OK with that as have done it before but using a small DB as opposed to Excel to get the data across but not as nice as some sort of linking. In this situation the head office computer would get maybe 30 to 40 emails a day as there are about 30 to 40 jobs done per day by the plumbers in the field.

Part of the reason thay want to connect to the field is that currently they have to enter the data from the plumbers paper work. I could cover that easily by not having the email automated but the plumber doing it at the end of the day and that way head office would only get about 6 emails a day and no need for entries to be made or for the plumber to bring in the paper work.

I seem to remember reading something on this forum about Windows Terminal Server in connection with this type off issue.

 

[dfenton]

The updates need to get back to head office not long after they are made so it needs to be "live" or at least as live as an email sending what is required so I think replication is out???

If they have enough of an Internet connection to support regular exchange of files, then they have enough of an Internet connection to run the whole application on a terminal server in the home office. That is by far the easiest way to accomplish the task.

Merging edits for multiple users at multiple locations is an extremely difficult task. I've done it between two databases more than once, but between more than two it becomes exponentiallly more complicated. I would not even attempt that. My first choice would be to host the app on Terminal Server, and if the Internet connection is somewhat intermittent, then go to indirect replication.

 

[Mike375]

My first choice would be to host the app on Terminal Server, and if the Internet connection is somewhat intermittent, then go to indirect replication.

Are you saying that the connection is to Terminal Server and then replication is done.

I have only tried Replication once and just as an experiment but I remember it was about choosing which updates to use or something similar, it was very messy.

 

[dfenton]

Are you saying that the connection is to Terminal Server and then replication is done.

No. Terminal Server removes the need for replication, because the users are running the app on the Terminal Server, and not on their local laptops.

I have only tried Replication once and just as an experiment but I remember it was about choosing which updates to use or something similar, it was very messy.

Huh. Direct replication couldn't be simpler. Perhaps you are talking about resolving conflicts? If you think the Access conflict resolver was complicated to use, then you shouldn't even be contemplating the suggestion to ship files back and forth and apply updates, because the logic required to properly update data without stepping on other people's logic is quite complex.

 

[NigelShaw]

Hi,

i currently use a few asp pages. i have an mdb on my site that holds various data. i input this from my remote database using a script. I have certain asp pages set up to do different things for example-

to add a new record i use AddRec.asp
to update an existing record i use UpRec.asp which updates a record based on a value i pass.

it works on a simple process of html script. for example

http://mysite.com/addrec.asp?name=name&email=email&phone=phone

the asp receives this and passes the info over to a new record. i dont get a html response as i have the response.write turned off.

i also use OpenInternetUrl which lets mdb to mdb via html across internet.


HTH



nigel

 

[Mike375]

If you think the Access conflict resolver was complicated to use, then you shouldn't even be contemplating the suggestion to ship files back and forth and apply updates, because the logic required to properly update data without stepping on other people's logic is quite complex.

I would normally agree with you 110%. However, in this case is it not hard to do. The records are only changed by the plumbers in the field and none of the plumbers overlap on the records. The main problem with the email back system is that about 35 emails would come back each day unless it was at the end of the day and then there would be about 6 or so but the head office would not be updated during the day.

But I want to be able to do it without emailing because the next one that comes up won't be so straight forward and be in the territory you are describing.

 

[NigelShaw]

Hi

I would see this fairly simple with the method I use although tough to set up!

1. Create an asp page that can read/write to the database
2. Have the page available on the webserver
3. Let the users update as and when which would initiate the script that is constructed on command. For example-

strPath = "http://myweb.com/updaterec.asp?completed="
strResponse = True ' or false
strUserDesc = "&user=
strUser = "user"
strJobDesc = "&JobID="
strJob = "id"

strRunResp = strPath & strResponse & strUserDesc & strUser & strJobDesc & strJob

then using OpenInternetUrl, you run the script and the table is updated

the asp page does the SQL query so it will pick up the user and job id to filter the query and update the right record.

This currently works really well for me. I'm in shopfitting and have similar scenarios as you describe.


Regs

nigelshaw

 

[Mike375]

Nigel,

I am showing that to the fellow who I work with that is into websites etc as I have no idea what you are talking about

 

[NigelShaw]

Hi Mike,

check these links out -


http://msdn.microsoft.com/en-us/library/aa385096(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa384363(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa384233(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa384227(VS.85).aspx
http://msdn.microsoft.com/en-us/library/aa384247(VS.85).aspx

http://msdn.microsoft.com/en-us/library/aa385103(VS.85).aspx

these all create the VBA internet communication. it takes a bit of setting up but concentrate on sUrl as this will be the url that is called in this case, your script.

Your web guy would then be looking at creating a page that receives a script and reads the values. something like -

        Dim strUser
	Dim strJobID
	Dim strComplete
        Dim arrUser(4)
		
	strName = CStr(Request("Name"))
	
	strJobID = CStr(Request("JobID"))
	strComplete = CStr(Request("Complete"))

this would store the script values into the asp page. then you would want to run sql into the mdb to either update or add records. Whichever you need

if your asp was in your website mysite.com and placed in a folder called update and the page was called callupdate.asp, the script would be

http://www.mysite.com/update/callupdate.asp?name=name&jobid=jobid&complete=complete

the page picks up the values from the string and does its stuff

you would need to store the retrieved values into an array

                arrUser(0) = CStr(Trim(strUser))
		arrUser(1) = CStr(Trim(strJobID))
		arrUser(2) = CStr(Trim(strComplete))

then issue the array into an sql statement which im sure your guy could do easily.

if you have problems, i'll see what time i have to make a demo


regs

Nigel

 

[Mike375]

Thanks Nigel, will check further and look at links. I am seeing the web bloke tomorrow.