Using Access+DBMS with >30 users

  • Thread starter Thread starter Remco Groot Beumer
  • Start date Start date
R

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
 
Remco said:
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.
Click to expand...

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

Access -> SQL Server * 40 users = no problem performance wise. IME.
 
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.
 
Trevor,

I think your 30% more time to develop in VB is very optimistic.
Click to expand...
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
 
Cor said:
Trevor,




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).
Click to expand...

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.
 
Trevor,
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.
Click to expand...
Do you know how a datagrid in Net works?

Cor
 
martin said:
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.
Click to expand...

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

Do you know how a datagrid in Net works?

Cor
Click to expand...

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.
 
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 interoperability, 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.
--
 
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.
Click to expand...

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.
 
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.
Click to expand...

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 said:
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.
Click to expand...


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.
--
 
Do you believe that he should use
Access to create the front end or
should he create the front end using
VB.Net?
Click to expand...

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
 
Larry,
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.).
Click to expand...
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 "Intercompany" and than not using the Interenet as communicationlayer
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
 
doh,
Here you do the same from what you in my opinion correctly are saying that
Nick is doing.
Click to expand...
Here you do the same as what you are in my opinon correctly telling that
Nick would not do.
where Access is mostly used for "Intercompany" and than not using the
Interenet as communicationlayer however the interenet.
Click to expand...
using Internet as communicationlayer however an Intranet.

Sorry

Cor
 
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?
Click to expand...

I've already posted in the thread.

I said that for a starting user population of 30+, what matters is
the back end (which was what I thought the question was).

Since I believe the OP already knows Access, I see no value in
learning a new development platform (he said he doesn't know .NET).

While it is true that certain advanced programming techniques are
not possible in Access, and that when you get to a certain level of
complexity, you'd working against Access's RAD orientation, I see no
evidence whatsoever that there is any danger of that. Most db
applications fall quite easily within the capabilities of Access.

So, I'd say that using VB or VB.NET makes little sense at all,
except if it is already known up front that the design requires
features and techniques that will be problematic to implement in
Access.
 
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 ... intranet.
Click to expand...

You can have some security with Front Page, but "doing database" with Front
Page means you have to have rather simple application requirements. You can
do much more complex applications in either classic ASP or in ASP.NET.
However, if the application requirements are such that Front Page could be
used, it will require only a small fraction of the investment of
learning-curve, time, and effort of .NET.

But there are also good, secure approaches to web-based databased
applications that can handle complex applications but still do not require
that investment. For example, I have seen some very impressive work done
with the third-party product I mentioned, Cold Fusion (back before Allaire
and the Cold Fusion product became part of Macromedia) and Crystal Reports.
It did require licensing a C F server product, as well as the development
environment. I have not, personally, worked with CF or Crystal -- the work
was done by a colleague in the same company.

Larry Linson
Microsoft Access MVP
 
Larry,
But there are also good, secure approaches to web-based databased
applications that can handle complex applications but still do not require
that investment. For example, I have seen some very impressive work done
with the third-party product I mentioned, Cold Fusion (back before Allaire
and the Cold Fusion product became part of Macromedia) and Crystal
Reports.
It did require licensing a C F server product, as well as the development
environment. I have not, personally, worked with CF or Crystal -- the work
was done by a colleague in the same company.
Click to expand...

I and nobody else have written in this thread about investment.

However, can you tell what is beside the hardware and OS is the maximum
investment for what is absolute needed for an ASPNET application with a Jet
Database.

Cor
 
I don't do .NET applications, but try to stay abreat of the technology.

Just learning the .NET languages and framework environment entails some
considerable effort. Some edition of Visual Studio will be required, using
VB.NET or ASP.NET.

You should also be aware that there is far more coding required in .NET than
in straight Access.

You'll also need a server (shared is OK) running IIS of a recent version,
with the .NET framework. You will probably use ADO.NET to access the
database -- if you do, then you will not need Access installed on the
server.

Larry Linson
Microsoft Access MVP
 
Back
Top