Connect to Actinic via ODBC

[Tripwire]

I need to change prices based on manufacturer which is a custom propertie. Having looked in the access db it seems prices are in the products table and manufacturer is in the productproperties table.

Would it be possible to build a php page which updates the Actinic database via ODBC?

I understand how to build a query in php to get the info I need to update but I haven't a clue how to do this in access2000

My boss is barking at me that he needs to update 100's of product prices as the manufacturers have raised their prices and he doesn't want to sit down for days on end manually changing prices!

Any help would be greatly appreciated.

Thanks

[cdicken]

I don't know about Access either so I just tend to copy the data I want out of Access and paste it into Excel.

There is a knowledge base article HERE that gives info about formulas for changing prices en masse. Hope that helps.

[chrisbrown]

Remember that Actinic is not a network application. If you go down the route that you describe (and why not?) you should not have the database open in Actinic while the PHP query is running and viceversa. Actinic caches data when it has the database open and therefore using the 2 programs simultaneously could corrupt the database.

[Tripwire]

Thanks for the pointer Chris. I do plan on only working from a backup of the database.
My first task is to get IIS and php up and running on the windows 2000 machine Actinic is installed on, hopefully it should be pretty straight forward fingers crossed

[peterB]

Tripwire,
here is a simple script to connect your Actinic database through ODBC and php. This will pull out all the customers from the customer table and display their names.

On your pc if you select the control panel thenodbc sources you will see a User Dsn set up called ActinicCatalog6, I find this does not always work, select the Systemtab and create a new dsn,
I have called it dsnActinic.

<?
$username = "";
$password = "";

#1 open database connection
#dsnActinic is the System DSN name
$connection = odbc_connect('dsnActinic',$password,$username);

if (!$connection)
{
exit("Connection failed: ". $connection);
}
#2 run the query through the connection
#select everything from the customer table
$sql="SELECT * FROM customer";
$rs=odbc_exec($connection,$sql);
if (!$rs)
{
exit ("Error in sql");
}

While (odbc_fetch_row($rs))
{
#sName is the row that holds the customer names.
$theCustomerName = odbc_result($rs,"sName");
echo ("$theCustomerName");
echo ("<BR>");
}

#close the connection
odbc_close($connection);
?>

All you need to do now is run a update script once you have worked out whhich prices need updating, if you have any more problems, i will keep a look out here for a day or two.

Hope this helps

Peter

[Tripwire]

Thanks Peter that is great help and its good to know someone else has already done this kind of thing before
I won't actually be working on this until next Monday now but I will post here with my results and probs if I get any.

[Tripwire]

In the Actinic 'Product' table the Product reference field is named exactly that with a space between Product and reference. This threw up an error when using a php odbc SELECT statement:
SELECT * FROM Product WHERE Product reference='$blahblah';
The solution was to open the Access db in Access 2000 and rename the column to ProductReference but I would rather not have to do that. Anyone here know why that might happen?
Thanks

[chrisbrown]

The space in the name is seen as a delimiter and therefore the statement is syntatically incorrect.

I'm not sure if this will also work in PHP but in Microsoft languages (e.g. VB) you need to put the field name in quotes in order to address fields with spaces in the name. The SQL would therefore become:

SELECT * FROM Product WHERE 'Product reference'='$blahblah';

[Note to self: learn PHP and soon]

[peterB]

Try this script....

In answer to you question about 'Product Reference' I put it into a variable and this seemed to work ok.
This script changes all the prices by 200pence ie £2.00.
It's not perfect but it works ok.


<?
$username = "";
$password = "";
#set a price of £2.00 ie 200 pence
$price = 200;

#this is held as a text field in the database.
$productReference = "Product reference";

#1 open database connection
#dsnActinic is the System DSN name
$connection = odbc_connect('dsnActinic',$password,$username);

if (!$connection)
{
exit("Connection failed: ". $connection);
}
#2 run the query through the connection
#select everything from the customer table
$sql="SELECT * FROM PRODUCT";

$rs=odbc_exec($connection,$sql);
if (!$rs)
{
exit ("Error in sql");
}

#fetch row
While (odbc_fetch_row($rs))
{
$thePrice = odbc_result($rs,"Price");
$theProdRef = odbc_result($rs,"Product reference");
#$theProdRef = "1"
if ($thePrice == 0)
{
#ignore
}
else
{
$thePrice = $thePrice + $price;
#$sqlUpdatePrice = "UPDATE Product SET Price = Price + 200 WHERE $shortDescription = $theProdRef";
$sqlUpdatePrice = "UPDATE Product SET Price = $thePrice WHERE '$productReference' = '$theProdRef' ";
echo $sqlUpdatePrice;
echo ("<BR>");
$cur = odbc_exec($connection,$sqlUpdatePrice);
if (!$cur)
{
exit( "Error in odbc_exec" );
}

#display the new price.
echo ("prod ref = $theProdRef");
echo ("...");
echo ("new price = $thePrice");
echo ("<BR>");
}
}

#close the connection
odbc_close($connection);
?>

If you are still struggling but have actual access to the database would it be easier to create a query within MS Access then just run that to do the update?

Regards
Peter.

[Tripwire]

Thanks for that Peter, I tried putting into a variable but no joy with that, weird how you can get it to work, heres my script which is to get prices based on a custom property and save it as a text file for updating in Excel. I have written an update script ready to read an excel saved tab delimited file but again I come up against the same problem. Do you know how to do all this in Access Peter?

Thanks

<?php
$username = "";
$password = "";
@$connection = odbc_connect('Actinic',$username,$password);

$filename = "data.txt";
fopen($filename, 'w');
$productreference = "Product reference";
if ($_POST['action']=="doit") {
$keyword=$_POST['manufacturer'];
    @$sql="SELECT * FROM ProductProperties WHERE sString1='$keyword'";
    @$rs=odbc_exec($connection,$sql);    
        
        While (odbc_fetch_row($rs))
        {
        $sProductRef = odbc_result($rs,"sProductRef");
        $sString1 = odbc_result($rs,"sString1");
        $sql2="SELECT * FROM Product WHERE '$productreference'='$sProductRef'";
        $rs2=odbc_exec($connection,$sql2);
        $Price = odbc_result($rs2,"Price");
        
        $content = "$sProductRef|$Price\r\n";
        
        if (is_writable($filename)) {
            if (!$handle = fopen($filename, 'a')) {
             echo "Cannot open file ($filename)";
             exit;
               }

               if (fwrite($handle, $content) === FALSE) {
               echo "Cannot write to file ($filename)";
               exit;
               }
   
               fclose($handle);
                   
        } else {
           echo "The file $filename is not writable";
        }

        
        }
            
header('Cache-Control: no-cache, must-revalidate');
header('Pragma: no-cache');
header("Content-type: application/force-download\nContent-Disposition: inline; filename=\"".$filename."\"\nContent-length: ".(string)(filesize($filename)));
readfile("$filename");
    
}

odbc_close($connection);
?>

[peterB]

HI Tripwire, sorry to hear you are struggling with this, if you open up the actinic database within access, select the correct table, then select Query from the Objects table on the left, then select "Create Query In Design View" this opens a selection of tables and criteria to use.

Once the query is designed you will see a red exclamation mark on the toolbar, click on this to run the query.

After reading your last post I'm not sure if what you 're trying to achieve is a little to complicated to do in access.

Unless anyone else has any ideas, perhaps the only other solution is to rename the column to Productreference.

Sorry if this is not a lot of help.

Peter.

[Jan]

Haven't read this whole thread through properly but in access you would have to say [Product Reference] not 'Product Reference' as the latter indicates a string with the text Product reference rather than a field.

Regards,