Multi user VB6 Access Backend Database

[ChrisM]

Hi,

I have a VB6 application which has to use an MS Access database as a back
end.

I'm not really used to using Access as a back-end. Usually use SQL Server.

Anyway,
I have split the back-end Access into 2 parts, all the data: 'Tables
Database', and the queries: 'Queries Database'

If I want to make this multi-user, obviously the 'Tables Database' will
reside on the server, but should each user have their own copy of the
'Queries Database' on their PC, as well as a copy of my application, or can
each copy of the application refer to one instance of the 'Queries Database'
(ie a copy on the server).

Thanks,

ChrisM

 

[ChrisM]

It's interesting to read about everyones experiences, it would seem that on
balance it should work OK (If I'm lucky?!).
No-one has actually answered my original question yet, although by reading
between the lines, I sounds as if I should go for the MDB containing the
tables on the server, and a copy of the MDB containg the queries on each
machine that will be running the software.
Could someone please confirm that this is the best way to go.

Thanks,

ChrisM

 

[Albert D. Kallal]

Actually, the responses are interesting.

My bets for your setup? Yes, put the copy of the queries on each station
just like you do with the application. 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.

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.

 

[Jezebel]

I guess you need to hope that some of these posters who have experience with
Access working well can answer your question. Seems to me that their
responses are a trifle slippery on facts, more concerned with justification
than emprical argument; but I'm a cynical shit at the best of times. (And
after a lot of years in the IT industry, I have long stopped stopped getting
any enjoyment from saying "I told you it wouldn't work" at the
post-catastrophe conference.)

 

[Albert D. Kallal]

I guess you need to hope that some of these posters who have experience with
Access working well can answer your question.

That is fair, since it is quite clear you don't have much knowledge about
ms-access as a product.

Seems to me that their
responses are a trifle slippery on facts, more concerned with justification
than emprical argument

Actually, it seems that while you supposedly have some experience in the IT
industry, you seem to understand little, or what ms-access is. It is not a
question of being slippery..but just one of product knowledge. As mentioned,
access is only a client tool to your database engine of choice anyway.
Maybe, perhaps you don't understand client to server? When writing a two
tiered applications, ms-access is as scalable as any other development tool
in the market place. Be it VB, c++ or .net. The issue of scalability is
going to be that of what data engine you choose...and not ms-access.

; but I'm a cynical shit at the best of times. (And
after a lot of years in the IT industry, I have long stopped stopped getting
any enjoyment from saying "I told you it wouldn't work" at the
post-catastrophe conference.)

You have also seemed to lost the enjoyment of learning also. It would seem
that your views of ms-access are that of a viewpoint through a narrow straw.
The question is not one of being careful, and cynical...but at what point
you stopped learning about the product you know little about. You have just
stopped learning about this industry..and that seems very sad to me.

As for the JET engine? (which you can choose to use with ms-access, or not),
that engine is likely by far an away the most popular data engine in the
marketplace today. However, you do still seem to be confused on the issue of
ms-access, and the choice(s) of the data engines that developers now have.
You *REALLY* need to keep that in mind.

However, that JET engine still does thrive in the market place

Many Popular products have used the JET engine.

Some are:

Ms-access (now, as mentioned you don't have to use JET)
Windows NT (directory services)
Microsoft Money
Internet Information Server
Index Server
Microsoft Project.
A Jet variation even serves as the message store for Exchange Server.

Simply Accounting - a very popular accounting system, and is multi
user
CityDesk - a popular web contact management system

The above list is only a small sample (from memory!). However, I just want
to stress that you want to learn and distinguish between the data engine of
choice..and that of ms-access which is a client development tool.

You also should realize that for the last 3 versions of office there has
shipped a 100% compatible client to server sql engine for use with ms-access
(and it DOES NOT require JET). You should also note that the ms-access
developer tools are now part of Visual Studio office extensions (again..you
likely did not know this).

You obviously have some learning here to do...as it is clear ms-access is
not the product you once thought it was...

 

[david epsom dot com dot au]

between the lines, I sounds as if I should go for the MDB containing the

tables on the server, and a copy of the MDB containing the queries on each

1) SQL in a local file.
2) SQL in the target database (like SQL Server)
3) SQL in a shared file

Think of the SQL as being a kind of data, stored in a database.

Data can be stored locally or shared, and may be in the same
database as the target database, or in an independent database.

If the SQL is STATIC, or tightly bound to the APPLICATION,
it makes sense to have a local copy.

If the SQL is tightly bound to the DATA, it makes sense to
include it in the target database. If you consider the metadata
(the structure of the database) as a kind of data, it would
make sense to include SQL if it depended on the metadata.
For example, you might ship data with a varying table structure,
but always include a static interface (view) in the database
file.

If the SQL is subject to frequent change, but independent
of the APPLICATION and DATA, a shared file would be appropriate.
For example, if you were using this file to switch between
different datasources during the day, you would use a shared
file rather than a local file.

As with any kind of code or data, you may decide to partition
the SQL between the three locations. You might put a few
queries in the target database, a few in a shared database,
and a few in a local database.

Or you might decide that there are logical imperatives that
over-ride the database design. If you are planning to move
to SQL Server, you might put all of your queries in the target
database as a first step: or you might just need to get rid of
the extra file. If you have done your development in Access,
all the queries might be in a local database to start with,
and you might never get around to moving them out when you
switch to VB. And of course you might decide that splitting
your SQL into three locations was just a truly stupid idea,
even if it did match your problem domain......

(david)

 

[Unicorn]

[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.

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

 

[david epsom dot com dot au]

In the case of queries, I thought it was just the query definition

returned, until the query was actually executed.

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)

[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.

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

 

[Unicorn]

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

[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
..

 

[Tony Toews]

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.

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

 

[Tony Toews]

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

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