PHP and Access

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

First timer here.
Where's the best place to educate myself on how to use PHP in
conjunction with Microsoft Access--good online tutorials, sites, blogs,
etc...?  I work at a small online grocery service that would like to
develop interfacing between our databases with the web to allow
customers/users to directly manipulate profile data and make orders
that will filter directly into our database without manual entry.


Re: PHP and Access wrote:
Quoted text here. Click to load it

You can do it with ODBC. Are you confident that Access can handle the  
load, though?

Photos from 40 countries on 5 continents:
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world:

Re: PHP and Access

Miguel Cruz skrev:
Quoted text here. Click to load it

Access can be pretty tough... But any I agree with Miguel. You should  
try some of the alternatives like SQLite or something. SQLite will work  
on many other platforms than just Windows (even though you can get  
Access working on Linux, it isnt something that it is fun, and most  
users wont even bother going through the trouble for something like Access).

You wont find many guides on how to work with Access and PHP, though it  
is totally possible.

If you decide to work with Access anyway, here is what you will have to  
do in Windows.

1. Go to the control panel @ Windows.
2. Go to administration.
3. Go to Data Sources (ODBC) (I think that is what it is called).
4. Click on "System-DSN".
5. Click "Add".
6. Choose "Microsoft Access Driver (*.mdb)"
7. Choose a name-id for the connection (something simple like "myaccess").
8. Click "Choose" and choose the access database, that the connection  
should work with.

Now you make a testing PHP-document to test the actual connection.

Write something like:
    $conn = odbc_connect("myacces");

    $f_test = odbc_exec($conn, "SELECT * FROM testtable") or  
    while($d_test = odbc_fetch_array($f_test){

Be aware, that you should create "testtable" and fill it with somedata,  
so that PHP will print something :)

I havent testet any of the code.

Best regards and good luck
Kasper Johansen aka knj

Re: PHP and Access

Thanks for all the responses.

It sounds like Microsoft Access is not a favorite among programmers
here.  But I'm afraid I'm stuck with it (for now at least), since our
company has been using it for about 4 years and we don't have the
resources/time right now to switch over to another system (though I
like Dikkie Dik's advice of a progressive transfer).

That being said, I'm actually wondering if PHP is the best language to
use for connecting our Access database to the website based on your
comments?  What I know is that we're currently using PHP (written by
another technician) to have our website forms filter data into email
accounts, so that the data can be manually entered later on in Access.
You say it's possible, Kasper, to combine Access and PHP, but not that
common.  So maybe the best thing is to switch to another database
system.  At the same time, I don't fully understand some of your
suggestions.  For example,  are softwares like SQLite or MySQL a
replacement for your Microsoft Access database, or are they associate
programs to help connect your existing databases to your website?  The
idea of transferring our existing data into another software seems
rather daunting.

I hope you understand what I'm getting at.
your student,

Kasper Johansen wrote:

Quoted text here. Click to load it

Re: PHP and Access

Currently, you're restricted to only running your web server, php, and  
Access through ODBC and the COM object on a Windows box because Access  
only runs on Windows.

You could "add a translation layer" where the web site's data is input  
into a suitable database (MySQL, SQLite, or Postgres), then run a  
periodic "extract" to transfer the data to Access.  This way if someone  
does any sort of database injection exploit, you'll have a layer between  
the actual business data in Access and the web.  You currently do this  
now with the manual data entry; it will just be easier. wrote:

Quoted text here. Click to load it

DeeDee, don't press that button!  DeeDee!  NO!  Dee...

Re: PHP and Access

Definately agree with that last post.  Store the data in a separate
database MySQL is the most common.

This is exactly the sort of job XML was designed for.  Dump data out of
the layer database in XML and then user XML parser to read it in to


Re: PHP and Access wrote:
Quoted text here. Click to load it

And most other environments where people have been exposed to the  

Quoted text here. Click to load it

I have done this (a few years back) and I didn't run into any particular  
difficulties in terms of being able to make things happen.

However, at that time - and maybe things have changed - it seemed like a  
lot of the relationalness of my queries was being handled by the ODBC  
drivers or something, because queries that hit several large tables -  
even if they didn't use very much of their data - resulted in huge  
amounts of data traveling over the network.

As a consequence it ran at about 5% of the speed of a MySQL solution.  

If you are just selecting rows by ID and that's it, then this doesn't  
matter. But if your database structure is complex, and the behavior I  
observed in 2001 is still the case now, then performance could be an  

Photos from 40 countries on 5 continents:
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world:

Re: PHP and Access

Quoted text here. Click to load it

That's a tough question. There is no "best" language. Every language has  
its own pros and cons. PHP and MySQL are such an often used combination  
that it is hard to be wrong with that. But PHP does not bite Access.

One of the pros of PHP is that it is easy to find a provider that  
supports it. These providers will not necessarily support Access to be  
installed on their system though. If you are hosting a site yourself or  
building an intranet application within your own office, that's no problem.

But if the database gets large, Access has a commercial limit built in,  
so I heared. Not that I ever built a database that large. Not even in  
MySQL or SQL Server.

What the best language is, depends on your skills, systems and  
preferences. Off course, Access goes well with ASP or ASP .NET too. And  
maybe you can use it with Ruby On Rails...

Quoted text here. Click to load it

MySQL is more like SQL Server than like Access. MySQL is a full blown  
database server. No fancy forms, reports, wizzards, etc.

Migrating databases is not that difficult. Your source code in PHP must  
be ready for the change, so an abstraction layer is useful. I don't know  
if your PHP code is object-oriented, or if you are familiar with the  
terms. It means that you define generic "database" or "query" objects,  
of which you can build both MySQL and Access variants. There are  
probably enough database layers available if you don't want to write one  

Getting the data over to another database is easy with access. Simply  
create linked tables to the new database and run some queries. Remember  
that if you want to query a linked table to a database server with  
Access, a pass-through query is much faster, but is written in the  
server's SQL dialect.

Good luck

Re: PHP and Access wrote:
Quoted text here. Click to load it

It most definitely isn't.  I've had to do a somewhat major project with
it, and it was horrible.  But while everyone is trying to steer you
away from it and into something more 'practical', allow me to play
devil's advocate...

Quoted text here. Click to load it

Access sucks.  Let's get that out of the way right now.  But what it
does do well is allow very easy creation of forms and reports.  If you
have an Access system that has been around for years, I would be very
surprised if it didn't already have dozens of forms and reports that
have been added into it over the years.  Putting aside the notion of
connecting to Access via PHP for a moment, consider the fact that if
you migrate the data to another database like MySQL, what happens to
all of those forms and reports?  You either have to recreate them all
from scratch on the web, or redirect Access to use the remote tables
rather than the internal ones (never done this personally, so I don't
know how easy/reliable it would be).  Either way, you're adding more
man-hours to the project which may not be worth the payoff.

When you weigh the options, it may just be more practical right now to
simply connect php to Access to dump the data, and then make a note
somewhere of a nice 'project' that you can do some time down the road
when there is more time/resources available.

Re: PHP and Access

I did it with ODBC to gain myself some time to install and learn MySQL.

If ODBC load is too high, you can use COM to create dbOpenTable type  
recordset objects. Never had any load problem with those.

Best regards wrote:
Quoted text here. Click to load it

Re: PHP and Access

I suggest you to convert your access database into some other db. For  
example postgresql or mysql.

Kaarel wrote:
Quoted text here. Click to load it

Re: PHP and Access

On 8 Aug 2006 08:50:32 -0700, wrote:

Quoted text here. Click to load it

Without getting into the Access/MySQL debate, it's perfectly feasible
(for a medium sized application). I ran Access with Coldfusion for
many years with no real problems.  

Many of the criticisms of Access used to be related to how it
performed in a networked environment (arrrgh!). PHP connects as one
user (sort of) and many of the network table and file locking problems
don't seem to happen (at least in my limited experience).  

Setting up and querying the ODBC connections can look a bit daunting,
but it's no more complex than MySQL. There are, however, a few
"gotchas" ...

1) Date formats. Access uses some pretty odd time/date formatting -
you'd have to Google around for the best ways round these. Time/date
functions have given me more grief than anything else.

2) Access uses Jet-SQL, which is slightly different to other
"flavours" of SQL. If you're familiar with it, then you'll be fine.
99% of the work in outputting from a database is in the design of your
SQL statement. *Don't* just cut & paste from Access's query builder
... it's VILE! Unnecessary nested brackets about 20 deep.

I can't think of a single place you could go for help. Any PHP.NET
online manual is by far the best, as it's crammed with loads of
contributionsand fixes from users.

I'd say *go for it* - just give it a try!


Site Timeline