Web-ifying an Access database / Security

[Pecanfan]

I've got quite a large database which is totally form-driven at the moment.
I now need to allow multiple users access to various parts of it and,
frankly, I'm not prepared waste my time with Access's inbuilt Workgroup
'security features' - it just seems way too clunky. I figured a more
sensible move would be to port over to SQL and drive it from a nice web
front end. Unfortunately my MS SQL experience is pretty much nil and my web
development skills are dubious to say the least (basic PHP / mySQL knowledge
only).

A while ago I looked into using the 'Pages' functionality within Access
combined with the upsizing wizard but it really struggled, the resulting
HTML wasn't exactly the cleanest I've ever seen and the database has grown
significantly since then.

What would people suggest would be my best way forward with this? To give
you an idea, the database isn't huge (36 tables, 39 forms and 64 reports)
but it is critical.

Do I call it a day and pay a development company to do it properly?

Andy

[Pecanfan]

> A while ago I looked into using the 'Pages' functionality within Access

combined with the upsizing wizard but it really struggled, the resulting
HTML wasn't exactly the cleanest I've ever seen and the database has grown
significantly since then.

(Sorry for double post - news server went down and OE went screwy!)

[Larry Linson]

"Pecanfan" <pe******@no.sp am> wrote

I've got quite a large database which
is totally form-driven at the moment.
I now need to allow multiple users
access to various parts of it and,
If the users are on a LAN, the multiuser environment is going to be cheaper
and easier.
frankly, I'm not prepared waste my
time with Access's inbuilt Workgroup
'security features' - it just seems way
too clunky.
It's not for the faint of heart, but it is learnable, and if the database is
"inhouse" on your LAN, you may not need to delve too deeply into it.
I figured a more sensible move would
be to port over to SQL and drive it
from a nice web front end. Unfortunately
my MS SQL experience is pretty much
nil and my web development skills are
dubious to say the least (basic PHP /
mySQL knowledge only).
Unless your users are out on the remote reaches of the Internet, you may
spend a good deal of time and effort giving them a less-rich-client than
using Access as a client. Access makes a nice client application for any
ODBC-compliant database, and unlike multiuser can often be successfully used
on a WAN.
A while ago I looked into using the
'Pages' functionality within Access
combined with the upsizing wizard
but it really struggled, the resulting
HTML wasn't exactly the cleanest
I've ever seen and the database has
grown significantly since then.
DAPs do have some limitations. For one, like any web app, they can provide
only a less-rich front end than Access. I know some people who use them
successfully on intranets.

I'm not sure why you mentioned struggling with the upsizing Wizard, as all
the DAP apps I know have used Access' Jet databases.
What would people suggest would be
my best way forward with this? To give
you an idea, the database isn't huge
(36 tables, 39 forms and 64 reports)
but it is critical.
Do I call it a day and pay a development
company to do it properly?

If it's critical to your organization, then it should be done properly. If
you aren't able to devote the time and effort to do so, then, yes, you
should hire someone who is.

You didn't say how many users, or describe the environment in enough detail
for me to offer really useful suggestions. If you have decided that you want
Microsoft SQL Server and a web app, you should be asking elsewhere, IMNSHO.
If you are open to other approaches, perhaps clarifying some details will
lead to useful suggestions here.

I've done a lot of work with Access clients via ODBC to various server
databases, and that has worked nicely in a LAN / WAN environment with user
audiences in the low hundreds. Others have done so with much larger user
audiences.

Larry Linson
Microsoft Access MVP

[Pecanfan]

Cheers for the sensible replies Larry, Carl & Jim - much appreciated...

> frankly, I'm not prepared waste my
> time with Access's inbuilt Workgroup
> 'security features' - it just seems way
> too clunky.
It's not for the faint of heart, but it is learnable, and if the database

is "inhouse" on your LAN, you may not need to delve too deeply into it.
I do have plans to make parts of the database accessible from outside the
organisation although at the moment it's just on the LAN. I've had a brief
play around with the workgroup security features and although it'll probably
do the job I haven't heard great things about it's robustness and from a
user support perspective I know problems can occur with .mdw files being
'misplaced' and the like. If people genuinely think this is a viable
solution though I'd certainly be happy to run with it!

I'm not sure why you mentioned struggling with the upsizing Wizard, as all
the DAP apps I know have used Access' Jet databases.
Sorry - that was a separate point. 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 :-). I
think I then used DAPs to access the new SQL database but the design was all
over the place. The DAPs worked fine by what I remember - the HTML scared
me though.

You didn't say how many users, or describe the environment in enough detail for me to offer really useful suggestions.

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. As I say I'd much rather stick with what I vaguely
know but if it's a choice between investing a fair amount of time with
workgroup security and this isn't a good long term decision, I'd much prefer
to invest the time now to develop a more robust system.

Cheers again,

Andy

[Larry Linson]

"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