Is There a Client/Server Version of Access ?

[B1ackwater]

We've fooled around with Access a bit, but only using the
single-user store-bought version. It seems to be a good
database - versatile and infinitely programmable - and
can apparently be used as a front end to SQL server if
we ever needed to go that route.

But - is there a client/server version of Access ? Looking
on the CDW site there is a bewildering variety of packages
and licences and such, but we can't figure out just which
do what or which setup is best for our small business. The
MS site for Access is even less informative.

What we need is to get up to about ten workstations all
accessing a single central data depository. Various
kinds of field data and GIS-referenced info will be
stored there. Programs like ArcPad and Pendragon Forms
will dump data directly in from PDAs.

From what I've heard lately, if you upgrade a workstation
or server - new motherboard especially - MS expects you
to buy a brand new version of any of their 'Office' spectrum
of products. Nasty ... and they wonder why people switch to
linux. As we DO upgrade workstations pretty frequently, but
servers infrequently, spending the bucks on a 'server'
program - and then cheaper licences for the workstations -
would be more cost-effective.

But IS there such a thing - or do we just buy a handful of
off-the-shelf Access programs and create the tables on a
file server ?

Reply to group.

[David W. Fenton]

The answer to the question in the subject is NO.

And there won't ever be one as it won't fit into Microsoft's
stratetic marketing plans for its database products. SQL Server is
the only thing MS will ever sell as a client/server database.

[e-mail] (B1ackwater) wrote in
news:44***************@news.west.earthlink.net:

We've fooled around with Access a bit, but only using the
single-user store-bought version. It seems to be a good
database - versatile and infinitely programmable - and
can apparently be used as a front end to SQL server if
we ever needed to go that route.
Yes. All that is very true.
But - is there a client/server version of Access ? Looking
on the CDW site there is a bewildering variety of packages
and licences and such, but we can't figure out just which
do what or which setup is best for our small business. The
MS site for Access is even less informative.
Access is automatically client/server if you use a server back end.

But what you're really asking is whether there's a client/server
version of *Jet*, Access's built-in database engine.

That's the question I answered at the top -- there isn't and there
never will be one.
What we need is to get up to about ten workstations all
accessing a single central data depository. Various
kinds of field data and GIS-referenced info will be
stored there. Programs like ArcPad and Pendragon Forms
will dump data directly in from PDAs.
Are all the workstations on the same LAN? If so, it doesn't sound
like anything Jet can't handle, though if the data being appended is
coming in large batches very quickly from multiple users at the same
time, it could be a problem.

But Access ships with the MSDE, which is a version of SQL Server
tuned for small workgroups (5 simultaneous connections, 2GB total
data store). There's also the newer SQL Server Express, which is yet
another repackaging of SQL Server intended for desktop use.

There are also free Open Source options like MySQL (wouldn't
recommend it without InnoDB tables) and PostgreSQL. I'm also looking
these days at SQLite.
From what I've heard lately, if you upgrade a workstation
or server - new motherboard especially - MS expects you
to buy a brand new version of any of their 'Office' spectrum
of products. . . .
No, that's not true. If you upgrade a PC with Office already
installed on it, and the upgrades are sufficient to invalidate the
existing authorization key, you contact Microsoft and they give you
a new key. You will probably have to explain to a human being that
you've upgraded your computer. It usually takes an upgrade of more
than one component to invalidate the existing authorization key.
. . . Nasty ... and they wonder why people switch to
linux. As we DO upgrade workstations pretty frequently, but
servers infrequently, spending the bucks on a 'server'
program - and then cheaper licences for the workstations -
would be more cost-effective.
What are you talking about? There is no product anywhere like what
you're talking about.

However, you could install a Windows Terminal Server and licensing
would be handled on the server (though you'd still need Office
licenses on the workstations). You could install an Access runtime
application on a Windows Terminal Server and that wouldn't require
Access installed on the workstations connecting to it.

But before you do that, an Access runtime installed on all the
workstations would get around the authorization problem, too.

Another option is to purchase the Enterprise license from Microsoft
that don't require individual authorization of each workstation.
It's called the Open License program, and is fairly reasonable even
for small shops.
But IS there such a thing - or do we just buy a handful of
off-the-shelf Access programs and create the tables on a
file server ?

Look into the Open License program.

The whole client/server issue is a complete red herring for your
actual problem, which is licensing.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[B1ackwater]

On Mon, 10 Apr 2006 14:52:01 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

"No"

But what you're really asking is whether there's a client/server
version of *Jet*, Access's built-in database engine.

That's the question I answered at the top -- there isn't and there
never will be one.

That was my major question ... a server version of Jet that
all others query for data.

Looks as if SQL server is the way to go - and then use Access
to develop a nice front end. Too bad they centered on the idea
of using VB instead of 'C-something' though ...

[Lyle Fairfield]

B1ackwater wrote:

Looks as if SQL server is the way to go - and then use Access
to develop a nice front end. Too bad they centered on the idea
of using VB instead of 'C-something' though ...

Uh huh!

[B1ackwater]

On Mon, 10 Apr 2006 14:52:01 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

What we need is to get up to about ten workstations all
accessing a single central data depository. Various
kinds of field data and GIS-referenced info will be
stored there. Programs like ArcPad and Pendragon Forms
will dump data directly in from PDAs.
Are all the workstations on the same LAN? If so, it doesn't sound
like anything Jet can't handle, though if the data being appended is
coming in large batches very quickly from multiple users at the same
time, it could be a problem.

Dunno about "very quickly" ... although there could be several
people dumping the contents of their PDAs into a single table
at the same time.

Another concern is the ultimate SIZE of the databases. Lots
of small records - day after month after year - add up.
Historical queries going back five years or so are likely.
We could start exporting to "old.data" tables beyond that,
but it would be NICE to keep everything all together. Jet
seems to offer fair capacity, but SQL Server seems even
better.
But Access ships with the MSDE, which is a version of SQL Server
tuned for small workgroups (5 simultaneous connections, 2GB total
data store). There's also the newer SQL Server Express, which is yet
another repackaging of SQL Server intended for desktop use.
We'd have to get the non-'express' version ... I calculate
we'd collect 2gb in about three years. Might be able to
squish some of it it down to codes - to be looked-up in
another table - and thus maybe half our storage requirement.

Alas, I'd also project more than 5 connections at least
half of the time ...
There are also free Open Source options like MySQL (wouldn't
recommend it without InnoDB tables) and PostgreSQL. I'm also looking
these days at SQLite.
I looked at MySQL ... and it ain't bad. Problem is that the boss
doesn't want to get too 'proprietary' - dozens of custom-writ
programs with a hundred subroutines each or use somebodys obscure
front-end. 'Access' is everywhere, lots of people know how to use
it (more or less), tons of "How To" books to be had. If MS ever
drops Access, it will probably have to replace it with something
almost the same - just dedicated to being a front end for SQL
server.

So, while there's a certain joy in writing custom programs for
working with DBs, you can get everything *just right*, I agree
that it's more sensible to stick with more common, well supported
and documented, canned front-ends that let you get it pretty
damned CLOSE to "just right".

Finally, this DB *has* to work with ESRI GIS products. Our
data collection software will make use of GPS data. ESRI
makes a PDA development package which lets you smartly
blend forms and GPS into a helpful and powerful system
for the users. It can directly dump data into Access and
SQL server with little or no fiddling-around. Does not
work with MySQL.

Haven't looked into SQL-Lite yet ... still in the 'shopping'
stage of things.

From what I've heard lately, if you upgrade a workstation
or server - new motherboard especially - MS expects you
to buy a brand new version of any of their 'Office' spectrum
of products. . . .

No, that's not true. If you upgrade a PC with Office already
installed on it, and the upgrades are sufficient to invalidate the
existing authorization key, you contact Microsoft and they give you
a new key. You will probably have to explain to a human being that
you've upgraded your computer. It usually takes an upgrade of more
than one component to invalidate the existing authorization key.

Hmmm ... I'd heard something just last week saying MS
was gonna get MORE greedy in this respect. Might have just
been a nasty rumor, but then we ARE talking MicroSoft, the
inventor of "Hey, what if we CHARGED people for software ?" :-)

Besides, I *hate* trying to justify my upgrades to some guy
in Calcutta.

. . . Nasty ... and they wonder why people switch to
linux. As we DO upgrade workstations pretty frequently, but
servers infrequently, spending the bucks on a 'server'
program - and then cheaper licences for the workstations -
would be more cost-effective.

What are you talking about? There is no product anywhere like what
you're talking about.

Nevermind ...
However, you could install a Windows Terminal Server and licensing
would be handled on the server (though you'd still need Office
licenses on the workstations). You could install an Access runtime
application on a Windows Terminal Server and that wouldn't require
Access installed on the workstations connecting to it.

But before you do that, an Access runtime installed on all the
workstations would get around the authorization problem, too.
That MAY be the easiest route - and it's not TOO expensive
either. We can get the full off-the-shelf version of Access
for about $150 per copy. Everyone here likes and gets fast PCs
so there's no need for the thin client paradigm. (interesting
how things have drifted back towards the mainframe/terminal
approach of late). I'd just have to limit who can do damage
to the data, tables and forms.

So, if I read you right, all we'd need then is the bare SQL-
Server program, sans any other licences ? Or, do I need
*seperate* licences to connect with SQL-Server and THEN
'Access' too ?

As I said, a bewildering and somewhat poorly documented
variety of options and licencing schemes ... not as
straightforward as I first imagined. Don't want to buy
more than I need to.
Another option is to purchase the Enterprise license from Microsoft
that don't require individual authorization of each workstation.
It's called the Open License program, and is fairly reasonable even
for small shops.
I'll definitely look into it. Um ... do you recall about
what "fairly reasonable" means ? :-)

Odd place here, doesn't mind buying $999 desktops for
the janitor but balks at "expensive" $200 programs to
run on the things. Every place has its ideosyncracies.

But IS there such a thing - or do we just buy a handful of
off-the-shelf Access programs and create the tables on a
file server ?

Look into the Open License program.

I shall.
The whole client/server issue is a complete red herring for your
actual problem, which is licensing.

In our particular case you may be right - although the
Jet engine may prove a bit puny so we're kinda obligated
to run some kind of central server engine that can
handle more. Access is a decent program - and ideal for
working with SQL Server. It's the details of licencing
for one or both products that's confusing me. I'd
rather be programming ...

[David W. Fenton]

[e-mail] (B1ackwater) wrote in
news:44***************@news.west.earthlink.net:

On Mon, 10 Apr 2006 14:52:01 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalid> wrote:

What we need is to get up to about ten workstations all
accessing a single central data depository. Various
kinds of field data and GIS-referenced info will be
stored there. Programs like ArcPad and Pendragon Forms
will dump data directly in from PDAs.
Are all the workstations on the same LAN? If so, it doesn't sound
like anything Jet can't handle, though if the data being appended
is coming in large batches very quickly from multiple users at the
same time, it could be a problem.

Dunno about "very quickly" ... although there could be several
people dumping the contents of their PDAs into a single table
at the same time.

That's actually the only issue, because if you're doing batches of
appends, collisions between simultaneous appends could be a problem,
as there's no central process interfacing with the data store that
could serialize the appends, as there would be with a server back
end.
Another concern is the ultimate SIZE of the databases. Lots
of small records - day after month after year - add up.
Historical queries going back five years or so are likely.
We could start exporting to "old.data" tables beyond that,
but it would be NICE to keep everything all together. Jet
seems to offer fair capacity, but SQL Server seems even
better.
In terms of capacity, lots of small records is not big deal with
Access. I have apps with *big* records with 100s of thousands of
records in several tables.

It all depends on what you want to do with that data, and how well
you can index it.

But Access ships with the MSDE, which is a version of SQL Server
tuned for small workgroups (5 simultaneous connections, 2GB total
data store). There's also the newer SQL Server Express, which is
yet another repackaging of SQL Server intended for desktop use.

We'd have to get the non-'express' version ... I calculate
we'd collect 2gb in about three years. . . .

How, exactly, are you calculating that? And 2GBs is the hard limit
for Jet, so that puts it out of the running in the first place.
. . . Might be able to
squish some of it it down to codes - to be looked-up in
another table - and thus maybe half our storage requirement.
Er, of *course* you should do that. Data normalization is something
you must do, no matter what the db engine.
Alas, I'd also project more than 5 connections at least
half of the time ...
Well, it's not actually that bad if you manage your connections
well. An append operation doesn't take very long, so if you open
your connection, append the records and close the connection, it's
now available for another user. Many people report supporting 15-20
users on MSDE by using those 5 simultaneous connections very
efficiently.

There are also free Open Source options like MySQL (wouldn't
recommend it without InnoDB tables) and PostgreSQL. I'm also
looking these days at SQLite.

I looked at MySQL ... and it ain't bad. Problem is that the
boss doesn't want to get too 'proprietary' - dozens of
custom-writ programs with a hundred subroutines each or use
somebodys obscure front-end. . . .

MySQL has nothing to do with Access. MySQL is a data store, not a
front end programming environment. You could progam your front end
in Access and store your data in MySQL.
. . . 'Access' is everywhere, lots of people know how to use
it (more or less), tons of "How To" books to be had. If MS ever
drops Access, it will probably have to replace it with
something almost the same - just dedicated to being a front end
for SQL server.
There's a non sequitur inherent in your thinking here. Access is
independent of the data store. You can use Jet (Access's default
data store). You can use MSDE (which ships with Access). You can use
SQL Server Express. You can use full-blown SQL Server. You can use
any database with an ODBC or ADO driver, including Oracle, MySQL,
PostgreSQL, Sybase, and so on.

So, there are two choices:

1. front end platform.

2. data store.

And you can choose Access for the 1st and use any of a number of
possibilities for the second.
So, while there's a certain joy in writing custom programs for
working with DBs, you can get everything *just right*, I agree
that it's more sensible to stick with more common, well
supported and documented, canned front-ends that let you get it
pretty damned CLOSE to "just right".
That issue has zilch to do with whether or not you chose MySQL or
any other database other than Access's built-in Jet (which you've
already eliminated as a realistic possibility based on the
information you wrote above).
Finally, this DB *has* to work with ESRI GIS products. Our
data collection software will make use of GPS data. ESRI
makes a PDA development package which lets you smartly
blend forms and GPS into a helpful and powerful system
for the users. It can directly dump data into Access and
SQL server with little or no fiddling-around. Does not
work with MySQL.
That's an unfortunate lack of proprietary dependencies in the data
collection software. It ought to be designed to interface with
industry standard database abstraction layers like ODBC and ADO
(though both were created by Microsoft, they are widely supported by
nearly everyone).
Haven't looked into SQL-Lite yet ... still in the 'shopping'
stage of things.
It's relatively new and I'm not sure what it's intended for. It
looks to be a better db engine than MySQL with its default ISAM,
though not necessarily better than MySQL with InnoDB tables.

From what I've heard lately, if you upgrade a workstation
or server - new motherboard especially - MS expects you
to buy a brand new version of any of their 'Office' spectrum
of products. . . .

No, that's not true. If you upgrade a PC with Office already
installed on it, and the upgrades are sufficient to invalidate the
existing authorization key, you contact Microsoft and they give
you a new key. You will probably have to explain to a human being
that you've upgraded your computer. It usually takes an upgrade of
more than one component to invalidate the existing authorization
key.

Hmmm ... I'd heard something just last week saying MS
was gonna get MORE greedy in this respect. Might have just
been a nasty rumor, but then we ARE talking MicroSoft, the
inventor of "Hey, what if we CHARGED people for software ?" :-)

Well, I've been installing MS Office on 100s of machines for the
last 15 years, and had to deal with the switch to authorization that
came about around 2000-01 period. It wasn't as painful as it sounded
like. And if you do Open Licensing, it's as easy as it can get.
Besides, I *hate* trying to justify my upgrades to some guy
in Calcutta.
I don't know that Microsoft's authorization call center is in India.
Your comment sounds like near racism to me.

. . . Nasty ... and they wonder why people switch to
linux. As we DO upgrade workstations pretty frequently, but
servers infrequently, spending the bucks on a 'server'
program - and then cheaper licences for the workstations -
would be more cost-effective.

What are you talking about? There is no product anywhere like what
you're talking about.

Nevermind ...

However, you could install a Windows Terminal Server and licensing
would be handled on the server (though you'd still need Office
licenses on the workstations). You could install an Access runtime
application on a Windows Terminal Server and that wouldn't require
Access installed on the workstations connecting to it.

But before you do that, an Access runtime installed on all the
workstations would get around the authorization problem, too.

That MAY be the easiest route - and it's not TOO expensive
either. We can get the full off-the-shelf version of Access
for about $150 per copy. Everyone here likes and gets fast PCs
so there's no need for the thin client paradigm. (interesting
how things have drifted back towards the mainframe/terminal
approach of late). I'd just have to limit who can do damage
to the data, tables and forms.

An Access runtime app is much cheaper than that, as you buy the
developer tools and then create your runtime distribution. The cost
of the developer tools (depending on which version of Access you're
going to distribute in) is something like $300-500. That gives you a
license to install a runtime distribution on an unlimited number of
computers.
So, if I read you right, all we'd need then is the bare SQL-
Server program, sans any other licences ? Or, do I need
*seperate* licences to connect with SQL-Server and THEN
'Access' too ?
SQL Server does require licenses for users. I don't know what the
cost is.
As I said, a bewildering and somewhat poorly documented
variety of options and licencing schemes ... not as
straightforward as I first imagined. Don't want to buy
more than I need to.
It doesn't seem like all the big a deal to me. As long as you're
using proprietary software, you're going to have licensing issues.
Just compare to Oracle, and it looks pretty simple by comparison.

Another option is to purchase the Enterprise license from
Microsoft that don't require individual authorization of each
workstation. It's called the Open License program, and is fairly
reasonable even for small shops.

I'll definitely look into it. Um ... do you recall about
what "fairly reasonable" means ? :-)

Well, it's no more expensive than buying the same number of
standalone copies.
Odd place here, doesn't mind buying $999 desktops for
the janitor but balks at "expensive" $200 programs to
run on the things. Every place has its ideosyncracies.

But IS there such a thing - or do we just buy a handful of
off-the-shelf Access programs and create the tables on a
file server ?

Look into the Open License program.

I shall.

The whole client/server issue is a complete red herring for your
actual problem, which is licensing.

In our particular case you may be right - although the
Jet engine may prove a bit puny so we're kinda obligated
to run some kind of central server engine that can
handle more. Access is a decent program - and ideal for
working with SQL Server. It's the details of licencing
for one or both products that's confusing me. I'd
rather be programming ...

Sounds like your best route would be an Access runtime against SQL
Server. Then you'd only buy the Office developer tools and the
needed licenses for SQL Server.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/