Opening Forms quickly over a slow data link.

[LittleSmudge]

I'm working with an Access 2000 database that has been split into a DataStore ( containing the tables ) and a FrontEnd ( containing the Forms, Reports etc )

Now the customer wants users on a remote site to be able to access this thing over a dial-up line so I'm doing some modifications to make the performance quicker - but what I'm doing doesn't seem to help as much as I'd expected.

Initially all the cpntrols on form frmCR were simply bound to fields in the table tblCR . When the Form was opened the entire contents of the tblCR had to go down the telecon before the Form opened properly ( and tblCR is a BIG table )

So I first made the frmCR.RecordSource = "SELECT TOP 1 * FROM tblCR" thinking that that would open quicker because the Form was only asking for the first one Record - It didn't improve things - Can anyone explain Why ?

I then tried frmCR.RecordSource = "SELECT * FROM tblCR WHERE CRId = 1" That didn't improve things either. - For the same reason I suspect.

So what do I do?
Unbind all of the controls, open an ADO recordset and then populate the controls with data from the recordset ? And then have to write to the recordset on every field's AfterUpdate ( or just when the Form changes record ).
That's almost making the remote form into a WebPage driven by ASP

Are there any other options that I've missed ?


G LS

 

[ScottMusich]

I have a few questions:

Are the remote users changing data frequently or just viewing the data? How important is it to have "Live" data?

I ask these questions becuase you could create a download process that will run and import/export new data each day (week, etc..) - you may have to add date and time fields to all tables in order to properly do this. This may take a bit to run but as they view/add/update data it will be much quicker.

We did something similar where I use to work and it worked well for the dial up user - they could then work offline! Scott Musich
Database Analyst/Programmer

 

[jgoodman00]

I have just started experimenting with the exact same thing. We have a SQL server database & I have been trying to work out how to speed up form operations.
What really surprised me was how fast the reports run over a dialup. On our database, a report of 10 pages could be produced in a few seconds, & by watching the sent/received bytes I noticed that less than 20KB of data was transferred. However, when I tried loading a form with the same information in it the amount of data transferred shot up to approx 75K. This immediately slows the process to the point of being unusable.

I have been spending a little time looking at the possiblities of stripping out information so as to minimize the amount of data transferred. Unfortunately I am thinking the same as you, that it will have to be written in ADO...

I watch this post eagerly though. It would be nice to find a simpler solution... James Goodman

 

[princeshri]

if the data is *not* to be modified, try using a snapshot cursor (in the query def) Otherwise, it will try to keep the connection open to update the recordset with changes.

If the data is to be updated, using client side cursors (you will need to open connection manually as far as I know) might elleviate some of the waiting


HTH,


Shri

 

[WildHare]

SELECT TOP 1 * FROM tblCR" thinking that that would open quicker because the Form was only asking for the first one Record - It didn't improve things - Can anyone explain Why ?

To answer this specific question, and also the next one, obviously, it's because the back end still copied the entire source dataset down to YOUR version of Access to evaluate the "TOP 1" part.

It is my understanding the other d/b engines, e.g. SQL Server, Oracle, etc WILL do the query processing on their end and ship down ONLY the result set, but not JET.

IOW, your back end NEVER does any query processing.




78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[LittleSmudge]

My "Back end" does very little processing at all Jim, but that's not an Access issue.

Unfortunately the remote users do quite a lot of updating.
One aspect of the database is staff activities/project time logging and cost allocation.

Another aspect requires a globally sequential log to be kept of records entered into the tblCR. Records can be added from any of three sites. One is local to the server. The other two are connected over telecom lines. - Hence the problems.
Even worse, once entered, any site can need to update any record in the tblCR

ScottMusich:
Your idea sounds almost like REPLICATION. Thats the next idea that we're considering.


jgoodman00:
Interesting James, Lets keep in touch on this one. We might be able to share some useful info instead of duplicating experiments.

PrinceShri:
Potentially useful lead Shri, I'll get back to you on the outcome.


G LS

 

[princeshri]

There is a better idea - use a web front end. This is what I usually reccomend for a situation like this


HTH,


Shri

 

[jgoodman00]

I agree with the web idea, but we have been experiencing problems using IIS & getting virii. As a result I am trying to steer clear of any web involvement...

I am experimenting with minimising the data transmitted in an attempt to speed up the connection. I have gotten it down to the following code, but it is far from satisfactory:

Private Sub cboRefNo_AfterUpdate()
On Error GoTo After_Err
Dim rs As Object

'Check if asset type has changed from last selection
If TargetForm = Me.cboRefNo.Column(2) Then
GoTo Skip
Else
TargetForm = Me.cboRefNo.Column(2)
With Me.SubformLoader
.SourceObject = "frmAsset" & TargetForm
.LinkChildFields = "RefNo"
.LinkMasterFields = "RefNo"
End With
End If




Skip:
'Image handling
'Only go through this routine if display images is true
If Me.chkDisplay_Images = True And Me.cboRefNo.Column(3) = True Then
Me.SubformPhotos.Visible = True
Call Form_Subform_Photos.DisplayImage
Else
Me.SubformPhotos.Visible = False
End If

'Fetch the record
Set rs = Me.Recordset.Clone
rs.Find "[RefNo] = '" & Me![cboRefNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark



After_Exit:
Exit Sub

After_Err:
MsgBox Error$

End Sub

This works very well across our LAN, but across a dialup it seems to slow terribly, even though the amount of data exchanged is minimal.

James Goodman

 

[jgoodman00]

Oops. This:
Skip:
'Image handling
'Only go through this routine if display images is true
If Me.chkDisplay_Images = True And Me.cboRefNo.Column(3) = True Then
Me.SubformPhotos.Visible = True
Call Form_Subform_Photos.DisplayImage
Else
Me.SubformPhotos.Visible = False
End If

'Fetch the record
Set rs = Me.Recordset.Clone
rs.Find "[RefNo] = '" & Me![cboRefNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

should be:
'Fetch the record
Set rs = Me.Recordset.Clone
rs.Find "[RefNo] = '" & Me![cboRefNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Skip:
'Image handling
'Only go through this routine if display images is true
If Me.chkDisplay_Images = True And Me.cboRefNo.Column(3) = True Then
Me.SubformPhotos.Visible = True
Call Form_Subform_Photos.DisplayImage
Else
Me.SubformPhotos.Visible = False
End If James Goodman

 

[princeshri]

In credit to the web front end, there are several other packages that you can use like Apache & PHP which usually works just as well if not better than IIS & PHP. Also ISM (with Win2K) is also quite good.


HTH,


Shri