Calculate time zones 1

[aw23]

I have clients in countries all around the world. I want to load the local time for the client when standing on the record of that client. So I thought I'd record in a table the GMT offset and get the gmt time and add. However this will not work as different countries change their clocks on different dates. Is there any simple way to do this?

Thanks

 

[DomFino]

Hi,
You may want to check this site.

http://www.karenware.com/powertools/powertools.asp

There is a free Zone Manager that you can download the source code and modify if need be.
Dom

 

[aw23]

I just looked at that. It's only for personal use, I need it for my office. Thanks, any other suggestions?

 

[alvechurchdata]

How many clients do you have around the world? If there's only a few countries involved then a crude answer would be to have a simple lookup table with four fields:

country
timezone
summer time start
summer time end

It would have the useful side-effect of letting you force the user to select country from a resticted combo box rather than let them choose their own way of spelling the names.

You'd have to update it each year but you could implement it in an hour and then you've got 12 months to think of a better solution<g>.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[aw23]

Thanks, this is what I have so far. My table has four fields.
country
gmt offset
gmt offset daylight savings
daylight savings date


Now this is what I still haven't figured out yet

1.how to get current time, although I'm sure that must not be too difficult

I figure I can do the following. When a client within a country is loaded, compare todays date and DLS date. Add to the gmt time accordingly. Is this the best way?

Thanks

 

[aw23]

I have clients literally all over the world, so that would not help. Also countries change to DST on diff dates every year. Like it'll be the first Sun of this month, etc. So if I would do it my way the table would have to be updated every year. Is there any way to get current data?

Thanks

 

[VBslammer]

Try this site:

http://www.timeanddate.com/time/

VBSlammer

Unemployed in Houston, Texas

 

[aw23]

Thanks, that's a great site. But how can I use that in my program?
Thanks

 

[VBslammer]

Well, if you're thinking of building a table of values there is a site that offers members-only downloads from their world time server in a variety of formats, from csv to comma-delimited, but they are rather expensive. If your company is willing to shell out the 395.00 per year for membership, take a look:

http://www.worldtimeserver.com/time_zone_guide/

I was thinking more along the lines of a Web Service. Most of the Web Services are developed to provide up-to-the-minute information about fluctuating markets, such as stock market tickers and currency exchange rates. If you google around enough you can usually find a free Web Service that your applications can query to get the results you need.

Does your work environment have always-on Internet access? If you have high-speed Internet, a Web Service would be a viable solution. Even on a bottle-necked network you could still use a Web Service if you develop a methodology for retrieving the information during off-hours or at intervals throughout the day and storing it in a table.

One service I found returns the current time for cities around the globe simply by passing in the name of the city in the web method call, such as:

strTime = soapclient.GetTime("Baghdad")

...and the result:

Debug.Print strTime
------------------------
09:55 AM Tuesday, Jan 18 2005

If the city isn't in their database, it returns an error string. For instance, if I mispell Houston I get this:

Sorry, time for Hooston is not available

...so it's pretty fool-proof. I tried it on dial-up and it was pretty quick, about a second, which means when my Form_Current event fired or I changed a client's city, there would be a brief delay before the time appeared. It's only one small stream of data, so a good connection might populate the control unnoticeably.

If you're interested in trying a Web Service, the code is simple. The hard part is finding a good service because they've been slow to adopt and there aren't a flood of them out there. I've consumed Web Services using C#.Net and ASP.Net, but the technology is already built into the .Net framework so it's a breeze to setup in Visual Studio.

In Access, you'll have to use COM to reference the Soap libraries in the Soap 3.0 Toolkit, which you can download at Microsoft:

http://www.microsoft.com/downloads/...DD-CEEC-4088-9753-86F052EC8450&displaylang=en

I don't think you can make it work on Win95 boxes, and WinXP is the only OS that comes with Soap installed, but it's version 2.0 and really needs to be updated to 3.0.

If you've downloaded and installed the Soap 3.0 Toolkit, you'll have a new set of references available in the code editor from the [tt]Tools -> References[/tt] menu. Select the "Microsoft Soap Type Library v3.0" to try the following code sample:

Function GetTimeStamp(ByVal City As String) As String
On Error GoTo ErrHandler
  Dim objSClient As MSSOAPLib30.SoapClient30
  
  [green]' create object[/green]
  Set objSClient = New MSSOAPLib30.SoapClient30
  
  [green]' point the SOAP API to the web service that we want to call...[/green]
  Call objSClient.mssoapinit("[URL unfurl="true"]http://upload.eraserver.net/circle24/worldtime/worldtime.asmx?WSDL")[/URL]
  
  [green]' call the web service[/green]
  GetTimeStamp = objSClient.GetTime(City)

ExitHere:
  On Error Resume Next
  Set objSClient = Nothing
  Exit Function
ErrHandler:
  If Not objSClient Is Nothing Then
    If objSClient.faultcode <> "" Then
      Debug.Print objSClient.faultstring
      Resume ExitHere
    Else
      Debug.Print Err, Err.Description
      Resume ExitHere
    End If
  End If
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

Pretty simple, eh? A textbox on a form could have the ControlSource property set to:

ControlSource: =IIf(Nz([City],"")="","",GetTimeStamp([City]))

You might have clients whose citie's aren't supported by the Web Service, so here's what I'd do: create a second city field in the client table for "MajorCity" where I could store the nearest major city that is supported. Then run an update query to copy the "City" entries for all records to the new "MajorCity" field, which becomes my new bound field for the time textbox.

Whenever I find an unsupported city, I'd run an update query to add the nearest supported city to the "MajorCity" field for every record containing the unsupported city I found.

VBSlammer

Unemployed in Houston, Texas

 

[aw23]

Wow, thanks so much. I am going to try that right now. Yes, I have high speed connection, and xp. And, coincidentally I am trying to do this for time, and currency as well. I have been stuck on this for a while.
I will work on this now, and let you know.

 

[aw23]

I'm in middle of downloading. One problem I think I will encounter is that the database has counrties listed, not cities. But I guess I can have the major city in each country listed in the database and just look that up.

 

[aw23]

Wow, this is really amazing! You have saved me! How can I give you multiple stars??

 

[VBslammer]

Conicidentally, I experimented with a currency exchange-rate service before I found a world time service, and called it like this:

Dim sngRate As Single
sngRate = CalculateExchangeRate("gbp", "usd", 100000)

Debug.Print Format(sngRate,"Currency")
--------------------------------------
$158,752.90

The function:

Function CalculateExchangeRate(ByVal rsCurrIn As String, _
                        ByVal rsCurrOut As String, _
                        ByVal vfAmtIn As Single) As Single
On Error GoTo ErrHandler

  Dim objSClient As MSSOAPLib30.SoapClient30
  Set objSClient = New MSSOAPLib30.SoapClient30
  
  ' Point the SOAP API to the web service that we want to call...
  Call objSClient.mssoapinit("[URL unfurl="true"]http://webcontinuum.net/webservices/ccydemo.wsdl")[/URL]

  ' Call the web service
  CalculateExchangeRate = objSClient.calcExcRate(rsCurrIn, rsCurrOut, vfAmtIn)
 
ExitHere:
  On Error Resume Next
  Set objSClient = Nothing
  Exit Function
ErrHandler:
  If Not objSClient Is Nothing Then
    If objSClient.faultcode <> "" Then
      Debug.Print objSClient.faultstring
      Resume ExitHere
    Else
      Debug.Print Err, Err.Description
      Resume ExitHere
    End If
  End If
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

VBSlammer

Unemployed in Houston, Texas

 

[aw23]

Thanks so much, I will take a look at that. One question on the time though. It seems to really be slowing things down. Is there anything I can do about that?

Thanks

 

[aw23]

Is there anyway I can do it with multithreading? Right now when the user loads a client, the whole form loads including the time. I'd like the page to load, without the delay. And then the time will load and appear once it is done. Currently the user can't use the form until the time is there. Is there anyway I can have the user use the form while I am getting the time?

Thanks

 

[VBslammer]

Well, now that is the downside of Web Services. For large databases I use unbound forms because that's the direction everything is going right now, considering that the .Net platform uses disconnected recordsets.

Are you using datasheet view? The key here is going to be displaying a single record at a time, or leaving the local time textbox unbound until the user decides they want to see the local time. You could put a button on the form that gets the current time when the user is ready to see it. That makes more sense anyway becuase if the user opens a record and leaves it open for a while, the time will be off when they look at it again. Providing them with a way to "Get Current Time" would be very intuitive as well.

VBSlammer

Unemployed in Houston, Texas

 

[aw23]

Thanks, but no my boss doesn't like the idea. I explained that the time will not be live and he asked me to make it live. Once I get the time, he wants me to increment it on my own.
And he still insists on multithreading.

 

[VBslammer]

The only way I know of to make an asynchronous call is to create a .dll using VB (or other language) that uses the API to monitor the thread processes, which calls the web service. There are example multi-threading projects for VB that I've experimented with, but you'd have to install the .dll on every machine that runs your application and reference it in your project. The biggest problem here is that VB6 is notoriously unstable when dealing with threads via the API.

Create The Illusion of MultiThreading:

My suggestion would be to set a TimerInterval on your form that only calls the Web Service at certain intervals based on the user's activity. For example, I would set the TimerInterval to 0 (default) at design time, so the form could load normally, then change it to 1000 (1 second) when the Form_Current() event fires, which would populate the textbox. The textbox in this scenario has to be unbound. Setting this interval to 1 second allows the user to scroll through the records without calling the Web Service, thus no lag.

The Form_Timer() event would then set the TimerInterval to 30000 (30 seconds) so any further calls would only be made in 30 second intervals, or often enough to keep the minutes current.

I've created a simple class module that raises an event telling the form when the updated time has been acquired, simplifying the UI code. After some testing, I found that the biggest performance hit occurs when the SoapClient initializes itself using the wsdl definition, not when the actual method is called. Therefore, in the class module, it only intitializes itself once, when the first method call is made. That helps the form load faster because the class doesn't do anything until the Timer makes a method call. Here's the class:

Option Compare Database
Option Explicit

[green]'********************
'*    EVENTS
'********************[/green]
Event TimeAcquired(ByVal strTime As String)

[green]'********************
'*    VARIABLES
'********************[/green]
Private mobjSClient As MSSOAPLib30.SoapClient30
Private mstrCity As String

[green]'********************
'*    CONSTRUCTOR
'********************[/green]
Private Sub Class_Initialize()
  'nothing - fast load.
End Sub

Private Sub InitSoap()
  If mobjSClient Is Nothing Then
    Set mobjSClient = New MSSOAPLib30.SoapClient30
    [green]' point the SOAP API to the web service that we want to call...[/green]
    Call mobjSClient.mssoapinit("[URL unfurl="true"]http://upload.eraserver.net/circle24/worldtime/worldtime.asmx?WSDL")[/URL]
  End If
End Sub

[green]'********************
'*    PROPERTIES
'********************[/green]
Public Property Let City(ByVal strCity As String)
  mstrCity = strCity
End Property

Public Property Get City() As String
  City = mstrCity
End Property

[green]'********************
'*    METHODS
'********************[/green]
Public Sub InitiateTimeStamp(ByVal strCity As String)
  mstrCity = strCity
  Call InitSoap
  Call GetWorldTime
End Sub

Private Sub GetWorldTime()
  Dim strTime As String
  
  If Len(Me.City) = 0 Then GoTo ExitHere
  
  [green]' call the web service[/green]
  strTime = mobjSClient.GetTime(City)
  
  [green]' this is the event that returns the time...[/green]
  RaiseEvent TimeAcquired(strTime)

ExitHere:
  On Error Resume Next
  Exit Sub
ErrHandler:
  If Not mobjSClient Is Nothing Then
    If mobjSClient.faultcode <> "" Then
      Debug.Print mobjSClient.faultstring
      Resume ExitHere
    Else
      Debug.Print Err, Err.Description
      Resume ExitHere
    End If
  End If
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

[green]'********************
'*    EOF
'********************[/green]

The form code would include a "WithEvents" declaration of the class so you can monitor the "TimeAcquired" event:

Option Compare Database
Option Explicit

Private WithEvents mWorldTime As clsWorldTime

Private Sub Form_Current()
  Me!txtCurrentTime = ""
  Me.TimerInterval = 1000
End Sub

Private Sub Form_Load()
  Set mWorldTime = New clsWorldTime
End Sub

Private Sub Form_Timer()
  Call UpdateTimeStamp
  Me.TimerInterval = 30000
End Sub

Private Sub mWorldTime_TimeAcquired(ByVal strTime As String)
  If mWorldTime.City = Me!City Then
    Me!txtCurrentTime = strTime
  End If
End Sub

Public Sub UpdateTimeStamp()
  If Len(Me!City) > 0 Then
    mWorldTime.InitiateTimeStamp Me!City
  End If
End Sub

It's not asynchronous, but on my dial-up connection it performs well. If you absolutely have to try the CreateThread() API - you should post a new question; probably in the VB6 forum.




VBSlammer

Unemployed in Houston, Texas

 

[aw23]

Wow! Thanks, looks like that really worked! I was getting an error at first every so often. Something like object with variable not set (I don't remember exactly) I played with it and I'm not sure if I got rid of it or I just haven't hit the error yet. I will let you know. Also, is it possible to go the the web service page and look up what cities there are? I went to worldtime.com thinking maybe that was the website but there seem to be lots of cities there that the web service does not have. I'd like to be able to find nearing cities to put in my database for cities that are not there.
Thanks!

 

[aw23]

OK, I got the error again
Run-time error '91':
Object Variable or with block variable not set


It stops on the following line of code:
mWorldTime.InitiateTimeStamp xCity
(xCity is a varible for the city, I call a function to get the city)
Public Sub UpdateTimeStamp()
Dim xCity As Variant
xCity = GetCity(Me.tmpnewcomp.Column(2), tmpcompany_id)
If Len(xCity) > 0 And xCity <> 0 Then
mWorldTime.InitiateTimeStamp xCity
End If
End Sub