[Q] Setting up connection between MS Access and PHP...

[CyanBlue]

Howdy guys... :)

I have a question... I have searched forum and google, but I couldn't find good answer on this question... :(

This is what I want to do... I have to create a connection to the MS Access database from Flash MX via PHP... To test this locally, I am on W2K and IIS 5.0... and eventually progarm will be running on WXP server with DSN... (This part, I will be needing some more searching...) According to the articles that I have read, I will be needing ODBC driver installed on the machine for PHP to have an access to the MS Access database... So, I have been to the Microsoft's Universal Data Access Web Site to find the ODBC driver, http://www.microsoft.com/data, where I have been more confused... :(

I was able to find some information on Microsoft Data Access Components (MDAC), OLE DB, ADO, and ODBC... Basicaly what it said on the page is that MDAC, OLE DB, ADO all works on top of ODBC which makes sense so far... Well... The confusing part is that I cannot find any link where I can download ODBC driver for Windows... According to the search from this forum, somebody suggested that I can basically install ODBC driver with MS Office, but I don't see that option on my machine...(I don't have ODBC icon in the Control Panel... and I don't see any ODBC listed in the Services section from the Administrative Tools...) So... I was wondering if anybody know where I can download ODBC driver for W2K and WXP...

Okay... Let's just say that somebody gave me the link and I installed ODBC driver in my computer... I will be needing ADO set up so that I could actually call MS Access database with PHP... Am I right???

I have done several MySQL database job with PHP, but MS Access is the first time... Please correct me if I am wrong... With MySQL, the MySQL server should be running all the time, but there is no need of server in case of MS Access, am I right??? I just want to store the form data from the Flash to the MS Access database such as survey or contact form so that people can utilize that database later on...

Any help would be appreciated...
Thank you and have a nice day... :)
Jason

[freddycodes]

Here is an example of how to do simple selects from MS Access with PHP implementing COM objects. I will leave it up to you to figure how to do inserts and updates.


<?php
$db_conn = new COM("ADODB.Connection");
$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./Northwind.mdb").";";
$db_conn->open($connstr);
$rS = $db_conn->execute("SELECT * FROM Employees");
$f1 = $rS->Fields(0);
$f2 = $rS->Fields(1);
while (!$rS->EOF)
{
print $f1->value." ".$f2->value."<br />\n";
$rS->MoveNext();
}
$rS->Close();
$db_conn->Close();
?>

[CyanBlue]

Thank you, freddycodes, for the reply... :)

Basically I need to set up the ODBC first to get that script working, right??? Any idea where I can download that ODBC driver for W2K???

[freddycodes]

You don't need any ODBC. It's already there. this is an example of a DSN-less connection.

[CyanBlue]

Ha... Yes... It worked... Thank you... :)

Do you think this DSN-less connection would work the same way over the network??? I mean if I want to access the MS Access database that is sitting in the server...

I guess I have wasted 3 days looking for the ODBC driver... Stupid me... :(

[freddycodes]

If its on the same machine as the PHP script is on, this will work. Also be advised that in order for PHP to insert records to the database, the user running the php process needs write access to the file.

If its two separate machines just set up a new DSN for the datasource in the ODBC manager(part of windows). Nothing to download as far as I know.

[operator]

I have been looking for an example for days thx freddycodes.

[Cota]

Just a few side notes for you CyanBlue, Access is very limited. For example, it will not support more than 4 concurrent connections, after that, everyone else gets locked out. Also, Access doesnt support all the "normal" SQL stuff, such as limited "join" statements. Be prepared for a headache with Access.

[CyanBlue]

Hehe... Thanks for the reply, Cota... :)
I've read the original question again and I don't know why I needed Access... Maybe I was just experimenting something... Can't remember what it was though... :)

[Cota]

I wish someone would have warned me before I started messing with Access......

[CyanBlue]

Haha... Yeah... I hear you... But sometimes, you are required to use that database whether you like it or not... It all depends on who your client is and how they listen to you... :)

[Cota]

And as we all know, clients just dont listen. They want what they want.

[SKJoy2001]

Can someone tell me if this DSN less handling with PHP/MSAccess works fine on non Windows servers? Thanks in advance.

[mxgen]

i think that MS Access will run on windows platforms only... so perhaps a PHP/MySQL DSN Less script will work on other platforms.. but hey, i could be wrong, tho...

still, hope this helps..

[sugar]

I im using the same script as freddycodes of this topic, and i have the folow question:
Its possible to use the same script but with the dabase in a network drive, not in the c: drive just like:
$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("y:/Northwind.mdb").";";

Thanks in advance for imputs.

Aldo

[Cota]

usually something like

"Data Source=\\GATEWAY\C\folder\file.mdb

Where, in this case, GATEWAY is the name of the machine hosting the file. They must be in the same workgroup.

[sugar]

usually something like

"Data Source=\\GATEWAY\C\folder\file.mdb

Where, in this case, GATEWAY is the name of the machine hosting the file. They must be in the same workgroup.

I tried that with no results, my path would be: \\nas1\workware\OrderManager\Crafta.mdb
and yes, its in the same workgroup...

what else can try?

Thanks

[Cota]

workware is the name of a drive? like C: is now workware?

[sugar]

i think yes because its a samba (unix) shared resource, not a windows shared folder...

[Cota]

make sure that path is correct..other than that, I cant be much help, dont know anything about samba....but the above would work for windows..

[sugar]

im searching for the correct path used for samba

thanks!

ALDO

[sugar]

This is the scneario, i have a unix samba share in my network, there are all the office stuff, including a share to a Ms access database wich its accessed from many windows clients.
Now i need to access that ms access database from http, so i installed apache+php in a windows server in the same workgroup, i did succesfully test when the ms access database was on the local windows server, but once i tried with the database in the actual server it didnt worked, i found few scripts in google about odbc, ole, and dsn-less connections, all of them worked when database resides in the local machine, but don work when database is in the actual unix samba share.

This is the script used with local machine test:

//$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\POS-SERVER\C$\om\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\POS-SERVER\C$\om");

this is the script used for samba share, it didnt work:
//$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\nas1\workware\OrderManager\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\nas1\workware\OrderManager");

What can I do? any adcvices will be apreciated/.

[sugar]

The thing was solved. I debbuged using the realpath php function...

I changed the user that apache uses to logon, to a samba authorized user acconunt, not the system account, and with this string:

$db_connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("\\\\nas1\\workware\\OrderManager\\xxafta.mdb") ." ;DefaultDir=". realpath("\\\\nas1\\workware\\OrderManager");

thanks to all!!!

[anurag.kulshrestha]

hi , i m anurag. help me plz ....

i want know the way to connect with mysql database using for php application. and i have another database in MSAccess in remote location in LAN . and i want to connect MSAccess database table with Mysql batabase to fatch data in mysql db table .
in access database cardv3.mdb and table is IOData
and in mysql there is Leave_attendence table . and i want to fatch data from MSAccess tabe IOdata into Leave_attendence

i make same structure of table
and applying coding is
<?php
$dns="CardV3"
$db_conn = new COM("ADODB.Connection"); :o
$dns = "DRIVER={Microsoft Access Driver (*.mdb)}; Server=192.168.3.49;Database=CardV3;";
$db_conn->open($dns);
$sqlconnect=odbc_connect($dsn);

$sqlquery="select * from dbo.IOData where IODate>='16-Mar-2009' AND HolderNo IN $HolderNo";
//echo "query--->$sqlquery";
$process=odbc_exec($sqlconnect, $sqlquery);
$i=1;
$j=0;
while(odbc_fetch_row($process))
{
$i=$i+1;
}
$process=odbc_exec($sqlconnect, $sqlquery);
while(odbc_fetch_row($process))
{
if($j<$i)
{
$Attendencedetail[$j][1]= odbc_result($process,"IODate");
$Attendencedetail[$j][2]= odbc_result($process,"HolderNo");
$Attendencedetail[$j][3]= odbc_result($process,"CardNo");
$Attendencedetail[$j][4]= odbc_result($process,"HolderName);
$Attendencedetail[$j][5]= odbc_result($process,"DepartmentNo");
$Attendencedetail[$j][7]= odbc_result($process,"IOStutus");
$Attendencedetail[$j][8]= odbc_result($process,"IOTime");
$j++;
}
}
odbc_close($sqlconnect);
?>
?>
<?php
echo "<body onload='resizeWindow1()' bgcolor=rgb(250,250,250)>";
$j=0;

while($j<$i-1)
{
$IODate1=$Attendencedetail[$j][1];
$arr=split(" ",$IODate1); // splitting the array
$IODate=$arr[0]; // first element of the array is month
//echo "IODate-->$IODate";
if($IODate=="")
{
$IODate="00-00-0000";
}

$HolderNo=$Attendencedetail[$j][2];
$CardNo=$Attendencedetail[$j][3];
$HolderName=$Attendencedetail[$j][4];
$DepartmentNo=$Attendencedetail[$j][5];
$IOStutus=$Attendencedetail[$j][7];
$IOTime=$Attendencedetail[$j][8];

$DBhost = "localhost";
$DBuser='root';
$DBpass='abcdef';
$DBName='helixtest';
$table1='leave_attendance';

mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
@mysql_select_db("$DBName") or die("Unable to select database $DBName");

if($Totalcount==0)
{
$sqlquery= "INSERT INTO $table1 values('$IODate','$HolderNo','$CardNo','$HolderNam e','$DepartmentNo','$IOStutus',' $IOTime')";
$results=mysql_query($sqlquery) or die(mysql_error());
}
}
alert_message("WAS-LMS-Attendance Updated");
?>