CODEWITHSUNDEEP
phpms-accessadodb
Gen
Gen

Reputation: 95

Check access database if table exists

Is there a way to check if table exists in mdb file? Im using PHP and ADOdb connection to connect.

here's my code

$conn = new COM("ADODB.Connection");
$conn->open("DRIVER={Microsoft Access Driver (*.mdb)}; PWD=somepassword; DBQ=".dirname(__FILE__)."\filename.MDB;");
if (table exists){ \\ where i need the code to check
    $createtable = $conn->execute("CREATE TABLE $tblname (fieldsample CHAR(1))");
}else{
    $insertqry = $conn->execute("INSERT INTO $tblname (field) VALUES (value)");
}

Been browsing all day for the syntax but all I've seen so far are all for VB and ASP.

Upvotes: 0

Views: 1921

Answers (3)

Erwin Moller
Erwin Moller

Reputation: 2408

What about a SELECT * on the table with LIMIT 1, and catch the error/exception?

(LIMIT is called TOP in ACCESS.)

So something like:

SELECT TOP 1 * FROM <YOURTABLE>;

I learned to be practical when it comes to ACCESS, because it is barely a database.

Upvotes: 1

Heinzi
Heinzi

Reputation: 172260

With COM available, you'd add a reference to DAO and check the TableDefs collection. Since you cannot do that, you'll have to resort to querying the hidden MSysObjects table:

SELECT COUNT(*) FROM MSysObjects WHERE Name = 'myTable' AND Type = 1

(More details on this system table: Using MSysObjects)

EDIT: According to this question, you can access the schema data using ADO, which should allow you to determine whether the table exists or not:

$rs_meta = $conn->OpenSchema(20, array(Null, Null, Null, "TABLE"));

Upvotes: 1

lorraine batol
lorraine batol

Reputation: 6281

$createtable = $conn->execute("CREATE TABLE $tblname (fieldsample CHAR(1))");

You could actually modify the query to achieve the results that you want:

$createtable = $conn->execute("CREATE TABLE IF NOT EXISTS $tblname (fieldsample CHAR(1))");

This query will be executed (create the table) if it's not there yet. If its there already, this query will not be executed.

Upvotes: 0

Related Questions