"Pecanfan" wrote
A while ago I vaguely remember trying
the upsizing wizard to port the entire
database over to SQL but it really
struggled, probably more down to my
shoddy database design skills :-).
The current Upsizing Wizard was created when the Development Group was still
listening to their own marketeers' hype about ADL being the only solution
for working with SQL Server. That is not the current position of the
Development Group, but it remains to be seen whether they will re-convert
the Upsizing Wizard to yield an MDB with ODBC connection to SQL Server, or
offer that as an option.
In every case where I worked on an Access client, the Tables had been
created in the server database (not always MS SQL Server) using a modeling
tool (the one most often used was ERWIN, simply because the same DBA was in
charge of the server database on most of the projects where I worked, ERWIN
was what he had, what he used, and worked nicely). Then the Access client
was developed linking to tables in the server DB. In-course-of-project
changes to table design went back to ERWIN and a new run was made. Don't ask
me how, but he saved the data... perhaps by using a special-purpose Access
front-end, or using the Access UI directly in datasheet view with Queries.
It's only a handful of users - 10 LAN based
at most at the moment, however this figure
will grow quite considerably and there's also
the possibility of around 20-30 web users.
You'd almost have to deliberately do things wrong to NOT be able to support
10 LAN users. Assuming the same backend database would be accessible from
your webserver, I'd wager the web users wouldn't add as much load as a
comparable number of LAN users. On the other hand, if the app is really
"mission critical", or has to be 24/7/365, you probably should consider a
server database. It is my belief that MS SQL Server is among the easiest to
learn and implement (but bear in mind that the going rate for DBA's is
considerably higher than for mere Access developers, for whatever that fact
is worth).
If you can live with less reliability and recoverability you can probably
continue with multiuser. To have a fully recoverable Access/Jet multiuser
arrangment, you'd have to code data logging and recovery yourself; to have a
fully recoverable client-server arrangement, it's likely a matter of setting
the server DB's options at or after installing it.
I've worked on several systems that were Access clients to server databases
for which the choice was made, not on number of users, but on reliability
and recoverability. Best of luck in making the correct decision.
Larry Linson
Microsoft Access MVP