php and access

[rowantrimmer]

Is it possible to read from access tables using php functions ?

[LordShryku]

yes

[AstroTeg]

If you're on a Windows machine and the PHP script is running off the same machine, you can use COM and ADO to access it.

[sijis]

I've just recently been playing with PHP, MS Access on a Windows server. So far i'm just able to read the database and not update/remove/insert data into it..

here's the code that should work for you.

function objConn(){
	global $objConn;
	$objConn = new COM('ADODB.Connection');

// you need both of these, if you use
//$objConn->Provider = 'Microsoft.Jet.OLEDB.4.0';
//$objConn->Open(DATA_DB);

// either one work seperately.
$objConn->Open('DRIVER={Microsoft Access Driver (*.mdb)}; DBQ='.DATA_DB);
//$objConn->Open('Provider=Microsoft.Jet.OLEDB.4.0; Data Source='.DATA_DB);
}


function objClose(&$objConn, &$objRS){

if(is_resource($objRS)){
	$objRS->Close();
	$objRS->Release();
	$objRS = null;
}

if(is_resource($objConn)){
	$objConn->Close();
	$objConn->Release();
	$objConn = null;
}

//global $objConn;
if(isset($objConn)){
	unset($objConn);	
	//echo 'Unset';
}
}

// usage; 2 examples

objConn();
$objRS = $objConn->Execute("select * from Table1");

$id = $objRS->Fields('id');
while (!$objRS->EOF) {
	print "$id->value<br />\n";
	$objRS->MoveNext();
}
objClose($objConn, $objRS);


// or something like this, i find easier
$id = $objRS->Fields('field_name');
$id = $id->value;
while (!$objRS->EOF) {
	echo "$id<br />\n";
	$objRS->MoveNext();
}
objClose($objConn, $objRS);

in the objConn() function i have a few lines that are commented out. These lines do work.. instructions are commented above them.

hope this helps.

(if you find out how to insert data to MS Access, please post the code, i'd love to see it)

[squison]

Inserting into an access db is just like any other db and is done with the ->Execute() function like normal. For some reason though I've noticed you have to do another query directly after your insert/update in order to make it commit.

$db->Execute("INSERT INTO [table] ([field1], [field2]) VALUES ("moo", "cow")");
$res=$db->Execute("SELECT max(id) from [table]");

Its dumb, and I don't know why you have to have that second query, but it works.

If you have other problems, check and make sure the server & IUSR_ANONYMOUS (I think that's what it is.. Everyone works too) have write access to the access file.

[greg252]

If you plan on using your DB seriously, dump Access and go with mySQL. According to the SQL Server Manual (around page 76)... Access is not designed or intened to be used as a database server.

[sijis]

squison

thanks alot about the tip for updating/inserting into access. i'll try it later this weekend. I figured it would just be the same as $db->Execute($sql); but wasn't sure why it wasn't working.

greg252
good point about not making MS Access a database server, but sometimes it all you have. besides, it doesn't hurt to play around with access and php. 🙂 Its all just a learning experience.