Adding data to database with Access/odbc

Maarten

I have a hard time find any information on how to add data to an Access database using ODBC. Everywhere I look it's about MySQL wich is not availeble to me.

Can anyone explain me how to ADD, EDIT and DELETE or post some links.

Thank in advance and I'll keep on looking 🙂

Frederick

http://us3.php.net/manual/en/ref.uodbc.php
http://www.phpfreaks.com/tutorials/61/0.php
http://www.w3schools.com/php/php_db_odbc.asp

bastien

Access expexts its dates in this format

insert into tablename (datefield)values(#$date#);

and must be of the format mm-dd-yy[yy] to get results that you expect

Maarten

Frederick

I couldn't find any information on the links you gave on how to add, edit or delete data from a database.

Bastien

The code I was tried to use was:

$SQL_Exec_String = "Insert Into Producten (Naam, Prijs, Categorie, Beschrijving) Values ('$naam', '$prijs', '$categorie', '$beschrijving')";
$sql= odbc_exec( $conn, $SQL_Exec_String );

if (!$sql)
{
echo "Error in sql ";
}
else
{
header( 'Location: Producten.php' );
}

odbc_close($conn);

and I always get Error in sql.

bastien

Assuming the db is Access try, also try echoing out the query and running it thru whatever gui you have for your db...ensure that the date format is correct (Access/MSSQL want mm-dd-yyyy, most of the others prefer yyyy-mm-dd and so does PHP)

$SQL_Exec_String = "Insert Into Producten (Naam, Prijs, Categorie, Beschrijving) Values ('$naam', '$prijs', '$categorie', #$beschrijving#)";

If you're not dutch, you're not much 😃

Maarten

Actually I don't have any gui to run the sql through. Only have Access 97 and don't know how to work with that.

Frederick

<?php
// ESTABLISH CONNECTION
$db = odbc_connect("DSN_Name","username","password");

// INSERT QUERY
$sql = "INSERT INTO tablename (field1,field2,field3) VALUES ('$value1','$value2','$value3')";
$exc = odbc_exec($db,$sql) or die (odbc_errormsg());

// UPDATE QUERY
$sql = "UPDATE tablename SET field1='$value1', field2='$value2' WHERE id=$id";
$exc = odbc_exec($db,$sql) or die (odbc_errormsg());

// DELETE QUERY
$sql = "DELETE FROM tablename WHERE id=$id";
$exc = odbc_exec($db,$sql) or die (odbc_errormsg());

// RESULTS QUERY
$sql = "SELECT * FROM tablename WHERE field1 = '$value' ORDER BY field2";
$exc = odbc_exec($db,$sql) or die (odbc_errormsg());
while ($row = odbc_fetch_array($exc)) {
	echo $row["field1"]."<br>";
}
?>

bastien

Originally posted by Maarten
Actually I don't have any gui to run the sql through. Only have Access 97 and don't know how to work with that.

open the access application, open a query window, switch to sql view...paste in the statement...

Maarten

This always has to happen to me, hehe. Somehow it doesn't seem to work when it should work.

Code is as follows:

require("conn.php");
$sql = "INSERT INTO Producten (Naam, Prijs, Categorie, Beschrijving) VALUES ('$naam', '$prijs', '$categorie', '$beschrijving')";
echo $sql;
echo $conn;
$exc = odbc_exec($conn, $sql);
echo $exc;
if (!$exc)
{
echo "Error in sql ";
}
else
{
odbc_close($conn);
header( 'Location: Producten.php' );
}
  odbc_close($conn);

conn.php

<?php
$conn=odbc_connect("DSN_Name","username","password");
if (!$conn)
{
echo "Connection Failed: ";
}
?>

The outturn of this is:
INSERT INTO Producten (Naam, Prijs, Categorie, Beschrijving) VALUES ('Cola', '1', 'Drank', 'Lekker')
Resource id #4
Error in sql

I've been looking for obvious reasons why it wouldn't like like typo's and such but I can't find any 🙁

I did what you asked and pasted the SQL in Access and there it adds a row so no problem there.

Access does change the sql statement into the following:
INSERT INTO Producten ( Naam, Prijs, Categorie, Beschrijving )
SELECT 'Cola', '1', 'Drank', 'Lekker';

bastien

try removing the quotes around the numeric value(s)

INSERT INTO Producten (Naam, Prijs, Categorie, Beschrijving) VALUES ('Cola', 1, 'Drank', 'Lekker')

Acccess is a PoS. Move to mysql if you can

Maarten

Although a numerical value is given the field is set as text in Access.

And I wish I could but for now odbc it is.

Frederick

Change this
$exc = odbc_exec($conn, $sql);

To this
$exc = odbc_exec($conn, $sql) or die (odbc_errormsg());

so you can get an idea of what maybe going on. It will display a message of any error and an area for you to look at.

Maarten

Add

SQL = INSERT INTO Producten (Naam, Prijs, Categorie, Beschrijving) VALUES ('Red Bull', '2', 'Drank', 'Geeft je vleugels.')

Error = [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Edit

SQL= UPDATE Producten SET Naam='Ole Wafer', Prijs='0,25' , Categorie='Snacks', Beschrijving='Wafel omhuld met Melkchocolade en gevuld me crème-vulling.' WHERE ID=1

Error = [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Delete

SQL = DELETE FROM Producten WHERE ID=1

Error = [Microsoft][ODBC Microsoft Access Driver] Could not delete from specified tables.

Maarten

I think my webhost has to resolve this problem. I looked up the errormessage in Google and it seems that the problem might be permissions on database.

I was thinking along those lines aswell yesterday and contacted my webhost (who is just a friend, not a company) to ask if he could check the permission on the database cause I was thinking it might be read/write protected or something along those lines.

So I'll just have to wait for his reply.

Roger_Ramjet

Just an idea. Is this hosted on an NT box? Are you using IIS?

The Access error message 'Operation must use an updateable query' normally applies to an attempt to do an Insert/Update in a query that involves a M:1 Join, or an aggregate function. Yours is running an insert on a table, no join, so it is a 'bogus' error - not unusual in Access.
It can also be generated when the table is locked, or the user does not have write access.

With NT/IIS you need both write privileges on the website (IIS settings) and write privileges on the server directory (NTFS filesystem settings). This is often the problem: pay peanuts and you get monkeys for your system admins.