[PHP] Reading an MS Access Database file on *NIX platform using PHP

[David Otton]

On Sun, 24 Aug 2003 14:54:41 -0400 (EDT), you wrote:

>I'm building a small web application for a friend using PHP. He'd like to
>use MS Access to keep the data in, and update the data on the site by FTP'ing
>Access files he edits on his machine up to the web host.
>
>The web host is unix-based (FreeBSD, slightly hacked by Verio, I believe), so
>this means COM functions aren't available. ODBC was my next thought, but I've
>never used it, and there are a few things I don't understand. Setting up a
>DSN is one of them -- apparently this isn't as simple as constructing a string,
>and all the tutorials I can find seem to involve going into a Windows control
>panel and making a setting. This of course will not be possible, since there
>are no windows control panels on FreeBSD.

Suggestion: go backwards. Set up the data in, say, MySQL with an ODBC
driver, and use Access as a front-end onto that data (Access makes a good
front-end for manipulating other databases). I've done this with SQL Server,
but it should be possible with anything that can talk ODBC.

He gets the interface he's used to, you get a database that can run under
BSD. Of course, changes will be reflected in the site in real-time, which
may be a good thing or a bad thing.

Either that or export the data from Access in a readable format, and import
it at the other end.

[url]http://www.google.com/search?hl=en&lr=&ie=UTF-8&oe=UTF-8&q=connect+access+mysql[/url]

4th link looks useful.

>In short: can anyone offer any quick tips on how to use the odbc database functions or PEAR::DB to query data from an MS Access file sitting on a UNIX box?

That's not what ODBC does. It's a protocol for talking to a database server,
not a program for parsing a file.

[Curt Zirzow]

* Thus wrote Giz (gizmo@gizmola.com):

> Access is a pc database. It doesn't run on unix. Why people insist on
> beating their heads against the wall in this manner I will never know.
> Ignorance I suppose. The alternative is to have your friend use a
> relational database and have a few simple forms that will allow him to
> insert/update/delete. Best of both worlds, and the open source/free
> databases mysql/postgresql are used on thousands of websites every day.

First off, Access has nothing to do with a pc, it is the
communication layer that causes the problem. And the only drivers
available for this layer are for MS Windows.

PC != MS Windows

Second, Not every solution can be solved by plopping a
mysql/postregsql database server somewhere and use it. There are
multiple reasons why people choose one database over the other,
like costs, familiarity, and quickness of results.

MS Access is used on thousands of websites every day too.




Curt
--
"I used to think I was indecisive, but now I'm not so sure."

[Jabber@Raditha.Com]

Hi,

One of these ' tricky' windows linux bridging techniques is with JDBC.
But that only works for sql server and not for access. This works like a
dream if you are on J2EE. can be used with php as well if you don't mind
mixing java and php classed. I have also had success with 'ODBC socket
server' they renamed themselves and i am not sure of the new name though.

It wouldn't matter so much if people wanted to use ms SQL server instead
they alsays insist on access :-((



Paul Fitzpatrick wrote:

>* Thus wrote Giz (gizmo@gizmola.com):
>
>

>>Access is a pc database. It doesn't run on unix. Why people insist
>>
>>

>on
>
>

>>beating their heads against the wall in this manner I will never know.
>>Ignorance I suppose. The alternative is to have your friend use a
>>relational database and have a few simple forms that will allow him to
>>insert/update/delete. Best of both worlds, and the open source/free
>>databases mysql/postgresql are used on thousands of websites every
>>
>>

>day.
>
>
>Programmers often get their heads banged against a wall 'by' clients.
>
>I recently had to go through the whole issue of trying to use a client's
>favorite Access DB on a Linux server with no success. I tried
>convincing him to switch to MySQL etc. but of course he wanted to stay
>with what he knew.
>
>Maybe try for some MSSQL capable hosting with this job, PEAR and ADODB
>are great for ODBC, the only issue is setting up an ACCESS DSN, which
>can't be done on a Unix box at this time. I have heard of tricky
>windows/linux bridging techniques though, but what ISP is going to set
>up an extra windows box for one Access db?
>
>Cheers,
>Paul
>
>
>

--
[url]http://www.raditha.com/php/progress.php[/url]
A progress bar for PHP file uploads.

[Curt Zirzow]

* Thus wrote David Otton (phpmail@jawbone.freeserve.co.uk):

> On Sun, 24 Aug 2003 14:54:41 -0400 (EDT), you wrote:
>

> >I'm building a small web application for a friend using PHP. He'd like to
> >use MS Access to keep the data in, and update the data on the site by FTP'ing

>
> Suggestion: go backwards. Set up the data in, say, MySQL with an ODBC
> driver, and use Access as a front-end onto that data (Access makes a good
> front-end for manipulating other databases). I've done this with SQL Server,
> but it should be possible with anything that can talk ODBC.

I would also suggest this. Basically you create a MS Access on his
computer and link (with odbc) mysql tables into the MS Access.

The only problem I forsee is granting remote access to the server.
Seeing that this is on a verio machine, I doubt they'll allow
access remotely. Perhaps there is a chance if your friend has a
static ip.

> Either that or export the data from Access in a readable format, and import
> it at the other end.

Yep, and this would be my alternative :) Using CSV files of
course!


Curt
--
"I used to think I was indecisive, but now I'm not so sure."

[Curt Zirzow]

* Thus wrote [email]jabber@raditha.com[/email] (jabber@raditha.com):

> Hi,
>
> The simplest way i know of to use access from linux is to use mdbtools
> (a free download at sourceforge). Haven't tried to invoke this from PHP
> but it would not be all that difficult to create a MDB module.
>

yeah, I saw a reference to that with some google searches. Also
some references of using 'wine' but that seemed to be a little
overkill just to communicate with ms access.


Curt
--
"I used to think I was indecisive, but now I'm not so sure."

[Murugesan]

Hello all,
I have cleared the stage of changing the window status on mouse over
and on mouse out.
echo "<a href='/list.php' onmouseover=\"window.status=' '; return true;\"
onmouseout=\"window.status=' '; return true;\">Click here</a>";
When I keep on pressing my left mouse button it is displaying the URL in the
status bar.
How to remove the status?
Any one there to help me?

-Murugesan

[Wouter Van Vliet]

So, while overnight everybody has said something about this.. Let me add a
few lines. Also because I just want to keep the discussion running, since
I'm having pretty much the same kind of situation.

First of all, what I get fro your description is that your friend wants to
make changes to access 'offline'. That means: when he's not connected to the
server that should parse the MS Access file. Makes me believe that he will
have to upload the Access file to the server which parses it. Even though
there's huge bandwidths nowadays, that's really not what you want for a ms
access file. Those fella's get HUGE.

I'd suggest you use XML for the talking. Haven't tried it yet, but I know
there's VB functions availabel within MS Access to save the data from a
table to XML. Then, almost any language can read PHP and thus parse it.
Other benefits are that there's just one button to click on, and the
interface will create XML files for all of your tables. Those files will
always be smaller than your MS Access file, since the latter also has
queries, forms and a lot of kbytes nobody knows where they come from.

As for what I'm running into is this. The frontend for the database is MS
Access, the data comes from PostgreSQL (using PosgresODBC to connect) and
there are four possible places where the user can access and make changes to
the database.
- 1+2: Connected to the server, using SAMBA to access the filesystem
- 3+4: Locations where there's no internet connection whatsoever whil
editting. Data syncing happens over a swappable harddisk.

Any ideas what might be the best way to handle it. On each location edits
and additions are possible, on data should ever get lost for any reason
thinkable and thus all changes made at any place should get into the main
PostgreSQL server.

Hoping to have helped and to recieve help in return I am saluting you all :D
Wouter

-> -----Oorspronkelijk bericht-----
-> Van: Weston Cann [mailto:weston@canncentral.org]
-> Verzonden: maandag 25 augustus 2003 3:18
-> Aan: [email]php-general@lists.php.net[/email]
-> Onderwerp: Re: [PHP] Reading an MS Access Database file on *NIX platform
-> using PHP
->
->
->
-> On Sunday, August 24, 2003, at 05:31 PM, Giz wrote:
->
-> > Access is a pc database. It doesn't run on unix. Why people
-> insist on
-> > beating their heads against the wall in this manner I will never know.
-> > Ignorance I suppose. The alternative is to have your friend use a
-> > relational database and have a few simple forms that will allow him to
-> > insert/update/delete. Best of both worlds, and the open source/free
-> > databases mysql/postgresql are used on thousands of websites
-> every day.
->
-> I share a low regard for Access, and I've got a set of standard
-> forms/code I use to set up a nice interface to MySQL, and I
-> showed it to
-> my friend. He likes that, but there's a problem:
->
-> He wants to be able to make changes offline, and then sync up.
->
-> This isn't an uncommon desire... I've run into people wanting
-> to do this
-> before (with Excel spreadsheets, no less). And in fact, it's probably
-> the most convenient way of doing things, as long as they're
-> warned about
-> certain dangers (overwrites from competing unsynced database files).
-> There's a certain cumbersome nature about web forms and the time delay
-> involved in HTTP transactions.
->
-> So anyway, that's the motivation for using Access here.
->
-> David Otton <phpmail@jawbone.freeserve.co.uk> wrote:
->
-> > Suggestion: go backwards. Set up the data in, say, MySQL with an ODBC
-> > driver, and use Access as a front-end onto that data (Access makes a
-> > good
-> > front-end for manipulating other databases). I've done this with SQL
-> > Server,
-> > but it should be possible with anything that can talk ODBC.
-> >
-> > He gets the interface he's used to, you get a database that can run
-> > under
-> > BSD. Of course, changes will be reflected in the site in real-time,
-> > which
-> > may be a good thing or a bad thing.
-> >
-> > Either that or export the data from Access in a readable format, and
-> > import
-> > it at the other end.
->
-> These ideas might work better; especially the latter. If COM doesn't
-> work under UNIX and ODBC can only talk to FoxPro, Access, and other
-> document (rather than server) oriented databases with the help of some
-> special Win32 control panel/dll, then it would seem that the
-> only way to
-> do offline updates that are then synced would be exporting from Access
-> in some standard format.
->
-> But it seems somewhat odd to me that there aren't any UNIXy (PHP, Perl,
-> something....) for parsing Access files. I know there's a few CPAN
-> modules for Excel, but it's interesting that there's apparently nothing
-> for Access.
->
->
-> ~ == ~
-> http://weston.canncentral.org/
->
-> --
-> PHP General Mailing List (http://www.php.net/)
-> To unsubscribe, visit: http://www.php.net/unsub.php

[Paul Fitzpatrick]

* Thus wrote Giz (gizmo@gizmola.com):

> Access is a pc database. It doesn't run on unix. Why people insist

on

> beating their heads against the wall in this manner I will never know.
> Ignorance I suppose. The alternative is to have your friend use a
> relational database and have a few simple forms that will allow him to
> insert/update/delete. Best of both worlds, and the open source/free
> databases mysql/postgresql are used on thousands of websites every

day.


Programmers often get their heads banged against a wall 'by' clients.

I recently had to go through the whole issue of trying to use a client's
favorite Access DB on a Linux server with no success. I tried
convincing him to switch to MySQL etc. but of course he wanted to stay
with what he knew.

Maybe try for some MSSQL capable hosting with this job, PEAR and ADODB
are great for ODBC, the only issue is setting up an ACCESS DSN, which
can't be done on a Unix box at this time. I have heard of tricky
windows/linux bridging techniques though, but what ISP is going to set
up an extra windows box for one Access db?

Cheers,
Paul