Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Sat Aug 29 13:19:04 CDT 2009
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
_______________________________________________