Big scale application

[Guest]

Hello.

I want to create a product configurator system where 500 (or more) users can
enter and access common data. A substantial part of these users also need to
be able to work off-line and enter new data - i.e. be able to synchronize
when on the server.

I am very experienced in Access2000 - and can easily see how I would design
the whole thing. But for this number of users I believe it should be
something "more".

I was thinking of Access2007 as frontend and SQL server as backend. Is that
possible - and how would that actually work? And how about offline
functionality?

 

[Larry Linson]

I want to create a product configurator system where
500 (or more) users can enter and access common data.
A substantial part of these users also need to be able
to work off-line and enter new data - i.e. be able to
synchronize when on the server.

I am very experienced in Access2000 - and can easily
see how I would design the whole thing. But for this
number of users I believe it should be something "more".

Will this application be run on a LAN? I ask, because that is my assumption
and if it is to be via web access, different responses would be in order.

500 concurrent users just doesn't work in the Access multiuser environment.
In fact, I haven't heard of any cases where people have come close to the
theoretically, physically possible maximum of 255 concurrent users.

I was thinking of Access2007 as frontend and SQL
server as backend. Is that possible - and how would
that actually work?

It's certainly possible -- more than half of the paying work I've done with
Access since 1993 has been Access client applications using linked tables in
server databases (MS SQL Server was one, but any ODBC compliant database can
be used). If you prefer (I do not prefer, though I have worked a little with
ADPs), you can use the Access "Project" ADP with an SQL Server database, and
use ADODB (OLEdb-based) instead of ODBC.

I've never heard of a user audience more than a thousand in an Access
client - ODBC - server DB environment, but, as far as I know, there's no
practical limit.

And how about offline functionality?

Please clarify this question for me, because I don't understand what you are
asking, unless it's meant something like "How 'bout them Forty-Niners?"

Larry Linson
Microsoft Access MVP

 

[Guest]

The application will be run on a LAN. With the back end data base on a
central server and the front ends on the local PCs. Frontend should be .mde -
and possibly running on a run-time license. I know that would work on pure
Access - but I dont know if you can access a SQL server through an Access
run-time license?

And 500 users - clearly not all of them will be on-line simultaneusly - so
the 255-limit may not even be a problem, but I would like to have more
data-recovery facilities as offered by Access. I have experienced Access to
chrash and leave tables in an unrecoverable state. You can work around that -
but in this kind of application this would lead to loss of data. I belive SQL
server has better handling of this?

And off-line - here I mean that users should be able to work when not
connected to the server. That mens on a local database that replicates data
back to the server when connceted again. I dont see all server data being
replicated on all off-line PCs but maybe to have a check-out procedure where
relevant projects can be downloaded to the off-line database for use on the
road. Conection to the server could in many cases be through some kind of VPN
connection from e.g. a home office. I dont know if that is what you mean by
web access?

 

[Larry Linson]

The application will be run on a LAN. With the back
end data base on a central server and the front ends
on the local PCs. Frontend should be .mde - and
possibly running on a run-time license. I know that
would work on pure Access - but I dont know if
you can access a SQL server through an Access
run-time license?

Yes, you can create Access client applications to SQL Server and execute
them with the run-time support -- they could be MDB or MDE. I know for
ADPs, there is an ADE corresponding to the MDE, but I've never been involved
with ADP or ADE using the runtime.

And 500 users - clearly not all of them will be on-line
simultaneusly - so the 255-limit may not even be a problem,
but I would like to have more data-recovery facilities
as offered by Access. I have experienced Access to
chrash and leave tables in an unrecoverable state. You
can work around that - but in this kind of application
this would lead to loss of data. I belive SQL
server has better handling of this?

I have worked on a number of database applications that used server
databases for the datastore, just for the reasons of reliability,
recoverability, and scalability with far, far fewer users than you
anticipate. Most server databases that I know about have logging that can be
used to recover in case of a crash, and because of the way they work with
client applications, tend to be less prone to crashes or corruption that the
file-server Jet database that is the default for Access.

And off-line - here I mean that users should be able
to work when not connected to the server. That mens
on a local database that replicates data back to the
server when connceted again. I dont see all server
data being replicated on all off-line PCs but maybe
to have a check-out procedure where relevant projects
can be downloaded to the off-line database for use
on the road.

Both Jet and MS SQL Server (and some other server databases) have the
capability for "replication". I haven't used it, but know it "is not for the
faint of heart". Others who post here have experience and can comment with
authority (David Fenton comes to mind, but there are others). Even so, it is
likely to be a great deal simpler than trying to "roll your own".

Conection to the server could in many cases be
through some kind of VPN connection from
e.g. a home office.

Synchronization of replicated databases is far more reliable and stable when
performed on the LAN, but can be done across the Internet. Again, I'll leave
discussion of that to others who are experienced with replication.

I dont know if that is what you mean by
web access?

No, what I was thinking was that your 500 users might be connecting using
the Internet or an intranet, in which case, you'd need a web-based
application or website to interface with the database in that situation.
There are multiple approaches to web access to databases, and this isn't
really the newsgroup that deals with those... Active Server Pages (ASP) for
pre-DotNet software tools, ASP.NET for the DotNet environment, FrontPage
with the Data Interaction Wizard, Adobe's (formerly Allaire) Cold Fusion, or
full scale Enterprise Distributed Applications using a variety of software
tools are some ways to use databases on the web.

One of the simplest would be to use Microsoft Terminal Services and/or
Citrix Metaframe to execute Access on the server, but I don't even know a
good rule-of-thumb for the number of users it could handle (obviously will
vary depending on the application and the hardware environment). If users
who are away from the LAN will always have access to the Internet, this VPN
setup might be a lot easier than implementing and maintaining a replicated
database.

Larry Linson
Microsoft Access MVP