By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

ADO parameters queries and stored procedures - and Connections

P: n/a
I use the following code to retrieve to recordsets into two local tables,
from an mdb file over a WAN. Works fine, but I'm trying to tweak the speed,
and I have some questions:

1. I tried both an ADOX-created stored procedure and a regular Access
parameter query. After several tests, I came to the conclusion that there
was no statistcal difference between the two. Does this make sense, or
could I do something different to speed it up (the two queries are just
standard three-table joined Select queries with one parameter)?

2. When I wrap the two calls below in a third recordset, opened on the user
table, this speeds things up - in other words, when the recordset 'rs' is
closed and re-opened, the connection to the WAN mdb is lost, and has to be
re-established - which is noticeable over the WAN. Is there any other way
to keep the connection open ... and why does closing the recordset close it?

3. Any other suggestions?
-----------------------------------------------------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim cmd1 As ADODB.Command
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set cnxn = New ADODB.Connection
Set cmd1 = New ADODB.Command

cnxn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnxn.Open "Data Source=" & GetServer()

Set prm1 = cmd1.CreateParameter("PO", adVarChar, adParamInput,
Len(GetSelectedPO()))
prm1.Value = GetSelectedPO()
With cmd1
.ActiveConnection = cnxn
.CommandText = "HomeInvestigationPQuery"
.CommandType = adCmdStoredProc
.Parameters.Append prm1
End With

rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
rs2.Close

rs2.Open "HomeCaseloadTable", CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdTable
cmd1.CommandText = "HomeCaseloadPQuery"
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew
For f = 0 To 2
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop
.Close
End With
rs2.Close
cnxn.Close
Set cmd1 = Nothing
Set prm1 = Nothing
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a
Do you really have to use recordsets? Can you just use two Update
Queries?

If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update. TTBOMK the batch update is
asynchronous, so we can go about doing something else while it does its
work.

It's unnecessary to close and release ADO object pointers.

Nov 13 '05 #2

P: n/a
On 25 Jul 2005 19:09:51 -0700, "lylefair" <ly******@yahoo.ca> wrote:
Do you really have to use recordsets? Can you just use two Update
Queries?

If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update. TTBOMK the batch update is
asynchronous, so we can go about doing something else while it does its
work.

It's unnecessary to close and release ADO object pointers.


Actually, if you open the recordset as static, batch optimistic, there's no
benefit to disconnecting it. It's not going to talk to the database again
until you do the batch update, disconnected or not. The only reason to
disconnect is if you really do want to close the connection to conserver
resources.
Nov 13 '05 #3

P: n/a
Lyle Fairfield wrote:
Do you really have to use recordsets? Can you just use two Update
Queries?
Good question. I would be updating the Front End mdb from the Back End mdb,
which I have never done before. How would I do this? Wouldn't the BE have
to know where the FE is?
If recordsets then ...
Quite often when dealing with ADODB recordsets it's more efficient and
faster to open them, disconnect them (Set .ActiveConnection = Nothing),
do our work, reconnect them (Set .ActiveConnection =
YourConnectionObject) and do a batch update.
So change this ...

rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
rs2.Update
.MoveNext
Loop

.... to this? ..

rs2.Open "HomeInvestigationTable", CurrentProject.Connection, adOpenKeyset,
adLockBatchOptimistic, adCmdTable

With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

Set .ActiveConnection = Nothing
Do While Not .EOF
rs2.AddNew
For f = 0 To 5
rs2.Fields(f) = .Fields(f)
Next f
.MoveNext
Loop
Set .ActiveConnection = cnxn
rs2.UpdateBatch

It's unnecessary to close and release ADO object pointers.


I've stopped doing it for recordsets, but I wasn't sure the same applied for
command and parameter objects.


Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #4

P: n/a
Steve Jorgensen wrote:
The only reason to
disconnect is if you really do want to close the connection to conserver
resources.


I'm doing this over a WAN (The Powers said I couldn't use their SQL Server).
All I want to accomplish is make it faster, any way I can. The WAN is good
and fast, and the PCs and server involved are first rate. I just want to:

1. Not open and close the connection if preventable - this alone takes
anywhere from 1-2 seconds, so if I open and close a recordset 4 times
instead of 1 in one Save action, that's an extra 3-6 seconds I add.

2. Not send any more data TO the server than I need to to GET data.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #5

P: n/a
On Mon, 25 Jul 2005 22:58:02 -0400, "Darryl Kerkeslager"
<ke*********@comcast.net> wrote:
Steve Jorgensen wrote:
The only reason to
disconnect is if you really do want to close the connection to conserve
resources.


I'm doing this over a WAN (The Powers said I couldn't use their SQL Server).
All I want to accomplish is make it faster, any way I can. The WAN is good
and fast, and the PCs and server involved are first rate. I just want to:

1. Not open and close the connection if preventable - this alone takes
anywhere from 1-2 seconds, so if I open and close a recordset 4 times
instead of 1 in one Save action, that's an extra 3-6 seconds I add.

2. Not send any more data TO the server than I need to to GET data.


I didn't realize you were using an Access MDB over a WAN. That's generally
both slow and unreliable. Can you get set up to remote control your PC at
work via Terminal Services? Alternatively, if there are only a handfull of
simultaneous users, could you use MSDE on your computer, and access that over
the WAN?

If none of those is an option, perhaps, you should look into replication.
David Fenton may have some advice on this point.
Nov 13 '05 #6

P: n/a
A few more things

Steve points out that it's unnecessary to close the connection, if one
changes the cursor type to adOpenStatic.

You should still be able to use
Dim FieldArray As Variant
FieldArray = Array(0, 1, 2, 3, 4, 5)
rs2.Open "HomeInvestigationTable", CurrentProject.Connection,
adOpenStatic,
adLockBatchOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew FieldArray, Array(Fields(0), Fields(1), Fields(2),
Fields(3), Fields(4), Fields(5))
.MoveNext
Loop
End With
rs2.UpdateBatch
(with lines inserted in the correct areas)
To avoid iterating f

CurrentProject.Connection can be 100s of times slower than a simple ADO
connection. You might try creating an independent connection (naming
the provider, data source, etc.)

Finally, running Access code can sometimes be very slow. In your
routine there is nothing that I can see that requires Access. I'm going
to guess that running this as VB Script, or Compiled VB, or JScript in
the Wsh environment might be faster.

Finally, finally, if the connection can find these tables in the front
end and backend, then why not SQL? In SQL we can reference a table or
query in an mdb with the path to the table and the dot and the name of
the object, perhaps all enclosed with [...].
eg
With DBEngine(0)(0)
.Execute "DELETE * FROM [C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\terrawaretables.mdb].[transactions]"
.Execute "INSERT INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\terrawaretables2.mdb].[transactions] " _
& "SELECT * FROM [C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\terrawaretables.mdb].[transactions] "
End With

Nov 13 '05 #7

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:to********************************@4ax.com:
I didn't realize you were using an Access MDB over a WAN. That's
generally both slow and unreliable. Can you get set up to remote
control your PC at work via Terminal Services? Alternatively, if
there are only a handfull of simultaneous users, could you use
MSDE on your computer, and access that over the WAN?

If none of those is an option, perhaps, you should look into
replication. David Fenton may have some advice on this point.


Well, there's WANs and then there's WANs. One client of mine, a
school district in New Jersey, has a WAN connecting all the schools,
but it runs at Gigabit speeds, so it's faster than most LANs, and
thus perfectly appropriate for using Access MDBs. And, indeed, the
first app I redesigned for them is indeed being run with the data
MDB on the high school server, and the end users both at the high
school and out in the grade schools and middle schools, on the other
side of the WAN.

So, the real problem with WANs is when they are running at under
10BaseT speed.

Oh, also, wireless is unacceptable, too.

Replication is not for the faint of heart, and I would say not at
all preferable to WTS except where WTS can't be done.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8

P: n/a
> I didn't realize you were using an Access MDB over a WAN. That's
generally
both slow and unreliable.
Slow, yes, unreliable, no.
Can you get set up to remote control your PC at
work via Terminal Services? Alternatively, if there are only a handful of
simultaneous users, could you use MSDE on your computer, and access that
over
the WAN?
80 users - ?? simultaneous (perhaps 20) - so MSDE is out. But MSDE,
Terminal Services, even scripting are all out for the same reason - I'm not
IT staff, and nothing will be put in without the approval of IT staff -
which will not come. I lost my automatic backups when IT nixed scripts run
from anywhere but the servers, and I was forced to put this on the WAN when
they eliminated all file sharing. As of now, I can't even share out my
Outlook calendar.

With either IBM or Northrop Grumman getting the IT outsourcing contract
soon, there will be no positive changes for me. I'm resigned to this
solution for the next 18 months until the enterprise .Net application is
installed, making my app obsolete.
If none of those is an option, perhaps, you should look into replication.
David Fenton may have some advice on this point.


Well, my subject line contains the text "ADO", so David has doubtless
blocked the thread ;), but I will read the chapter on replication in the ADH
and ask as needed.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

See www.adcritic.com/interactive/view.php?id=5927
Steve Jorgensen wrote:

Nov 13 '05 #9

P: n/a
Lyle Fairfield wrote:

A few more things

Steve points out that it's unnecessary to close the connection, if one
changes the cursor type to adOpenStatic.

You should still be able to use
Dim FieldArray As Variant
FieldArray = Array(0, 1, 2, 3, 4, 5)
rs2.Open "HomeInvestigationTable", CurrentProject.Connection,
adOpenStatic,
adLockBatchOptimistic, adCmdTable
With rs
.Open cmd1, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
Do While Not .EOF
rs2.AddNew FieldArray, Array(Fields(0), Fields(1), Fields(2),
Fields(3), Fields(4), Fields(5))
.MoveNext
Loop
End With
rs2.UpdateBatch
(with lines inserted in the correct areas)
To avoid iterating f
Thank you - I couldn't figure out a method to avoid that, even though I knew
it would be a drag.

CurrentProject.Connection can be 100s of times slower than a simple ADO
connection. You might try creating an independent connection (naming
the provider, data source, etc.)
I only use the CurrentProject.Connection for the local mdb, and specify the
path for the WAN mdb. But I'll try specifying both, as I really need to
wring out every drop of performance.

Finally, running Access code can sometimes be very slow. In your
routine there is nothing that I can see that requires Access. I'm going
to guess that running this as VB Script, or Compiled VB, or JScript in
the Wsh environment might be faster.
Unfortunately scripting has been disabled from local PCs along with other
security features.
Finally, finally, if the connection can find these tables in the front
end and backend, then why not SQL? In SQL we can reference a table or
query in an mdb with the path to the table and the dot and the name of
the object, perhaps all enclosed with [...]. .Execute "DELETE * FROM [C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\terrawaretables.mdb].[transactions]"
.Execute "INSERT INTO " _
& "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\terrawaretables2.mdb].[transactions] " _
& "SELECT * FROM [C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\terrawaretables.mdb].[transactions] "
End With


Ahhh! I really didn't know that you could do that (probably should have,
but didn't). Yes, I would love it if I could stick with a(n) SQL solution.

Thank you both, Steve and Lyle.

Now to test out my newfound nowledge ...
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #10

P: n/a
"David W. Fenton wrote:
So, the real problem with WANs is when they are running at under
10BaseT speed.
The speed of a download from the regional server is slightly better than I
get from Comcast Internet.

Replication is not for the faint of heart, and I would say not at
all preferable to WTS except where WTS can't be done.


I have a lot to read before I'll even be able to ask an intelligent question
(so yes, there are dumb questions).

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

Nov 13 '05 #11

P: n/a
"Darryl Kerkeslager" <ke*********@comcast.net> wrote in
news:Bf********************@comcast.com:
Well, my subject line contains the text "ADO", so David has
doubtless blocked the thread ;), but I will read the chapter on
replication in the ADH and ask as needed.


I read ADO threads. ADO is useful for working with non-Jet data.

But I don't really think replication is going to help you at all --
it would only worsen the problems you're already having because of
what is obviously completely incompetent IT management, management
that is more concerned with turf and control than it is with service
to its users.

This is exactly the reason that departments start setting up their
own servers managed locally, because IT refuses to give them the
control they need, or respond to their needs.

I often wonder how it is that so many complete idiots seem to get
ahead in these departments.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #12

P: n/a
David W. Fenton wrote:
But I don't really think replication is going to help you at all --
it would only worsen the problems you're already having because of
what is obviously completely incompetent IT management, management
that is more concerned with turf and control than it is with service
to its users.


I'll take your advice on replication, and read the chapter just for
edification, then.

Although I'm one of the ones suffering from this policy, I did have a long
talk with the Software Admin about it, and he told me that there were
currently more Access apps in the Department then there are employees (tens
of thousands). These apps of course have come about because the state has
been sooo slow in implementing enterprise software (we now mainly use about
5 poorly designed 1980's mainframe databases for statewide operations). I
understand that for him, having to even consider "just a little help" with a
few hundred "mission critical" apps poorly written by some schmuck who left
6 months ago is a nightmare. There is a certain logic to "just hold our
breath and wait for the enterprise app" that is, finally being coded.

But of course, it shouldn't apply to me!
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #13

P: n/a
"Darryl Kerkeslager" <ke*********@comcast.net> wrote in
news:Wa********************@comcast.com:
"David W. Fenton wrote:
So, the real problem with WANs is when they are running at under
10BaseT speed.


The speed of a download from the regional server is slightly
better than I get from Comcast Internet.


Well, that would likely be somewhere in the range of 2-5MBps, which
I would say is well under what I'd want any of my apps running at.
In that kind of environment, I wouldn't want to be using Jet any
more. MSDE/SQL Server would be just fine, though, if you planned for
it appropriately.
Replication is not for the faint of heart, and I would say not at
all preferable to WTS except where WTS can't be done.


I have a lot to read before I'll even be able to ask an
intelligent question (so yes, there are dumb questions).


Replication is *never* an answer for insufficient bandwidth.

It's only an answer for circumstances where the sites cannot be
adequately connected full-time.

If bandwidth cannot be increased, then the answer is to go to a more
WAN-friendly datastore, and that means a server back end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #14

P: n/a
David W. Fenton wrote:
Well, that would likely be somewhere in the range of 2-5MBps, which
I would say is well under what I'd want any of my apps running at.
In that kind of environment, I wouldn't want to be using Jet any
more. MSDE/SQL Server would be just fine, though, if you planned for
it appropriately.

Ah, well, I have what I have, and if wishes were beers, I'd be drunk as a
skunk.
--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

Nov 13 '05 #15

P: n/a
Darryl,
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of experience. I
was planning on using replication over a WAN with lower-speed links
(256Kb Frame) and now you have me doubting the wisdom of that plan.
Can you elaborate on any hard-won wisdom on that point for me?

- Mark

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #16

P: n/a
Darryl,
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of experience. I
was planning on using replication over a WAN with lower-speed links
(256Kb Frame) and now you have me doubting the wisdom of that plan.
Can you elaborate on any hard-won wisdom on that point for me?

- Mark

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #17

P: n/a
"Mark Burns" <mb****@sms-hci.net> wrote

Darryl,
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of experience. I
was planning on using replication over a WAN with lower-speed links
(256Kb Frame) and now you have me doubting the wisdom of that plan.
Can you elaborate on any hard-won wisdom on that point for me?

- Mark


Not my statement - I don't know Jack about replication. This was David
Fenton's post - who apparently knows Jack well.

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #18

P: n/a
"Mark Burns" <mb****@sms-hci.net> wrote

Darryl,
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of experience. I
was planning on using replication over a WAN with lower-speed links
(256Kb Frame) and now you have me doubting the wisdom of that plan.
Can you elaborate on any hard-won wisdom on that point for me?

- Mark


Not my statement - I don't know Jack about replication. This was David
Fenton's post - who apparently knows Jack well.

--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927
Nov 13 '05 #19

P: n/a
Mark Burns <mb****@sms-hci.net> wrote in
news:0y****************@news.uswest.net:

[quoting me, unattributed:]
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of
experience. I was planning on using replication over a WAN with
lower-speed links (256Kb Frame) and now you have me doubting the
wisdom of that plan. Can you elaborate on any hard-won wisdom on
that point for me?


This is one of the things I don't like about the sites that are
repackaging and carrying this newsgroup and provided a posting
interface for it -- the posting tools don't encourage proper
quotation and attribution.

In any event, as Darryl has said, that was me, and it's the voice of
experience.

Replication is not a replacement for live data on an insufficiently
fast WAN. It *can* be a good solution for sharing data between
multiple independent sites using a WAN if latency of updates at the
various sites is not a problem.

My first major replication project was for a company that had two
offices, one in NYC and one in London, and they had a local replica
in each office that was synched twice a day from the NYC office
(before start of business and after close of business), using
indirect replication over ISDN (that was the best solution at the
time, back when a T1 could easily cost $5K/month). The NYC office
started their day with the London office's mid-day data, and the
London office started their day with up-to-date NYC data.

Given the type of application, that worked just fine, never causing
any problems, because they didn't need less latency than that.

The lowest interval you can set for a replication schedule with the
synchronizer that is controlled from Replication Manager is 15
minutes, so if you think you're going to get data fresher than that,
you're not going to be happy.

Secondly, this kind of thing only really works (frequent synchs,
that is, e.g., every 30 minutes) when your design and business rules
make it extremely unlikely that there will be any editing conflicts
between then sites. If two sites are constantly editing each others'
records, then you'll have frequent conflicts in need of resolution,
and it will need to be done before the next synch (or the next synch
can't complete, except provisionally).

So, if you're contemplating replication over a WAN as a way for two
sites to use the same data and trade changes to that data, then it's
a good idea.

If, on other hand, you're trying to connect the two sites so you can
treat them as a single site, it really isn't going to work.

With a WAN, though, Windows Terminal Server could be an excellent
candidate for solving the problem, assuming you're not having to
support extremely large numbers of users. Nowadays I don't propose
replication except to support disconnected users, such as someone
travelling with a laptop.

Hosting the Access application on Windows Terminal Server is going
to be substantially easier to administer than a replicated solution,
and keeps all that administration centrally located. In terms of
performance, it's hardly going to be noticed, even over something as
narrow as a 384K DSL pipe (I've got clients sharing such a
connection to use an Access app on WTS in another office). Indeed, I
had a broadband outage a couple of weeks ago and needed to work on a
client's app, which I maintain via WTS, and it worked just fine --
no problems whatsoever -- over V.90 dialup Internet access.

WTS is a much better solution than replication for most scenarios
where replication is being considered. If it hadn't been so
expensive back in the day (TS + Citrix was $900/seat in software
costs alone back in 1998-99, and required re-installing a completely
different version of NT Server), I never would have gotten so much
experience with replication at all!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #20

P: n/a
Mark Burns <mb****@sms-hci.net> wrote in
news:0y****************@news.uswest.net:

[quoting me, unattributed:]
Replication is *never* an answer for insufficient bandwidth.


Interesting statement. That has the ring of the voice of
experience. I was planning on using replication over a WAN with
lower-speed links (256Kb Frame) and now you have me doubting the
wisdom of that plan. Can you elaborate on any hard-won wisdom on
that point for me?


This is one of the things I don't like about the sites that are
repackaging and carrying this newsgroup and provided a posting
interface for it -- the posting tools don't encourage proper
quotation and attribution.

In any event, as Darryl has said, that was me, and it's the voice of
experience.

Replication is not a replacement for live data on an insufficiently
fast WAN. It *can* be a good solution for sharing data between
multiple independent sites using a WAN if latency of updates at the
various sites is not a problem.

My first major replication project was for a company that had two
offices, one in NYC and one in London, and they had a local replica
in each office that was synched twice a day from the NYC office
(before start of business and after close of business), using
indirect replication over ISDN (that was the best solution at the
time, back when a T1 could easily cost $5K/month). The NYC office
started their day with the London office's mid-day data, and the
London office started their day with up-to-date NYC data.

Given the type of application, that worked just fine, never causing
any problems, because they didn't need less latency than that.

The lowest interval you can set for a replication schedule with the
synchronizer that is controlled from Replication Manager is 15
minutes, so if you think you're going to get data fresher than that,
you're not going to be happy.

Secondly, this kind of thing only really works (frequent synchs,
that is, e.g., every 30 minutes) when your design and business rules
make it extremely unlikely that there will be any editing conflicts
between then sites. If two sites are constantly editing each others'
records, then you'll have frequent conflicts in need of resolution,
and it will need to be done before the next synch (or the next synch
can't complete, except provisionally).

So, if you're contemplating replication over a WAN as a way for two
sites to use the same data and trade changes to that data, then it's
a good idea.

If, on other hand, you're trying to connect the two sites so you can
treat them as a single site, it really isn't going to work.

With a WAN, though, Windows Terminal Server could be an excellent
candidate for solving the problem, assuming you're not having to
support extremely large numbers of users. Nowadays I don't propose
replication except to support disconnected users, such as someone
travelling with a laptop.

Hosting the Access application on Windows Terminal Server is going
to be substantially easier to administer than a replicated solution,
and keeps all that administration centrally located. In terms of
performance, it's hardly going to be noticed, even over something as
narrow as a 384K DSL pipe (I've got clients sharing such a
connection to use an Access app on WTS in another office). Indeed, I
had a broadband outage a couple of weeks ago and needed to work on a
client's app, which I maintain via WTS, and it worked just fine --
no problems whatsoever -- over V.90 dialup Internet access.

WTS is a much better solution than replication for most scenarios
where replication is being considered. If it hadn't been so
expensive back in the day (TS + Citrix was $900/seat in software
costs alone back in 1998-99, and required re-installing a completely
different version of NT Server), I never would have gotten so much
experience with replication at all!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.