Welcome, guest | Sign In | My Account | Store | Cart

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, 44 lines
 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;

?>

29 comments

Pier Luigi 18 years, 8 months ago  # | flag

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

Bjørn Atle Vorland 18 years, 1 month ago  # | flag

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

Gregory Carlisle 17 years, 11 months ago  # | flag

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?

Gregory Carlisle 17 years, 11 months ago  # | flag

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

Geoffrey Hyson 17 years, 8 months ago  # | flag

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

Rob MacDonald 17 years, 7 months ago  # | flag

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

Mogens Melander 17 years, 7 months ago  # | flag

Add Record. Shure,

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

Mumthaz Sidik 16 years, 8 months ago  # | flag
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);

Mumthaz Sidik 16 years, 8 months ago  # | flag
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);

Rafi Abdul 16 years, 7 months ago  # | flag

$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 ';'

J S 16 years, 7 months ago  # | flag

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?

Terry Kosowick 16 years, 6 months ago  # | flag

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

Vu Ton That 16 years, 3 months ago  # | flag

r yjr. rt urt

Flávio Maia 15 years, 11 months ago  # | flag

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.

Hasan 15 years, 11 months ago  # | flag

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>

Hasan 15 years, 11 months ago  # | flag

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

Nicholas Gledhill 15 years, 10 months ago  # | flag

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?

Nicholas Gledhill 15 years, 10 months ago  # | flag

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.

Nicholas Gledhill 15 years, 10 months ago  # | flag

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

Terry Gilliver 15 years, 10 months ago  # | flag

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.');

Terry Gilliver 15 years, 10 months ago  # | flag

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.');

Claudio Xerez 15 years, 10 months ago  # | flag

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.');

Ivan Akcheurov 15 years, 8 months ago  # | flag

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.

Iain Adams 15 years, 7 months ago  # | flag

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

Neko Chan 15 years, 7 months ago  # | flag

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>

Neil Swartz 15 years, 4 months ago  # | flag

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???

him seyha 15 years, 2 months ago  # | flag

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.');

girishpalmah 12 years, 12 months ago  # | flag

Hi, I am getting the error Fatal error: Class 'COM' not found in /home/content/a/2/v/a2vws/html/zohib/Girish/index.php on line 22

Anyone knows why am I geetting this error and how can I fix it?

Rod F 9 years, 10 months ago  # | flag

I can tell you there is a major issue with this code. It may work for a little while but as soon as heavy users start hitting this connection it will cause all sorts of issues.

The author forgot:

$rs = new COM("ADODB.Recordset");

This should be placed above the first instance of $rs

Created by Erick Guanlao on Wed, 20 Nov 2002 (MIT)
PHP recipes (51)
Erick Guanlao's recipes (2)

Required Modules

  • (none specified)

Other Information and Tasks