I wouldnt post this again but really could do with some guidance on a problem I assume many people have cracked before in this forum.
My client has a MS Access db as the backend for his non-notes web site storing potential customer records. The web site is on a hosted by his ISP.
What he would like to do is integrate the data on the access db with his Notes based CRM system living on his internal LAN. Their Domino server is R5.08 but they intend to move next week to R6.5.
On previous integration projects I used the LS:DO. However with this situation I can use ODBC to connect to the access db using this but not in a secure manner.
Can someone please advise either a network solution or perhaps a different approach.
Access db ODBC connections seems to prefer a local connection or a network connection as Microsoft’s Network Neighborhood convention.
So… there would have to some sort of TUNNEL to the ISP (where the security would come into play) and from this tunnel the domino server could access it as if it is local. I think the hardship here is that an access db is not really a server solution. Access is more of a client computer/desktop solution. I think the difficulty is going to be connecting here is connecting to the db because the easiest way would be if it was local to the domino server.
I had mailed the ISP to investigate the network/vpn options if any. I am awaiting his response.
I agree that access is not really a server solution. As usual it is a case of needs must when the devil drives. The client wants a solution for next week
If the data has to be used as a back-end “live” over the internet, then I do not see how you could do that in a reasonable manner.
However, if you are only updating data in your Notes database from Access on some sort of schedule, then you could use a web service over the internet (via SSL for security) to pull back the data.
Slower than ODBC, but it would work with only minor changes to his website - (that is, something to handle web service request on his side and to write out the response back to you.)
On your side you can use the XMLHTTP COM object to get the job done. (I believe it is MSXML2.XMLHTTP.)
You can search this forum for examples of how to do a web service via the MSXML COM object.
On the website, you will need to have something spit back the data you want.
It can be done very easily in one week. You should be able to have a prototype up in a day - tops.
Some more options:
If it is just a file import on a schedule, it may be even easier to just run FTP to either (a) pull down the Access database itself and read from it using ODBC as you are used to, or (b) pull down an extract/dump of the access database. You could also use a Web Service to pull the file if FTP is not available.