title: run an update on a linked MS Access table using php
body: <p>I have Microsoft Access Database (mdb file) with a table in it (we'll call that table A) that is linked to another Microsoft Access Database (we'll call that table B). I need to be able to update table A using php (which will in turn update table B, since A is linked to B). Please note that the table I'm linking to has a primary key named "Intersection" and it is set to Indexed: Yes (No duplicates).</p>
  
  <p>If I use a simple <code>odbc_connect</code> and <code>odbc_exec</code> to run the following query:</p>
  
  <pre><code>$con= odbc_connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=$mdbFilename", $user, $password)
  $res = odbc_exec($con, "UPDATE tableA SET myfield=NULL WHERE myfield=2439;");
  </code></pre>
  
  <p>However, when I try this on a database with a linked table, I get the following error:</p>
  
  <blockquote>
    <p>Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC
    Microsoft Access Driver] Operation must use an updateable query., SQL
    state S1000 in SQLExecDirect in G:\web\libs\test.php on line 413</p>
  </blockquote>
  
  <p>For some reason it seems that my database is not updateable even though it should be. It has a primary key defined and is a simple table (except for the fact that it's linked). Originally I was getting the following error:</p>
  
  <blockquote>
    <p>Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC
    Microsoft Access Driver] 'H:\test\Database\test.mdb' is not a valid
    path. Make sure that the path name is spelled correctly and that you
    are connected to the server on which the file resides., SQL state
    S1009 in SQLExecDirect in G:\web\libs\test.php on line 413</p>
  </blockquote>
  
  <p>But then I changed my linked table so instead of using drive letters to navigate to the linked table, I used the absolute \server\ path, which seemed to fix that problem, however not it is not able to be updated.</p>
  
  <p>In case it helps, to create the Linked table, all I did was (Using MS Access 2010) I hit the "External Data" tab, then the "Access" (button), then I made sure to hit "Link to the source by creating a linked table" (option) and I selected the other database which contains the original table.</p>
  
  <p><strong>Is there some way to run an update on a linked access table using php?</strong></p>
  
  <p>Again, if you have a MS Access linked table that is linked to another table, and these tables have a primary key that is properly indexed. You CANNOT update the table using the following above PHP code (even though it works completely fine on an table which is not linked).</p>
  
  <p>Please note, I have tested this with a fresh accdb database file as well, and if it is a linked table, it seems it will become read-only if you try to update the table using odbc_exec, however it does return data just fine, I just can't insert or update records.</p>
  
  <p>Thank you.</p>
  
  <p>[UPDATE]</p>
  
  <p>Our server is running Windows Server 2003 with apache, and php.  So I set up a Windows 7 machine with apache, and php, and it <strong>IS</strong> able to update a linked database without a problem, so I suspect my problem (since it's the only difference between the machines that could affect this), is that the Win2003 server is running odbc v3.5 whereas my win7 Machine is running odbc v6.1.  I'm still looking into this further and will update this post if I find a solution to getting it working on our Windows Server 2003 machine.</p>