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
Download
Copy to clipboard1 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;
?>
|
Tags: database
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
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
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?
Error Found in Code. Hey, I found an error in the code. At line 29 there should not be the () in MoveNext.
New record. Can a new record be inserted into a table using this method of connection?
Missing escape \? Or missing end ; Have you escaped all \'s with the \ and have you ended the line with a ;?
Add Record. Shure,
$sql = "INSERT INTO TABLE(field1, field2,....) VALUES('$field1, $field2,....)";
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
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);
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
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);
$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 ';'
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?
Questions about ->. I'm just learning PHP. What does the -> mean? Or where can I find out about it? Thanks.
r yjr. rt urt
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.
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:
</pre>
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
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?
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.
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
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.');
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.');
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.');
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.
COM??? I am new to this, but was wondering what is COM and is there anywhere I can find documentation on it.
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() ?>
</pre>
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???
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.');
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?
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