G.Smith G.Smith - 3 months ago 7
PHP Question

PHP not allowing ACCESS update on certain columns

I am trying to update records in my legacy access system via php, but on certain columns it isn't working. For example the below works :

$query = "UPDATE Valuations SET SalesParticulars = '" . $salesparticulars . "',
Company = '" . $company . "' WHERE Ref=" . $ref;


If I change "Company" to "Name" it doesn't work, even though name is just a column in the database exactly the same as Company.

Basically I can update "Company" but not "Name", even though they are both the same data types

I get this exception, how can I catch it?


Fatal error: Uncaught exception 'com_exception' with message 'Source: Microsoft Access Database Engine
Description: Syntax error in UPDATE statement.' in


This is my full code

// Create an instance of the ADO connection object
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");

// Define the connection string and specify the database driver
$connStr = "PROVIDER=Microsoft.Ace.OLEDB.12.0;Data Source=".realpath("HS_BE.accdb").";";

// Open the connection to the database
$conn->open($connStr);
$name= "gareth";
// Declare the SQL statement that will query the database
$query = "UPDATE Valuations SET SalesParticulars = '" . $salesparticulars . "',
Name = '" . $name . "'
WHERE Ref=" . $ref;

echo $query;

// Execute the SQL statement and return records
$rs = $conn->execute($query);

Answer

That's because Name is a reserved word in Access SQL.

You can wrap it in brackets like this:

$query = "UPDATE Valuations SET SalesParticulars = '" . $salesparticulars . "',
 [Name] = '" . $name . "' WHERE Ref=" . $ref;