Multi user VB6 Access Backend Database

  • Thread starter Thread starter ChrisM
  • Start date Start date
In the case of queries, I thought it was just the query definition
returned, until the query was actually executed.
Click to expand...

Yes, I agree, although even that is not trivial, since
the query is more or less stored as a record in the file.

But it does raise interesting performance questions:
if you put the SQL into a third file (instead of into
the data file or the application file), at what stage
does the overhead of using the third file become greater
/less than the overhead of using the network to get the
SQL from the data file?

However, since he seems to have already decided to
store the saved querydefs in a third file, the question
is only if he should use a shared copy or a local copy.

In this case, the question of how much demand will
be made on the hardware resolves to simpler questions:
Will the demand be less for the local file (yes) than
for a shared copy? Will the chance of file corruption
be less for a local file (yes) than for a shared copy?

(david)



Unicorn said:
[Snip]
It will be more reliable and use
somewhat less bandwidth as that mdb file don't cross the network. Yes..this
is the way to go.
Click to expand...

I didn't think the entire MDB file was dragged across the network. Just the
whole tables required for SQL to manipulate.

In the case of queries, I thought it was just the query definition was
returned, until the query was actually executed.

If I am wrong, please jump all over me, cause I have made some major
blunders in the past, I am more than capable of having this wrong.

(Do you know what happens to a busy local network when 5 users try and check
for a files status every 10th of a second. :-) I had my own DOS internally,
because of a simple algebraic error. )


Matt
Click to expand...
 
david epsom dot com dot au said:
Yes, I agree, although even that is not trivial, since
the query is more or less stored as a record in the file.
Click to expand...
[Snip]

That was the point of my comment about what happens when 5 users check the
status of a file every 1/10 of a second. Nothing is trivial.

While I am not very knowledgeable on networking, I have been advised, ( and
I accept the information), that a 10mb local network is at best only capable
of around 1.5-2 mbs of data through put with NetBEUI enabled. As I am still
working mainly with these 10mb local nets ( in one case a 2mb dish connects
sites a couple of blocks apart).

What I was really seeking was confirmation of my understanding of the
process, and you have done that thank you David.

Matt
..
 
david epsom dot com dot au said:
Possibly, but note that it is not a transactional application,
and at first glance it would appear that users only update
their own record. If the rest of the data access is read-only,
the application could be using local DAO and still only be
limited by the number of user licenses on the file server.
Click to expand...

As far as I know, the LDB for the shared tables/data database will still
limit to 255 concurrent users. The "DIY" client-server arrangment can
eliminate that problem by only having one "user" interface with the shared
tables/data database, but "heavy use" can still clog things.

A scenario of connect-do a minimal amount of work-disconnect would, of
course, vastly increase the number of potential users but, perhaps, with
some cost in performance/response on an individual access to the data.

In either case (as with Drew's application), I'm sure we are talking many
users but rather light use/access to the data itself. I am certainly not
doubting your report, nor your colleague's. I have often said that I
suspected that Jet turned out to be a far more capable "little desktop
database" than even its creators imagined, so capable, in fact, that I would
imagine it cost Microsoft some sales of SQL server.

Larry Linson
Microsoft Access MVP
 
[SNIP]. I have often said that I
suspected that Jet turned out to be a far more capable "little desktop
database" than even its creators imagined, so capable, in fact, that I would
imagine it cost Microsoft some sales of SQL server.

Larry Linson
Microsoft Access MVP
Click to expand...
I suspect, that MSDE has also cost Microsoft SQLServer sales. Their over
zealous throttling of MSDE is response to their 'blunder' over Jets
capabilities has put may people into the open source Data engine world. As
you say JET is very capable. MSDE is crippled unless a lot of planning
goes into the deployment to ensure concurrency is absolutely minimal, and
SQL queries are not complex enough to cause the governor to kick in without
reaching the second user.


Matt
 
Albert D. Kallal said:
If you like most access developers have some "auto update" routine that at
start-up checks for a new version of your software and grabs it from the
server..then just include the copying of the new mdb file with the possible
new updated queries also in that process.
Click to expand...

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Unicorn said:
I didn't think the entire MDB file was dragged across the network. Just the
whole tables required for SQL to manipulate.
Click to expand...

If the selection and sorting criteria involve an index then only those
pages containing the relevant index entries are downloaded. Then the
relevant pages containing the selected records are downloaded.

If the selection and sorting criteria can't use an index, for example
a data field which doesn't happen to have an index on it, then yes,
the entire table is downloaded to the computer.

Now yes, Jet does create temp files on the PC as required but this
does not mean that the entire table gets put in the hard drive either.

The above all stated there are other problems though. There was a
report a while back of someone who did some network sniffing who saw a
combo box data coming down the wire three times.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top