jwcolby
jwcolby at colbyconsulting.com
Sat Aug 29 14:15:39 CDT 2009
Thanks Shamil.
What is missing here is an explanation.
1) What IS a web service?
2) What are the pieces, and what do they do? I assume that there is a service and a client?
3) What is this web service doing?
4) Doesn't it seem that ...
getting IIS and SQL Server set up, writing web services, writing a DLL for the client Access
database, getting the existing system using the DLL and installing the DLL on each machine...
doesn't all of that seem much more complicated than installing Hamachi and mapping a shared
directory to a drive letter?
Now, having said that, I have a future project which (if you would slow down and explain just what
the heck this stuff was) MIGHT be a perfect fit for this technology. OTOH, the future project is a
system that I hope to sell to small drug companies. Whatever this stuff is has to be point and
click installable as software at the client's site.
As I mentioned to Eric, I am listening, but so far I haven't understood a word of what I am hearing.
Perhaps a reference to some book, or article that explains this stuff step by step...?
Thanks,
John W. Colby
www.ColbyConsulting.com
Shamil Salakhetdinov wrote:
> Yes, web service would be a good solution here.
>
> I have made a test web service, which is located here:
>
> http://shamils-4.hosting.parking.ru/MSAccess/MSAccessWebService.asmx
>
>
> You can test it using this code (c#):
>
> using System;
>
> namespace WebServiceDirectTestConsole
> {
> class Program
> {
> //
> // Add ASP.NET 2.0 web service reference:
> //
> http://shamils-4.hosting.parking.ru/MSAccess/MSAccessWebService.asmx
> //
> static void Main(string[] args)
> {
> try
> {
> const int CALLS_QTY = 10;
>
> DateTime startTime = DateTime.Now;
>
>
> WebServiceDirectTestConsole.FileServiceSample.WebServiceFacade
> service = new
> WebServiceDirectTestConsole.FileServiceSample.WebServiceFacade();
>
>
> //http://shamils-4.hosting.parking.ru/MSAccess/LoremIpsum.txt (50KB)
> string p = @"c:\temp\LoremIpsum.txt";
> string fileName = "testFile";
> string fileText = System.IO.File.ReadAllText(p);
> string text = null;
>
> for (int i = 1; i <= CALLS_QTY; i++)
> {
> service.StoreFile(fileName + i.ToString(), fileText);
> text = service.GetFile(fileName + i.ToString());
> }
>
> text = service.GetWebServiceUsageStatistics();
>
> Console.WriteLine(text);
>
> DateTime endTime = DateTime.Now;
>
> double elapsedTime = ((TimeSpan)(endTime -
> startTime)).TotalSeconds;
>
> Console.WriteLine("Elapsed Time = {0:#0.00} seconds for {1}
> calls ({2}s/call, fileSize = {3})",
> elapsedTime, CALLS_QTY, elapsedTime / CALLS_QTY,
> fileText.Length);
>
> }
> catch (Exception ex)
> {
> Console.WriteLine(ex.Message);
> }
> }
> }
> }
>
> One of the results of the above test runs is the following:
>
> Total Web Calls: 20
> Total Web Calls Duration: 20 ticks
> Total Store File Web Calls: 10
> Total Store File Web Calls Duration: 10 ticks
> Total Get File Web Calls: 10
> Total Get File Web Calls Duration: 10 ticks
> Total Delete File Web Calls: 0
> Total Delete File Web Calls Duration: 0 ticks
> Currently Stored Files: 10
> Currently Stored Files Length: 503440
>
> FILES
> -----
> fileName = testFile1, length = 50344
> fileName = testFile2, length = 50344
> fileName = testFile3, length = 50344
> fileName = testFile4, length = 50344
> fileName = testFile5, length = 50344
> fileName = testFile6, length = 50344
> fileName = testFile7, length = 50344
> fileName = testFile8, length = 50344
> fileName = testFile9, length = 50344
> fileName = testFile10, length = 50344
>
> Elapsed Time = 6,80 seconds for 10 calls (0,6799s/call, fileSize = 50467)
>
>
> If you are interested we can make intensive test of this sample web service.
>
> (I personally would be interested in such testing.)
> I will make all its source code (simple) available on
> northwind.codeplex.com.
> This sample web service has already MS Access/VBA callable wrapper ActiveX
> DLLs.
>
> Thank you.
>
> --
> Shamil
>
> P.S. Here is how the same web service can be called from MS Access/VBA using
> ActiveX DLL wrapper library:
>
> Option Compare Database
> Option Explicit
>
> Public Function TestWebServiceAdv()
> Dim service As New MSAccessWebService
> Const CALLS_QTY As Integer = 10
> Dim startTime As Date
> startTime = Now
>
> '//http://shamils-4.hosting.parking.ru/MSAccess/LoremIpsum.txt (50KB)
> Dim p As String
> p = "c:\temp\LoremIpsum.txt"
>
> Dim fileName As String
> fileName = "testFile"
> Dim fileText As String
> fileText = ReadAllText(p)
> Dim text As String
> Dim i As Integer
>
> For i = 1 To CALLS_QTY Step 1
> service.StoreFile fileName + CStr(i), fileText
> text = service.GetFile(fileName + CStr(i))
> Next i
>
> Dim endTime As Date
> endTime = Now
>
> text = service.GetWebServiceUsageStatistics()
> Debug.Print text
>
> Dim elapsedTime As Double
> elapsedTime = DateDiff("s", startTime, endTime)
> Debug.Print elapsedTime & " seconds per " & _
> CStr(CALLS_QTY) & " calls"
> End Function
>
> Private Function ReadAllText(filePath As String) _
> As String
> Dim text As String
> Dim fn As Integer
> Dim fileLen As Long
> fn = FreeFile
> Open filePath For Input As #fn
> fileLen = LOF(fn)
> text = Input(fileLen, fn)
> Close fn
> ReadAllText = text
> End Function
>
> One of the test calls results is the following:
>
> Total Web Calls: 20
> Total Web Calls Duration: 20 ticks
> Total Store File Web Calls: 10
> Total Store File Web Calls Duration: 10 ticks
> Total Get File Web Calls: 10
> Total Get File Web Calls Duration: 10 ticks
> Total Delete File Web Calls: 0
> Total Delete File Web Calls Duration: 0 ticks
> Currently Stored Files: 10
> Currently Stored Files Length: 503460
>
> FILES
> -----
> fileName = testFile1, length = 50346
> fileName = testFile2, length = 50346
> fileName = testFile3, length = 50346
> fileName = testFile4, length = 50346
> fileName = testFile5, length = 50346
> fileName = testFile6, length = 50346
> fileName = testFile7, length = 50346
> fileName = testFile8, length = 50346
> fileName = testFile9, length = 50346
> fileName = testFile10, length = 50346
>
> 8 seconds per 10 calls
>
>
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Eric Barro
> Sent: Friday, August 28, 2009 5:54 PM
> To: 'Discussion concerning Visual Basic and related programming issues.'
> Subject: Re: [dba-VB] Access data across the internet
>
> John,
>
> This is a good candidate for a web service. A web service allows you to
> expose certain methods to client machines outside the network.
>
> 1. Basically in terms of servers you will need a web server and a database
> server (one physical server is possible but two are better due to security
> issues).
> 2. The web server exposes the web service application and takes care of
> authentication to the database server. The web server is the only one that
> is publicly accessible from the outside.
>
> This saves you from having to set up VPN client software on the client
> machines and also saves you from having to purchase hardware to run the VPN
> on the server side.
>
> Eric
>
>
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Friday, August 28, 2009 6:11 AM
> To: VBA
> Subject: [dba-VB] Access data across the internet
>
> I am looking at doing an application in C# that needs to be able to run on
> user systems around the country, but manipulate data in a common location.
> IOW employees can be anywhere, running this program on their machine, but
> reading / writing data to a central server.
>
> It is a fairly simple application in terms of the data, a hand full of
> fairly stable and short list tables that feed combos, and a couple of "log"
> kind of tables that document processes. It would be nice to have access to
> a fairly complex directory structure containing data files that need to be
> imported by and exported from this program. Again these files are small,
> less than a thousand lines of data, fixed width or CSV. I am looking at the
> files now and the largest appear to be 80K or so.
>
> I am thinking that a VPN tunnel to allow access to the data directories,
> which are then mapped to a drive on the local workstation. Some kind of
> data store on the server, perhaps SQL Server Express.
> A local data store to do the import into, manipulation / cleanup of data,
> export back to files on the remote server directory structure.
>
> I am wondering if you guys have experience in setting up this kind of a
> server and application to work on such a server.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
>
>
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
>
>