bw@barrrk.net (B1ackwater) wrote in
news:443acbda.37373309@news.west.earthlink.net:
[color=blue]
> On Mon, 10 Apr 2006 14:52:01 -0500, "David W. Fenton"
><XXXusenet@dfenton.com.invalid> wrote:[color=green]
>>[color=darkred]
>>> 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.[/color]
>>
>>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.[/color]
>
> Dunno about "very quickly" ... although there could be several
> people dumping the contents of their PDAs into a single table
> at the same time.[/color]
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.
[color=blue]
> 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.[/color]
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.
[color=blue][color=green]
>>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.[/color]
>
> We'd have to get the non-'express' version ... I calculate
> we'd collect 2gb in about three years. . . .[/color]
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.
[color=blue]
> . . . 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.[/color]
Er, of *course* you should do that. Data normalization is something
you must do, no matter what the db engine.
[color=blue]
> Alas, I'd also project more than 5 connections at least
> half of the time ...[/color]
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.
[color=blue][color=green]
>>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.[/color]
>
> 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. . . .[/color]
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.
[color=blue]
> . . . '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.[/color]
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.
[color=blue]
> 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".[/color]
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).
[color=blue]
> 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.[/color]
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).
[color=blue]
> Haven't looked into SQL-Lite yet ... still in the 'shopping'
> stage of things.[/color]
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.
[color=blue][color=green][color=darkred]
>>> 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. . . .[/color]
>>
>>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.[/color]
>
> 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 ?" :-)[/color]
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.
[color=blue]
> Besides, I *hate* trying to justify my upgrades to some guy
> in Calcutta.[/color]
I don't know that Microsoft's authorization call center is in India.
Your comment sounds like near racism to me.
[color=blue][color=green][color=darkred]
>>> . . . 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.[/color]
>>
>>What are you talking about? There is no product anywhere like what
>>you're talking about.[/color]
>
> Nevermind ...
>[color=green]
>>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.[/color]
>
> 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.[/color]
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.
[color=blue]
> 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 ?[/color]
SQL Server does require licenses for users. I don't know what the
cost is.
[color=blue]
> 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.[/color]
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.
[color=blue][color=green]
>>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.[/color]
>
> I'll definitely look into it. Um ... do you recall about
> what "fairly reasonable" means ? :-)[/color]
Well, it's no more expensive than buying the same number of
standalone copies.
[color=blue]
> 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.
>[color=green][color=darkred]
>>> 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 ?[/color]
>>
>>Look into the Open License program.[/color]
>
> I shall.
>[color=green]
>>The whole client/server issue is a complete red herring for your
>>actual problem, which is licensing.[/color]
>
> 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 ...[/color]
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/ 
