Reset AUTO_INCREMENT in PHP on MDB ACCESS database

Question

For a demo purpose, I need after empty all tables in my ACCESS format "database.mdb" using PHP and then reset all IDs AUTO_INCREMENT to 1, but i've problems with resetting PRIMARY KEYS.

It's important i've do all with PHP script.

I tried with:

/* ConnString verso il database da compattare */
$oldConn="Provider=Microsoft.Jet.OLEDB.4.0;";
$oldConn.="Data Source=".$path.$olddb.";";
$oldConn.="Jet OLEDB:Database Password=$oldpass ;";

/* ConnString verso il nuovo database (compattato) */
$newConn="Provider=Microsoft.Jet.OLEDB.4.0;";
$newConn.="Data Source=".$path.$newdb.";";
$newConn.="Jet OLEDB:Database Password=$newpass ;";    
$je=new COM("JRO.JetEngine") or die("Compact failed");
    $je->CompactDatabase($oldConn,$newConn) ;
    $je->Release() ;
    $je= null ;

but i get an error with CompactDatabase().

I also tried with PDO connection and:

$pdo->exec("ALTER TABLE tbl AUTO_INCREMENT = 1");

or

$s = $pdo->prepare("ALTER TABLE tbl MODIFY COLUMN ID INT(10) UNSIGNED AUTO_INCREMENT");

but i get: Syntax error or access violation

Is there any other way to do this?

Possible duplicate of [How to reset AutoIncrement field from Access Database](http://stackoverflow.com/questions/18318594/how-to-reset-autoincrement-field-from-access-database) — Michael Gaskill, Jun 15 '16 at 19:55
I need to do that in PHP script. Not with ASP and not with Microsoft Office Access executable. — Giuseppe Lodi Rizzini, Jun 17 '16 at 07:30

score 0 · Answer 1 · answered Jun 15 '16 at 17:12

0

try this:

ALTER TABLE MyTable ALTER COLUMN MyColumn COUNTER(1,1)

answered Jun 15 '16 at 17:12

random_answer_guy

856
4
11

No success. I get error: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: -1046 [Microsoft][Driver ODBC Microsoft Access] - ID is part of one or more relations. ....I tried delete relationsship of ID with other tables....but same error... – Giuseppe Lodi Rizzini Jun 16 '16 at 07:14
Hmm I "think" it may be because a PDO returns something and a DDL alter statement does not? – random_answer_guy Jun 16 '16 at 14:44

Reset AUTO_INCREMENT in PHP on MDB ACCESS database

1 Answers1