Screen Update via Separate App

[AG]

Split Access 2000 application.
As a user moves through records in an app on one machine I need to display
related records on a large screen.
The user's screen will be completely different that what is displayed on the
large screen, just related.
The large screen could be a second monitor on the same machine or on a
separate machine connected to the same LAN.

I am thinking two separate applications and would like suggestions on how
best to synchronize the two apps.
The time between the user moving to a new record, and the large screen
synchronizing must be a short as possible.
So far I have thought of two approches - updating a single record in a small
table in the back end, every time user moves to a new record and having the
big screen app poll that table once every second to trigger a requery.
Or something similar using a simple text file instead of a table in the back
end.

Any other suggestions?

 

[NKTower]

Here is a technique I have used many times for inter-process communication,
where the two applications can be on the same machine, on the same LAN, or
even connected via the Internet. Use a TCP/IP socket. Once established, a
socket is a private, bi-directional link between the two appliations. To do
that you will need a socket library - you can download the free SocketWrench
package from Catalyst Software (www.catalyst.com). They have excellent
documentation in on-liine help, good examples in various platforms (.NET,
VB6, etc.) Take a look at their 'chat' sample code in the Visual sub-folder.
(it's in VB but you will be able to follow it and implement it in VBA quite
easily. Essentially the application doing the browsing sends a message with,
say, the key of the record you want to display to the remote machine. The
code to send the message should be put in the form's OnCurrent event. The
receiving machine accepts the message, retrieves the appropriate record(s)
and displayst. For example, the message could be a SQL query, or just the
WHERE clause, etc.

For same machine installation, the IP address is the loop-back address -
127.0.0.1 For other configurations you would need the actual IP addresses.

Outline for setting it up:

Receiving application:
Drag two socket controls to the form - they are invisible. Name one
sock_Listener and the other sock_Reader. Set up the Listener to "listen" to
a port of your choosing - pick a number above 1024 - let's say 3333. For
same machine, tell it to listen to network address 127.0.0.1. For other
situations you can tell it to listen to the default network address, which
the control can determine.

When a connection request comes in on that port, the sock_Listener.OnAccept
event fires. Within that event give the handle of the request to the
sock_Reader.Accept method. You have now created a bi-directonal
communications link between the two applications. From now on, any data
coming in will fire the sock_Reader.OnRead event, and your data will be
available via the sock_Reader.Read method. Leave sock_Listener live,
listening for another connection request - in case you get disconnected.

For simplicity, launch the receiving application first so that the listening
socket is ready and waiting. If not there, the sending application will
either wait, or return a time-out error - your choice - how you set the
"blocking" property.

Sending Application:
The driving application only needs one socket, let's call it sock_Sender.
Set it up with its IP address as above. Call sock_Sender.Connect to make a
connection request to the other machine's IP address and port 3333. If you
chose a "blocking" connection, the call will not return until connected or
time-out. If you chose a non-blocking connection the call will return
immediately, and the sock_Sender.OnConnect event will fire when the remote
accepts the call. From then on, you can exchange messages via the socket's
..Read and .Write events. It is all event-driven. The socket connection will
exist until you close the applications or manually close the socket.

It's fast, it's light weight, it's event-driven so the code is simple, and
you aren't going to bog the machine down with polling, temporary files, etc.

 

[Peter YangMSFT]]

Hello,

I agree that using tcp/socket directly is a good option. You could also
consider other Inter-processes communication (IPC) such such as Named
pipe/shared memory methods to do similar job.

Also, shared file/table with polling is also an good option here since it's
only shared by 2 processes. I think in this case, it might be easier to
implement in this method than IPC ones.

If you have any other comments or questions, please feel free to let's
know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[AG]

Thanks Peter,

NkTower was kind enough to send me good sample code, so tcp might be a good
solution. I have to see how client feels about third party installs. Some
don't like it.

I set up a shared table as a demo, and it is a bit sluggish. Even though the
polling interval is one second, it sometimes takes 4-5 seconds for the
update, even with both apps and back end on the same machine. I am guessing
that Access is caching something somewhere, but don't know where. When
saving to the shared table, I have tried DBEngine.Idle dbRefreshCache as
well as using a transaction and committing with dbForceOSFlush option.

I haven't tried shared file yet.

Can you point me to any samples on IPC?

 

[Peter YangMSFT]]

Hello,

Regarding the dealy of Access update, based on my experience, it may be
related to cursortype/locktype besides cache option you mentioned. You may
want to try the following code to see if it help. Also, the shared file
shall not have this kind of issue.


Set .ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = "tbls"
.Open


As for IPC sample, I was not able to provide a thorough sample solution for
your situation, but you could find Platform SDK example on following if you
have platform SDK installed.

C:\Program Files\Microsoft Platform SDK\Samples\WinBase\IPC\NamePipe

You may want to refer to the following link for more related information
about IPC.

http://msdn2.microsoft.com/en-us/library/aa365574.aspx

Also, the following article is for VB, VB.Net but the code might be similar
in VBA.

http://support.microsoft.com/kb/177696
How To Use Named Pipes in a Visual Basic 32-bit Program

http://support.microsoft.com/kb/871044
How to use named pipes for interprocess communication in Visual Basic .NET
or in Visual Basic 2005

If you have any further questions or concerns, please feel free to let's
know. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[AG]

Peter,

I am using DAO (Jet), not ADO.
Using an 'Update' sql to update the table on the first app.
In the second app a form is bound (recordsettype = snapshot) to the table
and I am requerying it.

 

[Peter YangMSFT]]

Hello,

Thank you for your reply. To understand the situation better, will you send
the sample mdbs you use to me so that I could look into the code? You may
remove "online" from my displayed email address. I look forward to your
update.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Peter,

The actual databases are too complex to send, and I haven't had time to
create scalled down versions.
However, here is the code I am using.
*** App_A***

Option Compare Text
Option Explicit

Dim mdbS As DAO.Database

Private Sub Form_Current()
On Error GoTo ErrLine
Call LotLink
ExitLine:
On Error Resume Next
Exit Sub

ErrLine:
If Err.Number <> ERR_COMMAND_CANCELLED Then
Call ReportError("Form_frmEnterWinningBidsTs.Form_Current")
End If
Resume ExitLine
End Sub

Private Sub LotLink()

Dim strSql As String

On Error GoTo ErrLine
If TypeName(mdbS) = "Nothing" Then
Set mdbS = CurrentDb
End If
strSql = "Update tblAuctioneerLot " _
& "SET AuctionID = " & Me!AuctionID & ", AuctionNo = """ &
Me!AuctionNo & """, LotId = " & Me!LotID & ", LotNoDec = " & Me!LotNoDec _
& " Where ALID = 1"
mdbS.Execute strSql, dbFailOnError
If mdbS.RecordsAffected = 0 Then
strSql = "Insert Into tblAuctioneerLot ( ALID, AuctionID,
AuctionNo, LotID, LotNoDec) " _
& "Values (1, " & Me!AuctionID & ", """ & Me!AuctionNo & """, " &
Me!LotID & ", " & Me!LotNoDec & ")"
mdbS.Execute strSql, dbFailOnError
End If

ExitLine:
On Error Resume Next
Exit Sub

ErrLine:
If Err.Number <> ERR_COMMAND_CANCELLED Then
Call ReportError("Form_frmEnterWinningBidsTs.LotLink")
End If
Resume ExitLine
End Sub

*** App_B***

This is the form that needs to display data based on the current record in
App_A.
The form that the below code runs from is bound to tblAuctioneerLot and it's
TimerInterval is set to 1000 and RecordsetType = snapshot..

Private Sub Form_Timer()
Me.Requery
End Sub

Private Sub Form_Current()
me!subform1.Form.Requery 'two subforms contain the actual data to be
displayed.
me!subform2.Form.Requery
End Sub

 

[Peter YangMSFT]]

Hello,

Sorry for the late response. I tried to build 2 test mdb files by using
similar code you provided but I cannot reproduce the issue. The update of
one application is displayed on the second one without delay.

I have included my test databases in a zip file and send it to you to test.

If the issue still occurs, you may try to change the registry key value
under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0

PageTimeout 1000
MaxBufferSize to 512


153046 PRB: Shared Data Update Delayed by 5 Seconds in Jet 3.x
http://support.microsoft.com/default.aspx?scid=kb;EN-US;153046

If you have any update, please feel free to let's know.
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[AG]

Thanks Peter,

I see the same delay using your files.
Changing the registry settings made a definite improvement, but I am not
sure this will be practical on the client's machines.
It would be much better not to have to depend on a registry setting.

What I don't understand is that the KB article also states:
NOTE: Starting with DAO 3.5, you can use the Idle method with the
dbRefreshCache parameter to perform the same function as the PageTimeout.
See the online DAO 3.5 SDK documentation for more information. This method
is preferred because changing the registry entry affects all applications.

The KB also states that it may affect performance, so it would be best to
limit use of the short timeout to only this application.

I had previously tried using DBEngine.Idle dbRefreshCache before the requery
action as well as after writing the new values and it made no difference.
Why would that not work?

 

[AG]

Peter,

On further investigation, setting the two values via DbEngine.SetOption
method solves the problem and does not change the registry settings.

 

[Peter YangMSFT]]

Hello,

Great to see you solved the issue! Your experience will benefit the
community.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================