SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Using an MS Access DB

    OK this is really sad for me. My project mates have gone ahead and used MS Access as our database and now I've to access it with PHP. I'm thinking that I'd need to use the ODBC functions. Am I correct?

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, i don't think PHP has native Access functions.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK thanks Matt

    [EDIT] I'm walking thru an old tutorial at PHPbuilder -> http://www.phpbuilder.com/columns/siddarth20000228.php3 so I think I'm pretty safe, for now.
    Last edited by redemption; Mar 7, 2003 at 02:21.

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another question: is there a way to password protect access to the Access database?

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Are you running on a *nix machine or a Windows machine? If Windows then use the COM components in PHP and utilising an Access DB is easy. Here's a general example ...
    PHP Code:
    <?php
    if (!$conn = @new COM("ADODB.Connection")) {
        echo 
    "doh";
    }

    $mdb="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" realpath("/inetpub/wwwroot/foldername/filename.mdb") ."PWD=yourpassword"
    $conn->open($mdb); 

    $sql "SELECT * FROM TableName";

    $rs odbc_exec($conn,$sql)
        or die (
    "Could not execute SQL");
    echo 
    "<table border=1>\n";
    while (@
    odbc_fetch_into ($rs$arr)) {
        echo 
    "<tr>\n";
        foreach (
    $arr as $val) {
            echo 
    "<td>$val</td>\n";
        }
        echo 
    "</tr>\n";
    }
    echo 
    "</table>\n";

    $conn->close();
    ?>
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Ian I didn't know that.
    Yep I'm using a Windows machine. I presume using COM would mean I wouldn't have to setup the database in Data Sources (ODBC)?

    I'd gone the 'easy' way by setting up a System DSN and connecting like so:
    PHP Code:
            $conn odbc_connect'myDSN''root''' ); 
    What's the advantage/disadvantage?

    Also, do you know how to setup a password?

  7. #7
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I actually checked again and found that DSN-less didn't work properly - when I looked closely at my original file I found that I had used a DSN. Sorry! Here is the working code I tested ...
    PHP Code:
    <?php
    $dsn 
    "PartsTest";

    $conn odbc_connect($dsn"""")
        or die (
    "Could not connect");

    $sql "SELECT * FROM PartNumbers";

    $rs odbc_exec($conn,$sql)
        or die (
    "Could not execute SQL");

    echo 
    "<table border=1>\n";
    while (@
    odbc_fetch_into ($rs,$arr)) {
        echo 
    "<tr>\n";
        foreach (
    $arr as $val) {
            echo 
    "<td>$val</td>\n";
        }
        echo 
    "</tr>\n";
    }
    echo 
    "</table>\n";
    ?>
    You can setup a password in the DSN definition. Is that what you're asking, or are you asking how to set a password on an Access file? (Tools | Security | Set Database Password).
    Ian Anderson
    www.siteguru.co.uk

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again, Ian.

    The password at the DSN definition doesn't work (I tried 'Advanced' at the DSN setup in the ODBC control panel)

    Trying it out the other way (setting a password on the file) worked.

  9. #9
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Bear in mind that a password on the file must be matched by a password in the DSN or in the odbc_connect() function.
    Ian Anderson
    www.siteguru.co.uk

  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup that's actually what I wanted. All I want is toa setup database permissions much like those you can do using DCL (GRANT). But I figure Access isn't a server (it's just a file) so it can't have that. Am I right to say that?

  11. #11
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You can setup workgroups and define user access that way. Do this on the file (read the Access Help files) and you should find that a .mdw (I think) file will be created - this holds the workgroup information.

    Like I said - it's probably best to read the Access help docs. If all you want is a basic password on the file then you can simply create a password within the file, then assign that password in the Advanced menu for that DSN. (Make sure it is a SYSTEM DSN and not a USER or FILE DSN).
    Ian Anderson
    www.siteguru.co.uk

  12. #12
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    Like I said - it's probably best to read the Access help docs.
    You didn't, but point noted

    Appreciate your help Ian.

  13. #13
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I said it in the previous paragraph of that post.

    Ian Anderson
    www.siteguru.co.uk

  14. #14
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    My bad!

  15. #15
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Location
    San Diego
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would look at some of the ASP code for connecting to an Access database with DSN-less connections and COM. It's EXACTLY the same as PHP, except:
    - Semicolons
    - server.createObject(...) becomes:
    PHP Code:
    $conn 
    PHP Code:
    [color=#006600]= @new [/color][color=#0000cc]COM[/color][color=#006600]([/color][color=#cc0000]"ADODB.Connection" [/color][color=#006600])) [/color] 

    - conn.methodName(...) becomes
    PHP Code:
    $conn->methodName(...); 
    PHP Code:
    [font=Courier New][color=#006600][/color][/font] 
    Kyle Maxwell
    www.kylemaxwell.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •