Paul wrote:
Hi all
I have a pending project with the company I work for, if funding comes
through.
They have an internal Access db and wish to make this available to
customers online for the purposes of ordering.
My initial thought was to use PHP/MySQL.
The problem is how to link or convert the data efficiently.
Any ideas or thoughts?
Have you done this perhaps?
The only DB migrations I've done have been from an old FileMaker Pro
version to MySQL, and later through to the considerably superior (and
also free) PostgreSQL.
This was a full migration, and involved re-building the user interface
as a web application, so it's probably not what you're after.
In this case, though, you might be interested to know that PostgreSQL
(and I think MySQL) can be used as data "engine" back-ends for Access.
Rather than using Microsoft Access's internal database engine (JET?),
you use an ODBC interface to store the data in a real relational
database system (MySQL, PostgreSQL, MS-SQL, etc) and just use Access for
the user interface. The actual database server need not even be on the
same computer (you could, for example, run it on your main server).
With your data in a real RDBMS, it's stored in a superior database
engine that's much more suitable for multi-user access, more easily
backed up, and probably faster and more reliable. You can still use
Access for the user interface, so as I understand it users who aren't
actively modifying the database schema (creating, modifying, and
altering tables and views) won't even know much has changed. On top of
that, you can do things like have your web server talk to the database
to expose that same data through a web application, use 3rd party and
open source reporting tools to analyse your data, and more easily write
quick SQL queries against the database (using tools like the `mysql' and
`psql' command-line clients) if you just need to do some one-off analysis.
In your case, being able to share the same data between Access and your
web server would no doubt be the most attractive aspect of the
configuration I've described.
I haven't done this with Access myself yet (I have with other database
tools), but probably should have a play with it since I have a copy of
Access lying around.
Here are some articles (straight off Google) on the topic:
http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view
http://www.ucl.ac.uk/is/mysql/access/
http://66.102.7.104/search?q=cache:rZdNUWo1Wc4J:www.washington.edu/computing/web/publishing/mysql-access.html+%22Microsoft+Access%22+MySQL&hl=en&client=firefox-a
Your other alternative might be to use the ODBC interface for Access's
internal data store to poke into the Access database. I suspect that
approach won't be too attractive though - potentially slow, unreliable,
and almost certainly much less capable of dealing with larger numbers of
users. I've seen similar schemes work very poorly indeed.
--
Craig Ringer
