Best Way to Get At Data in Another mdb

[Paul Wagstaff]

Hi All

Can I have some feedback on the best way to access data held in an mdb on a
separate server over a LAN (and WAN for that matter). I currently use DoCmd
to bring in the relevant tables linked to the external mdb & (tdf.delete to
remove 'em when I'm done). I then use standard CurrentDB, db.OpenRecordset
(strSQL), stuff to get at the treasure therein (being: 2 tables (1 - M) of
c.1250 records comprising c.10 fields) . I'll need to write back to these
external tables - but will probably be able to get away with an update query
as I'm datestamping the records with an action undetaken in my local mdb

I've never attempted ODBC /DSN and wondered whether I ought to getting stuck
into this instead of my tried and trusted method? I'm currently in Acc 97 /
Win NT 4.0 but moving to Acc 03 runtime Win Xp next year (if that alters
anything). I basically want to do tha right thing by way of performance,
network traffik etc

TIA
Paul

[David W. Fenton]

"Paul Wagstaff" <pa**********@blueyonder.co.uk> wrote in
news:LO*****************@fe3.news.blueyonder.co.uk :

Can I have some feedback on the best way to access data held in an
mdb on a separate server over a LAN (and WAN for that matter). I
currently use DoCmd to bring in the relevant tables linked to the
external mdb & (tdf.delete to remove 'em when I'm done). I then
use standard CurrentDB, db.OpenRecordset (strSQL), stuff to get at
the treasure therein (being: 2 tables (1 - M) of c.1250 records
comprising c.10 fields) . I'll need to write back to these
external tables - but will probably be able to get away with an
update query as I'm datestamping the records with an action
undetaken in my local mdb
Sounds to me like you're doing an awful lot of work that's
unnecessary. The Query builder allows you to write queries against
data stored in another MDB without needing to link tables (see the
query properties for the SOURCE DATABASE property). You can write
SQL that has the IN clause pointing to the other MDB and use that as
the recordsource of a form, so that you could edit the data
directly, rather than using DAO.

Of course, either method is only appropriate on a LAN. You just
can't do it safely on a WAN at all.
I've never attempted ODBC /DSN and wondered whether I ought to
getting stuck into this instead of my tried and trusted method?
I'm currently in Acc 97 / Win NT 4.0 but moving to Acc 03 runtime
Win Xp next year (if that alters anything). I basically want to do
tha right thing by way of performance, network traffik etc

You can't use ODBC from Access to get to Jet data -- it's not
permitted.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Darryl Kerkeslager]

"David W. Fenton" <dX********@bway.net.invalid> wrote

Of course, either method is only appropriate on a LAN. You just
can't do it safely on a WAN at all.

Can. Have. Although its been a long five months making changes and
testing, I can now safely say that an Access FE/Jet BE can be made to
perform well over a WAN. This is of course, my experience only, but with
around 80 users at three sites, no use of linked tables, making well over
1000 connections/saves/queries to the db per day, the problems are reduced
to a small number. All resolve into either my own coding errors, or "Server
busy" (about 8 per day); there are no issues with loss of data or
corruption.

I wouldn't recommend it, of course, (I was forced into it), but it will work
with sufficient care. Performance is good - less than 1/2 second for most
updates to the server - and only noticeably slow for when running reports of
large data sets.

--
Darryl Kerkeslager

[Larry Linson]

"Darryl Kerkeslager" wrote

Of course, either method is only appropriate on a LAN. You just
can't do it safely on a WAN at all.
Can. Have. Although its been a long five months making changes and
testing, I can now safely say that an Access FE/Jet BE can be made to
perform well over a WAN. This is of course, my experience only, but with
around 80 users at three sites, no use of linked tables, making well over
1000 connections/saves/queries to the db per day, the problems are

reduced to a small number. All resolve into either my own coding errors, or "Server busy" (about 8 per day); there are no issues with loss of data or
corruption.
Verrrrrry interesting! You are the first "regular" here to document that. I
suspect your WAN is likely faster than some others. But it is good to know,
if one is "forced into" a situation where it is the only alternative, as was
your case.
I wouldn't recommend it, of course, (I was forced into it), but it will work with sufficient care. Performance is good - less than 1/2 second for most updates to the server - and only noticeably slow for when running reports of large data sets.

I've seen "noticeably slow" on reports of large recordsets on a 4 MBPS LAN.

[Darryl Kerkeslager]

"Larry Linson" <bo*****@localhost.not> wrote

Verrrrrry interesting! You are the first "regular" here to document that.
I suspect your WAN is likely faster than some others. But it is good to
know, if one is "forced into" a situation where it is the only
alternative, as was your case.

Unfortunately, I haven't been able to find an easy way to measure the real
world speed of the connection, but I can put it in perspective: I also
connect over VPN through Comcast, and have found this connection to be
marginally slower than the office WAN.

--
Darryl Kerkeslager

[David W. Fenton]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in
news:AJ******************************@comcast.com:

"David W. Fenton" <dX********@bway.net.invalid> wrote

Of course, either method is only appropriate on a LAN. You just
can't do it safely on a WAN at all.
Can. Have. Although its been a long five months making changes
and testing, I can now safely say that an Access FE/Jet BE can be
made to perform well over a WAN. This is of course, my experience
only, but with around 80 users at three sites, no use of linked
tables, making well over 1000 connections/saves/queries to the db
per day, the problems are reduced to a small number. All resolve
into either my own coding errors, or "Server busy" (about 8 per
day); there are no issues with loss of data or corruption.

Er, how does "server busy" come up in an all-Jet Access application?
I wouldn't recommend it, of course, (I was forced into it), but it
will work with sufficient care. Performance is good - less than
1/2 second for most updates to the server - and only noticeably
slow for when running reports of large data sets.

If you're running a server back end, of course it's safe.

But if it's Jet for the back end, it's never going to be safe.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Darryl Kerkeslager]

"David W. Fenton" <dX********@bway.net.invalid> wrote

Er, how does "server busy" come up in an all-Jet Access application?
That's what my MsgBox says :) The actual errors are various - "Unrecognized
database format", etc- but I include code that lets me know exactly when the
error occurred, and it is at the line of code that opens the database
connection.
If you're running a server back end, of course it's safe.

But if it's Jet for the back end, it's never going to be safe.

Well, I don't know exactly what you mean by safe, then. I'm not running a
hospital or bank on this app; so I'm not risking a patient's life or
livelihood. And yes, I realize that it *can* in fact crash on the BE, and
we could lose and have to recreate a days work - however, it is "safe
enough", which is a line that you have to draw somewhere for most any
database.

--
Darryl Kerkeslager

[Pachydermitis]

mmm. . . me neither. Having to do something similar I set up a VPN -
while muttering curses under my breath, hair pulling, etc. I found the
only way that performance was feasible on forms with even moderate
recordsets was to avoid all the native Access criteria methods like the
plague (subforms being the worst), and have a local table (I lovingly
call tSettings). Say I have a list on a form that finds a record, when
the user clicks on the list, I write the criteria to the settings
table. The form's recordsource is limited to the setting so I just
refresh. Only the needed records are pulled. The biggest performance
gains were from turning off subdatasheets - huge gains.
Larry, I see you around a lot, giving us fellow gearheads good advice,
do you have any tips?
Pachydermitis

[Darryl Kerkeslager]

This is a Probation&Parole database. In most cases, an officer/secretary
works on one offender at a time.

Each form is bound to a local table, which mirrors the BE table. When the
offender is to be edited, all the records pertaining to that offender only
are copied to the local FE.

A Save operation essentially updates the BE using the FE data, or inserts a
new record in the BE and copies the new id# to the FE. Only one offender
table at a time is modified for most operations.

All SQL done with ADO Connections; no linked tables.

Almost all other tables not directly related to offenders (lookups, mostly)
are stored locally and on the BE. They rarely if ever change.


--
Darryl Kerkeslager

[Paul Wagstaff]

Thanks David - I'll look into using IN (it never ocurred to me!).

If I can ask 2 related questions: why would one prohibit using Access over
a WAN, apart from risks to the integrity of the data/databse itself, could
anything bad happen? and,

Our org. insists on no more than *15* concurrent (by which, they mean
simultaneous) connections to an Access database - I find this to be too
restrictive and often exceed this figure without any noticable detrimental
effects. So, what are the risks if one was to exceed this figure? Is there a
linear trade-off between performance (to the database and/or network) and
the number of users having a database open? I want to push my employer
into taking a more relaxed approach to both operating over the WAN ('we'll
own this risk - such as it is') and especially increasing the number of
simultaneous accesses, and could do with some expert feedback to respond to
the bureacratic 'Non' which I can hear even as I post this question.

As ever, I'm grateful for any and all advice.

Paul.


"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Paul Wagstaff" <pa**********@blueyonder.co.uk> wrote in
news:LO*****************@fe3.news.blueyonder.co.uk :

Can I have some feedback on the best way to access data held in an
mdb on a separate server over a LAN (and WAN for that matter). I
currently use DoCmd to bring in the relevant tables linked to the
external mdb & (tdf.delete to remove 'em when I'm done). I then
use standard CurrentDB, db.OpenRecordset (strSQL), stuff to get at
the treasure therein (being: 2 tables (1 - M) of c.1250 records
comprising c.10 fields) . I'll need to write back to these
external tables - but will probably be able to get away with an
update query as I'm datestamping the records with an action
undetaken in my local mdb

Sounds to me like you're doing an awful lot of work that's
unnecessary. The Query builder allows you to write queries against
data stored in another MDB without needing to link tables (see the
query properties for the SOURCE DATABASE property). You can write
SQL that has the IN clause pointing to the other MDB and use that as
the recordsource of a form, so that you could edit the data
directly, rather than using DAO.

Of course, either method is only appropriate on a LAN. You just
can't do it safely on a WAN at all.

I've never attempted ODBC /DSN and wondered whether I ought to
getting stuck into this instead of my tried and trusted method?
I'm currently in Acc 97 / Win NT 4.0 but moving to Acc 03 runtime
Win Xp next year (if that alters anything). I basically want to do
tha right thing by way of performance, network traffik etc

You can't use ODBC from Access to get to Jet data -- it's not
permitted.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Darryl Kerkeslager]

> If I can ask 2 related questions: why would one prohibit using Access

over a WAN, apart from risks to the integrity of the data/databse itself,
could anything bad happen? and,

The simple reason is that *many* Access apps are poorly written, and end up
pulling a lot of data over the server. No, it doesn't at all need to be
this way, but there are probably 20 apps in use for every copy of Access
purchased, and the bulk of them are written by people who have never heard
of CDMA ;) and have no idea to separate BE/FE, or use queries to limit data.
Think about pulling 10k records over the WAN every time a user wants to see
one record, and multiply that for 10 users many times a day, and you can
easily see how that could slow down all those important emails and people's
Internet radio.

Can anything else bad happen? Yeah, users complaining to the 'real' IT guys
that "the network is slow" and "why can't you make our network faster?"

--
Darryl Kerkeslager

[David W. Fenton]

"Paul Wagstaff" <pa**********@blueyonder.co.uk> wrote in
news:i7*******************@fe2.news.blueyonder.co. uk:

If I can ask 2 related questions: why would one prohibit using
Access over a WAN, apart from risks to the integrity of the
data/databse itself, could anything bad happen?

To update the back end, even via ADO, even if you're updating 1
record, substantial parts of the BE MDB have to be pulled across
the
WAN. THat means the headers of that BE are opened across the wire.

Now, the risk here depends on the stability and bandwidth of the
WAN. I have a client with a Gigabit WAN running over dedicated
fiber
and would never worry about running even the most poorly-designed
Access app across that WAN (indeed one of the two apps I've been
involved with was one I took over that was very badly designed, and
I've never altered most of the problems, since it was working well
enough, after I split the thing and gave all the users their own
front ends).

But to me, because the most important part of the MDB has to be
pullsed across the wire, I'd be very wary of doing anything, even
quick updates, across a WAN.

But by WAN, I'm talking about something that is less than half the
bandwidth of 10BaseT and not as stable as 10BaseT.

There are lots of WANs these days that are faster and more stable
than some of the LANs of 10-15 years ago (remember Ethernet over
coax? What was that, 4.xomething Mbps?).

To me, it's often more cost-effective in a WAN environment to set
up
a Terminal Server and run the Access app from there than it would
be
to completely re-engineer the app to be unbound to the back end.
That kind of unbound app basically takes away all the advantages of
using Access in the first place. Running on TS would also be faster
for the end users, and easier to administer, while being less risky
with the data. When you add up all the costs of engineering to run
across the WAN, and include the potential damage if something goes
wrong, Terminal Server wins hands down.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc