Hi all

Iím sure this is an old question but Iíve scrolled back through old posts
a fair way and not really seen a discussion on this.

Iím about to work on a new VB6 project. Its primarily an internal order entry
system for a team of about 20 sales people. The db will be SQL Server 7.
Iím experienced with VB5 although mainly on the server side and Iím quite
familier with db design and normalization etc. Iíve worked through phase
1 and 2 of the MSDN Duwamish books application. I will continue with the
other phases as soon as I get VB Enterprise although the phase 2 or 3 example
seems to fit our requirements. I donít think we need to go to a multi tier
system (it will be logically separated but not physically distributed with
objects on the server). Iíve also studied the relevant ADO documentation.
I like the way Duwamish is structured with an abstraction layer of objects
and collections and the UI only dealing with these objects rather than SQL
statements, bound controls etc.

Iíd appreciate any good book recommendations etc on ĎVB in the real world
with real data quantitiesí and / or comments on the following. I realize
there are probably no hard and fast answers.

Do people use the Duwamish 1,2 and 3 type architectures in real world apps?
Iím concerned that the VB collection object is said to be slow. Some of the
Duwamish techniques, if followed blindly, wonít work in the real world. eg,
they load all their catalog table into a VB collection and then into a grid.
Thatís fine for 500 items but not for a million. That is easily fixed by
careful control of what the user is allowed to do (eg they must enter search
criteria and then hit search rather than controls defaulting to Ďeverything
firstí).

Do people use the VB6 DataEnvironment in the real world? It doesnít look
too bad if its used simply as a box of ADO recordsets ready to be ĎOpenedí
when needed. Having said that, I find it hard to see great advantage to it
over simply creating dynamic SQL statements in code to create recordsets
in the appropriate layer (not the UI). Iíd probably use the nice gui tools
to help write SQL and then copy and paste.

In a non distributed system like this where all code is on the client, what
is the recommendation for database server logins? Should everyone have their
own SQL Server user id? The app will need to implement its only security
since different groups of users will be able to do different things within
the app but there is a chicken and egg situation if we want user ids and
passwords to be stored in a db table. We need to connect to read the table
so should we have a generic login for everyone embedded in the app which
can do anything the app needs to do or perhaps an Ďauthentication accountí
which can only read the user table and then everyone logs in with their own
SQL Server account. Thinking out loud here Ö. perhaps we only have SQL server
user ids and nothing else. When a user logs into the app we try to create
an ADO connection and trap the error if not authenticated. But Ö thinking
into the future Ö offline, disconnected use might be a good idea one day
and that would make this difficult.

Thanks
Ross