ActiveState Code

Recipe 163447: Microsoft Access Database Connectivity (DSN-Less)

An example of connecting to a Microsoft Access database, executing an SQL query, and displaying the results in HTML using PHP and COM (Windows only).

NOTE: This works with PHP 4.x. I have been unsuccessful with getting this to work with PHP 5.1.6 and Apache 2.0.59 on Windowx XP Professional.

PHP 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
<?php

// Two versions of Microsoft Office.  Choose one.
//$db = 'C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb';
$db = 'C:\\Program Files\\Microsoft Office\\Office10\\Samples\\Northwind.mdb';

$conn = new COM('ADODB.Connection') or exit('Cannot start ADO.');

// Two ways to connect. Choose one.
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.');
//$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db") or exit('Cannot open with driver.');

$sql = 'SELECT   ProductName, QuantityPerUnit, UnitPrice
	FROM     Products
	ORDER BY ProductName';
$rs = $conn->Execute($sql);

?>

<table>
<tr>
	<th>Product Name</th>
	<th>Quantity Per Unit</th>
	<th>Unit Price</th>
</tr>
<?php while (!$rs->EOF) { ?>
	<tr>
		<td><?php echo $rs->Fields['ProductName']->Value ?></td>
		<td><?php echo $rs->Fields['QuantityPerUnit']->Value ?></td>
		<td><?php echo $rs->Fields['UnitPrice']->Value ?></td>
	</tr>
	<?php $rs->MoveNext() ?>
<?php } ?>
</table>

<?php

$rs->Close();
$conn->Close();

$rs = null;
$conn = null;

?>

Comments

  1. 1. At 2:01 a.m. on 26 mar 2003, Pier Luigi said:

    PWS and PHP. I tried the Connection string by Gualano and Hendricks to connect PHP files to Microsoft Access Data Base with Windows 2000 and Internet Information server. They were perfect. BUT with Win98 and Personal Web server they didn't work!!! Any suggestion ?? Luigi Gaudio

  2. 2. At 12:30 p.m. on 28 oct 2003, Bjørn Atle Vorland said:

    Re: PWS and PHP -- > Missing DCOM / MDAC. You might be missing the latest DCOM http://www.microsoft.com/com/dcom/dcom98/download.asp or MDAC http://download.microsoft.com/download/3/b/f/3bf74b01-16ba-472d-9a8c-42b2b4fa0d76/mdac_typ.exe ,hope this helps. Regards Bjørn Atle

  3. 3. At 6:29 a.m. on 24 dec 2003, Gregory Carlisle said:

    Connection Error. Hey, I'm usin WinXP, and when I tested that script it said "Parse error: parse error, unexpected T_VARIABLE in c:\web site\test.php on line 5" can anyone help? I checked the code and it was exactly as it was shown on the site, any help?

  4. 4. At 6:51 p.m. on 24 dec 2003, Gregory Carlisle said:

    Error Found in Code. Hey, I found an error in the code. At line 29 there should not be the () in MoveNext.

  5. 5. At 11:36 a.m. on 29 mar 2004, Geoffrey Hyson said:

    New record. Can a new record be inserted into a table using this method of connection?

  6. 6. At 7:36 a.m. on 19 apr 2004, Rob MacDonald said:

    Missing escape \? Or missing end ; Have you escaped all \'s with the \ and have you ended the line with a ;?

  7. 7. At 6:17 a.m. on 26 apr 2004, Mogens Melander said:

    Add Record. Shure,

    $sql = "INSERT INTO TABLE(field1, field2,....) VALUES('$field1, $field2,....)";

  8. 8. At 8:07 a.m. on 7 apr 2005, Mumthaz Sidik said:
    the code is same as Eric Guanlao (other recipes) titled Microsoft Access Database Connectivity (DSN-Less) posted on 2003/02/11

    In the middle of the code from

        Fields['ProductName']->Value

    Fields['QuantityPerUnit']->Value

    Fields['UnitPrice']->Value



     rs.Update //here the code should be added to  update before rs.MoveNext

    Close();

    $conn->Close();

    ?>

    OR

    The second method

    --On the Query---

    eg: $sql = 'insert into (ProductName,QuantityPerUnit,UnitPrice) values('Coke','100','10')'from products ;

    $rs = $conn->Execute($sql);

  9. 9. At 8:08 a.m. on 7 apr 2005, Mumthaz Sidik said:
    Inserting OR Updating Records in MSACCESS using SQL Query. the code is same as Eric Guanlao (other recipes) titled Microsoft Access Database Connectivity (DSN-Less) posted on 2003/02/11

    In the middle of the code from

        Fields['ProductName']->Value

    Fields['QuantityPerUnit']->Value

    Fields['UnitPrice']->Value



     rs.Update //here the code should be added to  update before rs.MoveNext

    Close();

    $conn->Close();

    ?>

    OR

    The second method

    --On the Query---

    eg: $sql = 'insert into (ProductName,QuantityPerUnit,UnitPrice) values('Coke','100','10')'from products ;

    $rs = $conn->Execute($sql);

  10. 10. At 11:03 p.m. on 15 apr 2005, Rafi Abdul said:

    $db = 'C:\Inetpub\wwwroot\6\a.mdb'; can u find any problem in this code ... I am getting the same error as well .. Parse error: syntax error, unexpected T_VARIABLE, expecting ',' or ';'

  11. 11. At 1:40 p.m. on 1 may 2005, J S said:

    PHP, Access. I've tried running the code in the article and I keep getting a PHP Script Interpreter error. When I replace the "$conn->Open" statements with "$conn = odbc_connect()" statements, I don't get the Interpreter error, but I do get a PHP error: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause., SQL state 37000 in SQLExecDirect in .

    I'm sure my SQL statement is correct. What else may be wrong?

  12. 12. At 7:14 p.m. on 18 may 2005, Terry Kosowick said:

    Questions about ->. I'm just learning PHP. What does the -> mean? Or where can I find out about it? Thanks.

  13. 13. At 8:26 p.m. on 31 aug 2005, Vu Ton That said:

    r yjr. rt urt

  14. 14. At 6:51 p.m. on 21 dec 2005, Flávio Maia said:

    Didn't work. I tried to use the code "as is", with no success on either method of connection. Then i tried to use an existing .mdb file of mine, but it didn't work either. I use Apache 1.3.34, PHP 5.1.1, Win XP. The .mdb file was created using Access XP.

  15. 15. At 2:57 a.m. on 26 dec 2005, Hasan said:

    This works for me. One of my setups is XP+IIS+PHP4.4.1 and I use the following for a DSN-less connection to Access:

     Comment - find the absolute path for scripting
 if(!isset($_SERVER["DOCUMENT_ROOT"])){
  $_SERVER["DOCUMENT_ROOT"]=str_replace('\\','/',getcwd());
 }

 Comment - connect to db One of my setups is XP+IIS+PHP4.4.1 and I use the following for a DSN-less connection to Access:

<pre>
 Comment - find the absolute path for scripting
 if(!isset($_SERVER["DOCUMENT_ROOT"])){
  $_SERVER["DOCUMENT_ROOT"]=str_replace('\\','/',getcwd());
 }

 Comment - connect to db

    </pre>

  16. 16. At 3:02 a.m. on 26 dec 2005, Hasan said:

    My apologies... That didn't print correctly. But I just whipped up an HTML page where the complete code from my previous post can be viewed at http://www.marxmedia.net/tuts/php/dsnless/dsnless.htm

    Enjoy!

    Hasan

  17. 17. At 3:37 p.m. on 24 jan 2006, Nicholas Gledhill said:

    Another noob. I've been trying to work out how to do this as well.

    I've found a lot of coverage of how to access Access without a DSN using COM but how do you do it from a UNIX platform.

    I'm trying to implement a new intranet, on Linux / Apache, but I need to be able to talk to some legacy Access .mdb files from my PHP web pages.

    Can anyone help?

  18. 18. At 3:39 p.m. on 24 jan 2006, Nicholas Gledhill said:

    Another noob question. I've been trying to work out how to do this as well.

    I've found a lot of coverage of how to access Access without a DSN using COM but how do you do it from a UNIX platform.

    I'm trying to implement a new intranet, on Linux / Apache, but I need to be able to talk to some legacy Access .mdb files from my PHP web pages.

    Can anyone help?

    Cheers, Nick Gledhill.

  19. 19. At 3:44 p.m. on 24 jan 2006, Nicholas Gledhill said:

    An embarrassed noob (with an answer). Having just embarrassed myself by posting the same message twice - I have now found this answer to the question - which I thought might help other people:

    Go to:

    http://aspn.activestate.com/ASPN/Mail/Message/370705

    for details of a good way to solve the MS Access from Linux / Unix problem. Cheers,

    Nick Gledhill

  20. 20. At 10:53 a.m. on 29 jan 2006, Terry Gilliver said:

    php and Microsoft Access. Hi,

    I am new to php and tried the php cookbook example, modified to my own needs.

    I get a 'Cannot open with JET' message or 'Cannot open with Driver' depending on which $conn->open statement I choose. Below is a sample of my code. $db = 'c:\inetpub\wwwroot\experimental\SunflyDatabase.mdb'; $conn = new COM('ADODB.Connection') or exit('Cannot start ADO.');

    $conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.');

  21. 21. At 10:57 a.m. on 29 jan 2006, Terry Gilliver said:

    php and Microsoft Access. Hi,

    I am new to php and tried the php cookbook example, modified to my own needs.

    I get a 'Cannot open with JET' message or 'Cannot open with Driver' depending on which $conn->open statement I choose. Below is a sample of my code. $db = 'c:\inetpub\wwwroot\experimental\SunflyDatabase.mdb'; $conn = new COM('ADODB.Connection') or exit('Cannot start ADO.');

    $conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.');

  22. 22. At 8:25 p.m. on 3 feb 2006, Claudio Xerez said:

    php and Microsoft Access. Hi! i was trying to implement htis code and after some tries i've made it work commenting it like below: $conn = new COM('ADODB.Connection');// or exit('Cannot start ADO.');

    $conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db");// or exit('Cannot open with driver.');

  23. 23. At 12:04 a.m. on 28 mar 2006, Ivan Akcheurov said:

    PHP+Apache+MS ACCESS. Please, can somebody tell me why this script doesn't work for PHP under Apache. I've tried this approach under IIS using ASP, works perfectly, but under Apache it says "Cannot open with jet". Please, help me to connect to DB from PHP under Apache without DSN.

  24. 24. At 3:23 p.m. on 20 apr 2006, Iain Adams said:

    COM??? I am new to this, but was wondering what is COM and is there anywhere I can find documentation on it.

  25. 25. At 8:41 a.m. on 24 apr 2006, Neko Chan said:

    Problem with the code. Hi everyone!

    I'm trying to use this code but i have problems with the sign "->". Everytime it appears, php doesn't work.

    If code is:

    $db = 'C:\Program Files\Microsoft Office\Office11\SAMPLES\Northwind.mdb';

    $conn = new COM('ADODB.Connection') or exit('Cannot start ADO.');

    // Two ways to connect. Choose one.

    $conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.');

    //$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db") or exit('Cannot open with driver.');

    ...

    When i open the page, the only thing it shows is:

    Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.'); //$conn->Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$db") or exit('Cannot open with driver.'); $sql = 'SELECT ProductName, QuantityPerUnit, UnitPrice FROM Products ORDER BY ProductName'; $rs = $conn->Execute($sql); ?> EOF) { ?> MoveNext() ?>

     Product Name   Quantity Per Unit   Unit Price <pre>

Fields['ProductName']->Value ?> Fields['QuantityPerUnit']->Value ?>     Fields['UnitPrice']->Value ?> Close(); $conn->Close(); $rs = null; $conn = null; ?>



So, what is the problem????

Thanks a lot

    </pre>

  26. 26. At 3:02 p.m. on 24 jul 2006, Neil Swartz said:

    Error When trying this code. I copied this code into Dreamweaver and executed it. Depending on the connection string I used I am getting the following errors: Cannot open with Jet Cannot open with driver

    I have MS Office 2003 installed and am using an Apache2 Server with PHP installed.

    Can anyone help???

  27. 27. At 9:29 p.m. on 25 sep 2006, him seyha said:

    can't open database. 'Cannot open with JET' message or 'Cannot open with Driver' depending on which $conn->open statement I choose. Below is a sample of my code. $db = 'c:\inetpub\wwwroot\experimental\SunflyDatabase.mdb'; $conn = new COM('ADODB.Connection') or exit('Cannot start ADO.'); $conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$db") or exit('Cannot open with Jet.');

Sign in to comment