Using Access+DBMS with >30 users

[Remco Groot Beumer]

Hello,

I'm trying to decide if the following situation would be workable:

Generate an MS Access Front End (which will run localy on client computers),
which will link to a DBMS (SQL server or Oracle). As far as I know there
will be approx. 30 till 40 users max working on the database. I think
approx. 25 users will actually enter data (others will be mostly reporting
and viewing).
I don't really know the network capacity.

Another possibilty would be to create this front end in a VB.NET
environment, using also a DBMS. This only will cost approx 30% more time to
build (since we do not have a lot of components ready for use in this
environment).

I've red a lot of different issues on performance of an access database, but
they mostly focus on the situation with an Access back end.
Can anybody give me some argumentation on this topic or some pros and cons
for the Access versusVB option.

Thanx ,

Remco GB

[Trevor Best]

Re: Using Access+DBMS with >30 users

Remco Groot Beumer wrote:[color=blue]
> Hello,
>
> I'm trying to decide if the following situation would be workable:
>
> Generate an MS Access Front End (which will run localy on client computers),
> which will link to a DBMS (SQL server or Oracle). As far as I know there
> will be approx. 30 till 40 users max working on the database. I think
> approx. 25 users will actually enter data (others will be mostly reporting
> and viewing).
> I don't really know the network capacity.
>
> Another possibilty would be to create this front end in a VB.NET
> environment, using also a DBMS. This only will cost approx 30% more time to
> build (since we do not have a lot of components ready for use in this
> environment).
>
> I've red a lot of different issues on performance of an access database, but
> they mostly focus on the situation with an Access back end.
> Can anybody give me some argumentation on this topic or some pros and cons
> for the Access versusVB option.[/color]

I think your 30% more time to develop in VB is very optimistic.

Access -> SQL Server * 40 users = no problem performance wise. IME.

--
[Oo=w=oO]

[martin]

Re: Using Access+DBMS with >30 users

Access works great if you can count the users on one hand. SQL will save
you migration pains if you do it from the start.


"Remco Groot Beumer" <nospam@nospam. com> wrote in message
news:d38341$886 $1@news6.zwoll1 .ov.home.nl...[color=blue]
> Hello,
>
> I'm trying to decide if the following situation would be workable:
>
> Generate an MS Access Front End (which will run localy on client
> computers),
> which will link to a DBMS (SQL server or Oracle). As far as I know there
> will be approx. 30 till 40 users max working on the database. I think
> approx. 25 users will actually enter data (others will be mostly
> reporting
> and viewing).
> I don't really know the network capacity.
>
> Another possibilty would be to create this front end in a VB.NET
> environment, using also a DBMS. This only will cost approx 30% more time
> to
> build (since we do not have a lot of components ready for use in this
> environment).
>
> I've red a lot of different issues on performance of an access database,
> but
> they mostly focus on the situation with an Access back end.
> Can anybody give me some argumentation on this topic or some pros and cons
> for the Access versusVB option.
>
> Thanx ,
>
> Remco GB
>
>[/color]

[Cor Ligthert]

Re: Using Access+DBMS with &gt;30 users

Trevor,

[color=blue]
> I think your 30% more time to develop in VB is very optimistic.
>[/color]
You make me curious, have you any reference where you have based this
"thinking" on?

(I assume that you mean with VB, VBNet because there was the question
about).

Cor

[Trevor Best]

Re: Using Access+DBMS with &gt;30 users

Cor Ligthert wrote:[color=blue]
> Trevor,
>
>
>[color=green]
>>I think your 30% more time to develop in VB is very optimistic.
>>[/color]
>
> You make me curious, have you any reference where you have based this
> "thinking" on?
>
> (I assume that you mean with VB, VBNet because there was the question
> about).[/color]

Access has a lot of features for RAD that are not present in VB or
(AFAIK) VB.Net, e.g. in Access you bind a form to a recordsource and
drag fields onto the form, this gives a textbox bound to a column and a
label (hopefully with a decent bit of text in it), in VB you drag a
textbox onto a form, then a label then you have set properties and/or
write code to get that textbox to interact with an underlying column in
a recordsource.

So for a say a form with 20 fields in, you get something up and running
in about a minute in Access, that's a form that can display/edit/delete
and find records. How long will a form with that functionality take in
VB? Bit longer than 30% more than in Access.

--
[Oo=w=oO]

[Trevor Best]

Re: Using Access+DBMS with &gt;30 users

martin wrote:[color=blue]
> Access works great if you can count the users on one hand.[/color]

That'll be 31 then (counting in binary) :-)

--
[Oo=w=oO]

[Cor Ligthert]

Re: Using Access+DBMS with &gt;30 users

Trevor,
[color=blue]
>
> Access has a lot of features for RAD that are not present in VB or (AFAIK)
> VB.Net, e.g. in Access you bind a form to a recordsource and drag fields
> onto the form, this gives a textbox bound to a column and a label
> (hopefully with a decent bit of text in it), in VB you drag a textbox onto
> a form, then a label then you have set properties and/or write code to get
> that textbox to interact with an underlying column in a recordsource.
>
> So for a say a form with 20 fields in, you get something up and running in
> about a minute in Access, that's a form that can display/edit/delete and
> find records. How long will a form with that functionality take in VB? Bit
> longer than 30% more than in Access.
>[/color]
Do you know how a datagrid in Net works?

Cor

[Rick Brandt]

Re: Using Access+DBMS with &gt;30 users

martin wrote:[color=blue]
> Access works great if you can count the users on one hand. SQL will
> save you migration pains if you do it from the start.[/color]

The OP was talking about an Access front end to a SQL Server or Oracle back end.
In that scenario your comment is meaningless.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Re: Using Access+DBMS with &gt;30 users

Cor Ligthert wrote:[color=blue]
> Trevor,
>[color=green]
> >
> > Access has a lot of features for RAD that are not present in VB or
> > (AFAIK) VB.Net, e.g. in Access you bind a form to a recordsource
> > and drag fields onto the form, this gives a textbox bound to a
> > column and a label (hopefully with a decent bit of text in it), in
> > VB you drag a textbox onto a form, then a label then you have set
> > properties and/or write code to get that textbox to interact with
> > an underlying column in a recordsource. So for a say a form with 20 fields
> > in, you get something up and
> > running in about a minute in Access, that's a form that can
> > display/edit/delete and find records. How long will a form with
> > that functionality take in VB? Bit longer than 30% more than in
> > Access.[/color]
> Do you know how a datagrid in Net works?
>
> Cor[/color]

Yeah, very poorly. Grids (in general) suck. They are a horrible way to present
data with the exception of detail rows in a master/detail situation or perhaps
as a pick-list. I would use one as the primary interface with only the simplest
of data structures. Web-enabled front ends have swamped users with grid
interfaces because they are the nearest thing to a RAD solution that most
development environments offer.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Nick Malik [Microsoft]]

Re: Using Access+DBMS with &gt;30 users

When using an Access front end to SQL Server or Oracle, Access is not a
consideration for the number of users, because the Access front end is being
used by ONE user (the front end is installed directly on the client).
Having that front end located on a server and called from the client makes
no difference. Each front end runs seperately from the others. You could
have 1,000 users if you want, all using an Access front end on a SQL Server
or Oracle database.

IMHO, the reason that many developers tend to shy away from Access has more
to do with the structure of code in the Access environment. Access is a
very RAD environment, which means that doing straight-forward things is very
easy, but doing anything complicated or outside the expectations of the
designers takes more time than it would in another language. There is a
point of diminishing returns with highly RAD environmnents in terms of
developer productivity.

If you are an experienced OO developer, you can probably create a front end
in VB.Net or C# is only 30% more time than in Access, so Access will appear
more performant at first. However, if you want many of the features of
..Net: easy interoperabilit y, Object orientation, Frameworks for updating the
client environments, clean mechanisms for security, encryption, XML, Web
Services, and other features, then Access will QUICKLY become a liability.

In addition, if you allow customers to access your database from Access,
they will be able to access the database from Excel and any ODBC connection
as well. Access pretty much requires full access to the table-level data
(as opposed to using a stored procedure interface). Speaking personally, in
large environmnets, this is simply not allowed for security and data control
purposes. Many IT security specialists would turn you down cold if you
suggested this option.

My advice: Create a front end in Access if you need to put together a front
end for simple use, for a small number of users, or for a proof of concept
in an internal environment (Intranet). Let your first interface be Access
to get something in front of the users, so that they can give you better
requirements. This would be an agile approach.

Then, following behind that, create a replacement interface in VB.Net or C#.

Bottom Line: If you want to create an interface that you will maintain and
keep running for more than a year, or you need real developer productivity
when using common features of modern systems, or you need to integrate the
application with other systems, then write your front end in VB.Net or C#.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Remco Groot Beumer" <nospam@nospam. com> wrote in message
news:d38341$886 $1@news6.zwoll1 .ov.home.nl...[color=blue]
> Hello,
>
> I'm trying to decide if the following situation would be workable:
>
> Generate an MS Access Front End (which will run localy on client
> computers),
> which will link to a DBMS (SQL server or Oracle). As far as I know there
> will be approx. 30 till 40 users max working on the database. I think
> approx. 25 users will actually enter data (others will be mostly
> reporting
> and viewing).
> I don't really know the network capacity.
>
> Another possibilty would be to create this front end in a VB.NET
> environment, using also a DBMS. This only will cost approx 30% more time
> to
> build (since we do not have a lot of components ready for use in this
> environment).
>
> I've red a lot of different issues on performance of an access database,
> but
> they mostly focus on the situation with an Access back end.
> Can anybody give me some argumentation on this topic or some pros and cons
> for the Access versusVB option.
>
> Thanx ,
>
> Remco GB
>
>[/color]

[David W. Fenton]

Re: Using Access+DBMS with &gt;30 users

"martin" <de_lange@xtra. co.nz> wrote in
news:bIN5e.1725 7$1S4.1736505@n ews.xtra.co.nz:
[color=blue]
> Access works great if you can count the users on one hand. SQL
> will save you migration pains if you do it from the start.[/color]

I would say up to 20 or 30 users is quite doable with Jet, as long
as you know how to design your application.

But if you're starting with more than 30 users, yes, you're better
off with a server back end.

Anyone who can't code an Access app for 5-10 users shouldn't be
coding Access apps at all.

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

[David W. Fenton]

Re: Using Access+DBMS with &gt;30 users

"Nick Malik [Microsoft]" <nickmalik@hotm ail.nospam.com> wrote in
news:wtudnRcsNI Q01cXfRVn-jw@comcast.com:
[color=blue]
> In addition, if you allow customers to access your database from
> Access, they will be able to access the database from Excel and
> any ODBC connection as well. Access pretty much requires full
> access to the table-level data (as opposed to using a stored
> procedure interface). Speaking personally, in large environmnets,
> this is simply not allowed for security and data control purposes.
> Many IT security specialists would turn you down cold if you
> suggested this option.[/color]

It sounds to me like your knowledge of Access pre-dates the release
of Access 2000.

Note ADO, not ODBC.

Note ADPs as alternative to MDBs.

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

[Nick Malik [Microsoft]]

Re: Using Access+DBMS with &gt;30 users

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns9633D69 784143dfentonbw aynetinvali@24. 168.128.78...[color=blue]
> "Nick Malik [Microsoft]" <nickmalik@hotm ail.nospam.com> wrote in
> news:wtudnRcsNI Q01cXfRVn-jw@comcast.com:
>[color=green]
>> In addition, if you allow customers to access your database from
>> Access, they will be able to access the database from Excel and
>> any ODBC connection as well. Access pretty much requires full
>> access to the table-level data (as opposed to using a stored
>> procedure interface). Speaking personally, in large environmnets,
>> this is simply not allowed for security and data control purposes.
>> Many IT security specialists would turn you down cold if you
>> suggested this option.[/color]
>
> It sounds to me like your knowledge of Access pre-dates the release
> of Access 2000.
>
> Note ADO, not ODBC.
>
> Note ADPs as alternative to MDBs.
>[/color]


I agree that my knowledge of Access is probably neither current nor expert.
I defer to your opinion, although I wonder if I could encourage you to
provide advice to the original poster about their question. Do you believe
that he should use Access to create the front end or should he create the
front end using VB.Net?

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

[Larry Linson]

Re: Using Access+DBMS with &gt;30 users

"Nick Malik [Microsoft]" wrote
[color=blue]
> Do you believe that he should use
> Access to create the front end or
> should he create the front end using
> VB.Net?[/color]

Access is an excellent client to server databases. I've used it with
Microsoft and Sybase SQL Servers, Sybase SQL Anywhere, and Informix. In one
case, we had between 175 - 200 users of an Access 2.0 client to Informix
before I left the project.

If it is client-server, I'd strongly suggest Access for developing the
client application; if it is a web-based application, then VB.NET may be a
useful choice (but so might several other things: Front Page with the
Database Interaction Wizard, third-party development tools like cold fusion,
etc.).

Larry Linson
Microsoft Access MVP

[Cor Ligthert]

Re: Using Access+DBMS with &gt;30 users

Larry,
[color=blue]
> If it is client-server, I'd strongly suggest Access for developing the
> client application; if it is a web-based application, then VB.NET may be a
> useful choice (but so might several other things: Front Page with the
> Database Interaction Wizard, third-party development tools like cold
> fusion,
> etc.).
>[/color]
Here you do the same from what you in my opinion correctly are saying that
Nick is doing.

It is in my opinion real no reallity to compare FrontPage with .Net. With
one exception and that is the same environment where Access is mostly used
for "Intercompa ny" and than not using the Interenet as communicationla yer
however the interenet.

Business Internet applications are mostly made for external company use, and
need therefore a lot of security and other errorprotection , something that
good can be done with ASPNET.

As forever do I think that in this kind of discussions we should not be to
much philosofic and tell what we think (however absolutly don't know) about
tools where others in the same discussion know much more from.

Just my thought,

Cor