Date:         Wed, 14 Sep 2005 09:15:49 -0700
Reply-To:     "Theise, Eric" <ETheise@anesthesia.ucsf.edu>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         "Theise, Eric" <ETheise@anesthesia.ucsf.edu>
Subject:      Re: SPSS v. Access
Content-Type: text/plain; charset=us-ascii

> Terry Reardon writes:
> Ms Access is a good tool to store vast amounts of data in table format

I would caution people that Access (and Excel) can store vast amounts of
data in the row direction, but it can only hold 255 columns in any table
or query.  This is a limit we've recently exceeded in some of the
genetic work we're doing, and I'm relying more and more on SPSS's
data/merge commands to assemble the output of several different queries
into a single dataset.  Databases like mySQL, MS SQL, and, I assume,
Oracle are not as limited.

Another caution is that Access is more liberal about field names than
the SQL standard.  It's quite possible to have fully functional tables
and queries that cannot be called from outside Access.  We have many
dozens of queries that have evolved over the years that we would like to
call directly from SPSS using an ODBC connection (there's a database
wizard for this), but because the variable names use hyphens or other
"special" characters, they cannot be called without modification.  Most
of the time we export data to files, then import those files into SPSS,
when a database call directly from SPSS to Access would be much more
efficient.  This can be avoided with good planning and design.

We use Access mostly for its tables and queries; for database
management.  For us, data entry takes place either by importing an Excel
spreadsheet into an Access table (how we work with collaborators) or
through a web-based form interface that runs with ColdFusion and
Microsoft's webserver, IIS (how our clinicians enter data).  I should
also mention that it is possible to manipulate data programmatically by
coding in Visual Basic for Applications, and we've created a few
functions that perform simple statistical tasks, though for the heavy
lifting we rely on SPSS (and StatView).

--Eric