...

View Full Version : The joy of MSAccess through ODBC



PAUL HOOPER
05-18-2004, 01:27 PM
:confused: I having problems inserting/updating information in a msaccess database throught php.
running php 4.3.4/windows XP/access 2000

I get the following message

Warning: SQL error: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query., SQL state S1000 in SQLExecDirect in

There is no problem reading information from the database.

I have try

1. creating a new db with just one record.
2. adding security to the database
3. adding a index to the table
4. setting the read only flag in the (just to see what happends)
5. setting the exclusive flag on/off
6. updating the microsoft access components to 2.8
also download KB829558 from microsoft web site

can anybody help


Code Example

The following code is run on a single table microsoft access database
the select command works ok and returns data item but the insert
command fails with the message

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query., SQL state S1000 in SQLExecDirect in d:\test.php on line 38 failed to update action table -[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.


<?php

$currentdb = odbc_connect("newdb","","");

if ($currentdb == 0)
{
die("Cannot connect".odbc_error($currentdb));
}
else
{
$sqlstring = "select * from name";
$currentres = odbc_exec( $currentdb,$sqlstring);

if ($currentres == FALSE)
{
die("Cannot exec read".odbc_errormsg($currentdb));
}
else
{
$s = odbc_result($currentres,1);
print "$s\n";
print "</br>";
}

$sqlstring = "INSERT INTO name VALUES ('paul2')";

print "$sqlstring\n";
print "</br>";

$currentres = odbc_prepare ( $currentdb , $sqlstring);

if ($currentres == FALSE)
{
die("Cannot prepare".odbc_errormsg($currentdb));
}

if (odbc_exec($currentdb , $sqlstring ) == false)
{
die("failed to update action table -".odbc_errormsg($currentdb));
}
}
?>

Darknight
05-18-2004, 06:11 PM
Could you post some code for us to review please?

sad69
05-18-2004, 08:16 PM
Take a look at this tutorial, it might help:
http://www.phpfreaks.com/tutorials/61/0.php

Sadiq.

PAUL HOOPER
05-20-2004, 05:28 PM
I have look at the tutor but it only covers selection from a database

sad69
05-20-2004, 05:52 PM
I should have read your code over.. it's incomplete:


$sqlstring = "INSERT INTO name (columnName) VALUES ('paul2')";


You have to specify the columns in your insert statement -- so it's an SQL error.

Try that and hopefully that fixes your troubles.

Sadiq.

PAUL HOOPER
05-21-2004, 03:50 PM
Syntactically it is correct if you are defining all the fields for the table (Ok so its not best practice)
I did try it as it alway thing that you think you know that cause the problem. but no joy….. :(

sad69
05-21-2004, 05:31 PM
Hmm..don't really know what to tell you.

Have you tried executing the SQL statement (the insert..) in Access directly? And did it work? Also, you prepare the insert, but not the select (not that I know what prepare means..) but would that have an affect?

Sadiq.

sad69
05-21-2004, 05:33 PM
Also, I don't know much about Access, but I wonder about this line:


$currentdb = odbc_connect("newdb","","");


You haven't provided a username/password. Could that prevent you from getting write access to the database? I don't know that it will or won't, just talking 'out loud' here (as if that's possible..)

Also, what is the type of the field your inserting into? Is it text? And do you want to insert 'paul2' or "$paul2" or $paul2 (no quotes in the last one as it is a number or something other than text)?

Sadiq.

PAUL HOOPER
05-24-2004, 11:46 AM
The prepare statement is a bit of a red herring, there another execute command that uses a pre validated statement. i have try adding a username and password and then running the php but it has no effect

carl_mcdade
05-26-2004, 09:13 AM
This usually is a windows security issue. Without knowing the windows platform I would say right click on the database file and check to make sure that user is "everyone" and all rights are given. Then check the webserver to make sure that the folder that the file is in is accessible. You can also check to make sure INET_USER has permissions on the file or folder that the website is in.

PAUL HOOPER
06-07-2004, 10:04 AM
Ok got it working , carl_mcdade was right about the file permisions.
As a test i moved the .MDB file to the shared folder in XP and the code was happy to write data the to database :thumbsup:

Having been given this thread i found Microsoft Knowledge Base Articles
that may also be usefull if any one has a similar problem

Microsoft Knowledge Base Article - 175168
Microsoft Knowledge Base Article - 316475
Microsoft Knowledge Base Article - 830977



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum