#1
  1. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123

    Access Database on Linux


    Hey everyone,

    I'm trying to write a script to do the bulk of my work for me. What I want to do is go through a loop and select records based on loop control variable states. Unfortunately the database I have to use is an MS Access MDB file. I've been reading around and found a link to PHP documentation[below]. What I read in this was that if you have PHP on a Windows BOX you already have the ODBC drivers required to make the database connection. If you're on a *nix box you will need unixODBC drivers from openlink[not open source]. But it also goes on to say that this will allow you to connect from a Linux box to an MDB on a Windows box. What I really want to do is connect to the MDB on the local Linux box. Is this possible?

    PHP Access Database Docs:
    http://www.php.net/manual/en/faq.dat...tabases.access

    P.S. This is the best place I found to put the thread.
    Last edited by acidfourtyfive; March 9th, 2010 at 11:43 AM.
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,445
    Rep Power
    4539
    To the best of my knowledge, no. The mdb has to live on a windows computer. I don't know if there is any solution using wine though.

    Comments on this post

    • acidfourtyfive disagrees : You can access an MDB usnig MDBTools and unixODBC
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123
    I have found some people saying they have gotten access to an Access Database located on a Linux box using combinations of various software. The most popular seems to be MDBTools (MDB Driver) and unixODBC (ODBC Driver?). Anyway, I'm still fighting with it but I'm to the point now that I'm getting PHP errors that show my lack of knowledge of what I'm going.

    Originally Posted by PHP Error
    SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified
    I will keep looking but the only information I can relly find is in regards to other database configurations. I found something at bryanmills.net but I can't seem to get it working due to the lack of a PHP function: odbc_connect.
  6. #4
  7. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,134
    Rep Power
    123
    I have successfully read a MDB file using PHP on a Linux box without any Windows machines...except to copy the MDB file from when I started this project

    Anyway, I used this as a guide:
    http://www.bryanmills.net/archives/2...linux-and-php/

    I found it via google but the link on google wasn't available so I used the google cache and then went to bryanmills.net to search using their search engine and found the article again. Here is the guide. I had to rebuild PHP with the --with-unixODBC=shared,/usr/lib. I also didn't use the RMPs but go source tarballs of the same version for MDBTools and unixODBC. I have used the sample script with minor changes to read from an Access 97 database successfully.

    That being said, the versions mentioned in the guide had a few issues for compiling but when I tried the RPMs - which i initially installed and figured was my issue so uninstalled them - they worked fine, as in I was getting the same errors from PHP that required me to rebuild PHP. The changes in the source, for MDBTools, are minor like adding a C function prototype definition and changing a function type from extern to static. The forums for the various softwares have these documented as I have made sure that all of my findings were available in the forum by me or someone else. There is also one change where there is an illegal switch in C source so adding the proper breaks solves this. Again, it's all mentioned in the forums. MDBTools seems to be in the process of being converted to Java but the last release, which I didn't use because it didn't have everything I needed from what I could tell, is 4 years old.

    Originally Posted by BryanMills.net
    Microsoft Access Database using Linux and PHP

    Recently I had a project in which I needed to allow Microsoft Access MDB files to be uploaded via a web screen and then parse the data in those files and import selected records into another database (MySql). As I began looking into this problem I began to realize that not many people needed to do this on Linux and that far more people were just convinced this was an impossible task. So I decided to try to make it work, hence this post.

    You will need the following:
    Linux ( I’m running RedHat 9.0)
    PHP
    Apache
    UnixODBC
    MDBTools

    INSTRUCTIONS
    1) Download the UnixODBC RPM, found here. I installed unixODBC version 2.2.5.1.
    rpm -ivh unixODBC-2-2.5-1.i386.rpm

    2) Download the MDBTools rpm, found here. I installed mdbtools version 0.5.1. Read limitations!
    rpm -ivh mdvtools-0.5-1.i386.rpm

    3) Download the MDBTools ODBC driver rpm. Again I installed version 0.5-1.i386.rpm. Read limitations!
    rpm -ivh mdbtools-odbc-0.5-1.i386.rpm

    4) Add the mdbtools driver to your unixODBC config.
    Create a new text file. NON-LINUX user: Beware do not do this on windows as you might get werid new lines, use vi.

    [MDBToolsODBC]
    Description = MDB Tools ODBC drivers
    Driver = /usr/lib/libmdbodbc.so.0
    Setup =
    FileUsage = 1
    CPTimeout =
    CPReuse =

    NOTE: The driver may be in /usr/local/lib/libmdbodbc.so.0. This happens if you build from source and use the make install command. The RPM puts it in /usr/lib.
    Now install the driver using the file you created. This is called a template file.
    odbcinst -i -d -f template.file

    5) Define the datasource name. This is done in the /etc/odbc.ini file. So pull up this file in vi or another text editor and add the following lines.

    [Dogs]
    Description = Microsoft Access Database of Dogs
    Driver = MDBToolsODBC
    Database = /var/data/my_dog_db.mdb
    Servername = localhost
    UserName =
    Password =
    port = 5432

    That’s it you should now have an odbc connection available. I will demonstrate using php, this assumes that your php is compiled with UnixODBC support, the version that ships with Redhat 9 does if yours does not then you can learn how here.

    So I will write a quick php script to query my dogs database and print out the names and weights of my dogs.

    $myDB = odbc_connect(”Dogs”,”",”");
    $query = “select name, weight from dog_list”;
    $result = odbc_exec($myDB, $query);
    while (odbc_fetch_row($result)) {
    print “Name = ” . odbc_result($result,”name”);
    print “
    Weight = ” . odbc_result($result,”weight”);
    }

    If you get a php error that says odbc_connect is not a function then see if you have php-odbc installed. Do rpm -qa php-odbc. If you see php-odbc returned then you have it if not install it., the rpm is available on the redhat discs.

    Limitations:
    - As of the time of writing this entry MDBTools did not support write access to the mdb files. This was fine for my purposes as I was reading data in and sticking it into a mysql database.
    - There is a bug in MDBTools v0.5 which does not allow you to query tables or columns with an underscore. This was a bug I hit early on, but it has been fixed in new version 0.6 but that has not been released as of the time of writing this article. So I recompiled the 0.5 source code with the fix from the 0.6 CVS repository. I have bundled it into a 0.5 release and have the two rpms mentioned above here:
    mdbtools-0.5-1.i386.rpm
    mdbtools-odbc-0.5-1.i386.rpm

    I would check the official download site before using my hacked version as I’m sure this bug will be fixed in 0.6 (plus rumor has write access will be present as well).
    Last edited by acidfourtyfive; March 12th, 2010 at 12:07 PM.

IMN logo majestic logo threadwatch logo seochat tools logo