Asked  7 Months ago    Answers:  5   Viewed   35 times

I have no experience with access.

How to do update/insert/delete/select statement with and without $rs = new com("ADODB.RecordSet"); ?

 Answers

73

PDO

If you want to interface with an MS Access database using PHP, PDO is available for you.

<?php
    try {
        $pdo = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:accounts.mdb;Uid=Admin");
    }
    catch (PDOException $e) {
        echo $e->getMessage();
    } 

When using PDO, due to the unified interface for DB operations, you have the opportunity to make your app more portable across various RDBMs systems. All you have to do is to provide the connection string to the PDO new instance and have the correct PDO driver installed.

As the result of this unified interface, your application can be easily ported from MS Access to MySQL, SQLite, Oracle, Informix, DB2, etc. which most certainly is the case if it ages enough.

Here's an insertion example:

<?php
try {
   // Connect, 
   // Assuming that the DB file is available in `C:animals.mdb`
   $pdo = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:animals.mdb;Uid=Admin");

    // INSERT data
    $count = $pdo->exec("INSERT INTO animals(animal_type, animal_name) VALUES ('kiwi', 'troy')");

    // echo the number of affected rows
    echo $count;

    // close the database connection
    // See: http://php.net/manual/en/pdo.connections.php
    $pdo = null;
}
catch (PDOException $e) {
    echo $e->getMessage();
}

ODBC

In case you don't want to use PDO for some insane reasons, you can look into ODBC.

Here's an example:

<?php

if (! $conn = odbc_connect('northwind', '', '')) {
    exit("Connection Failed: $conn");
}

if (! $rs = odbc_exec($conn, 'SELECT * FROM customers')) {
    exit('Error in SQL');
}

while (odbc_fetch_row($rs)) {
  echo 'Company name: ', odbc_result($rs, 'CompanyName'), PHP_EOL;
  echo 'Contact name: ', odbc_result($rs, 'ContactName'), PHP_EOL;
}

odbc_close($conn);
Wednesday, March 31, 2021
 
ManojGeek
answered 7 Months ago
95

Short answer: Yes.

Long answer:
You should ensure that your code always uses connection identifiers to avoid confusion and have clean, readable code. (Especially when you connect to both databases using an abstraction layer like ODBC or PDO)

Please look into the PHP Manual on PDO and connection management

Example:

$link_mysql = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
$link_msaccess = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test.mdb");

// query MySQL DB
foreach($link_mysql->query('SELECT * FROM test') as $row) {
    print_r($row);
}

// query MS Access DB
foreach($link_msaccess->query('SELECT * FROM omg_its_access') as $row) {
    print_r($row);
}

Example without PDO:

$link_mysql = mysql_connect("localhost", $user, $pass);
mysql_select_db("test", $link_mysql);

$link_msaccess = odbc_connect("odbc:Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test.mdb");

// you may omit the link identifier for MySQL, but i suggest to use it explicitly 
$res1 = mysql_query('SELECT * FROM test', $link_mysql);
while ($row = mysql_fetch_row($res1)) {
    print_r($row);
}
// for ODBC the link identifier is mandatory
$res2 = odbc_exec($link_msaccess, 'SELECT * FROM omg_its_access');
while ($row = odbc_fetch_row($res2)) {
    print_r($row);
}

As you see above, the code for the two database drivers differs in its syntax - that is why i suggest to use PDO.

PDO will avoid a lot of hassle and will make switching to another database driver much easier if you decide to do so later. It abstracts all database drivers and gives you a simple interface to handle them all with the same syntax.

Saturday, May 29, 2021
 
millenomi
answered 5 Months ago
75
Driver={Microsoft Access Driver(*.mdb, *.accdb)}

is not a valid ODBC driver name because it is missing a space. The correct name for the newer "ACE" ODBC driver is

Driver={Microsoft Access Driver (*.mdb, *.accdb)}

However, in this case PHP is running in the 32-bit environment and trying to open an .mdb database so the older "Jet" ODBC driver ...

Driver={Microsoft Access Driver (*.mdb)}

... will work, too.

Saturday, May 29, 2021
 
cegfault
answered 5 Months ago
65

Write the connection object like the following and save characters case.

$dbName = "E:\path\to\db.mdb";
 $db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)};charset=UTF-8; DBQ=$dbName; Uid=; Pwd=;");
Tuesday, August 17, 2021
 
csi
answered 2 Months ago
csi
23

The path to the database file (.accdb or .mdb) that you provide in your connection URL must be either

  • an absolute path, or

  • a relative path from the current working directory that is in effect when your application is running, which in your case appears to be "C:/Users/Gandalf/workspace/FubbleApp/bin/".

If you want your application to automatically search the CLASSPATH for the database file you will need to either provide your own code to do that or include some third-party code to do the search for you.

Monday, August 30, 2021
 
Bob
answered 2 Months ago
Bob
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :