If col1 is a text column then use ... WHERE [col1] = 'some text' (note the use of quotes) for an exact match.
... WHERE [col1] LIKE 'xxx' is equivalent to ... WHERE [col1] = 'xxx'.
When using LIKE the wildcard character % matches zero or more characters, and _ matches one character, so
... WHERE [col1] LIKE 'To%'
should match "Toronto", "Tokyo", "Toledo", etc. ...
Edit
The man page for the mdb-sql command of MDB Tools does claim to support LIKE, so I put together a test .mdb file with a table named [Clients] that contained
ID LastName FirstName Email
-- ---------- -------------- ------------------
1 Thompson Gord [email protected]
2 Loblaw Bob [email protected]
3 Kingsley Hank [email protected]
4 Thompson Hunter S. [email protected]
I did sudo apt-get install mdbtools on my test server (Ubuntu 12.04.02), uploaded the .mdb file and did the following
gord@pingu:~$ mdb-sql -p ~/ubuTest2003.mdb
1 => SELECT ID, LastName FROM Clients
2 => go
ID LastName
1 Thompson
2 Loblaw
3 Kingsley
4 Thompson
4 Rows retrieved
1 => SELECT ID, LastName FROM Clients WHERE LastName LIKE 'Thomp%'
2 => go
ID LastName
1 Thompson
4 Thompson
2 Rows retrieved
If you are having difficulty with WHERE clauses I would suggest running a similar test (with a small sample dataset) on your system using mdb-sql to see if WHERE clauses work in that context. If they don't work, then your MDB Tools is broken. If they do work, then we'll need to investigate further.
Edit
I spent some time trying to recreate this issue in PHP on my Ubuntu 12.04 test server. I was unable to do so, only because I was unable to get the odbc_ functions in PHP to work with mdbtools at all. I could establish a "valid" connection (no errors) and I could "execute" a query (again, no errors), but I could not get any query to actually return results, not even SELECT * FROM Clients.
In searching for assistance with my problem I happened upon the Stack Overflow article here, which further discouraged me from pursuing the matter further.
Instead, since this is apparently an active Access database hosted on a Windows machine, I personally would opt for using ODBTP. It is a free (GPL) TCP/IP protocol that allows you to pass queries to a Windows machine, which then submits the query via its ODBC driver and passes the results back to you.
It does require that a service be installed and run on the Windows machine, and the PHP client component must be compiled on Linux boxes, but if those requirements aren't too daunting then it is a pretty robust solution to this type of problem. (I have used it a few times in the past.)
And while it is true that ODBTP hasn't been updated for several years, it still works: I tested it just now with my LAMP server running the following PHP script to query an .mdb file residing on my older Vista notebook:
<?php
echo '<html><body><pre>';
echo "ODBTP test:\n";
$con = odbtp_connect('192.168.1.254',
'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\__tmp\\ubuTest.mdb;UID=admin;PWD=;');
if (!$con) die('_connect error: ' . odbtp_get_error($con));
$rst = odbtp_query("SELECT * FROM Clients WHERE LastName LIKE 'Thomp%'");
if (!$rst) die('_query error: ' . odbtp_get_error($con));
while ($row = odbtp_fetch_assoc($rst)) {
echo $row['ID'] . ': ' . $row['LastName'] . "\n";
}
echo '</pre></body></html>';
The results as displayed in my browser window are
ODBTP test:
1: Thompson
4: Thompson
