Image attach to Access Database

[tn04462]

I tried using addrecord, and accessupdaterecord from the access UDF but i had an error.. so i tried this method

#include <Access.au3>

expandcollapse popup

$adSource = "C:\Scripts\dbadev.accdb"
$adTable = "[dba-item images]"
$rData = "testdata"
$adCol = 'owner'
$adData="C:\Scripts\blue.png"
   $objErr = ObjEvent("AutoIt.Error","MyErrFunc")

$Numeric = "141"
$SQL  = "UPDATE [dba-item images] SET Image1 = '" & $adData & "' WHERE ID = " & $Numeric & ";"
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
$adCursorType=3
$oADO = _dbOpen($adSource)
$oRec = ObjCreate ("ADODB.Recordset")
$oRec.Open("SELECT * FROM " & $adTable, $oADO, $adOpenStatic, $adLockOptimistic)
$oADO.Execute($sql)


Func MyErrFunc()
    $hexnum=hex($objErr.number,8)

    Msgbox(0,"","We intercepted a COM Error!!"      & @CRLF                & @CRLF & _
                 "err.description is: "    & $objErr.description    & @CRLF & _
                 "err.windescription is: " & $objErr.windescription & @CRLF & _
                 "err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF & _
                 "err.scriptline is: "   & $objErr.scriptline    & @CRLF & _
                 "err.number is: "       & $hexnum               & @CRLF & _
                 "err.source is: "       & $objErr.source        & @CRLF & _
                 "err.helpfile is: "       & $objErr.helpfile      & @CRLF & _
                 "err.helpcontext is: "    & $objErr.helpcontext _
                )

ConsoleWrite ("We intercepted a COM Error!!"                     & @CRLF)
ConsoleWrite ("err.description is: "    & $objErr.description    & @CRLF)
ConsoleWrite ("err.windescription is: " & $objErr.windescription & @CRLF)
ConsoleWrite ("err.lastdllerror is: "   & $objErr.lastdllerror   & @CRLF)
ConsoleWrite ("err.scriptline is: "  & $objErr.scriptline    & @CRLF)
ConsoleWrite ("err.number is: "      & $hexnum              & @CRLF)
ConsoleWrite ("err.source is: "      & $objErr.source        & @CRLF)
ConsoleWrite ("err.helpfile is: "      & $objErr.helpfile      & @CRLF)
ConsoleWrite ("err.helpcontext is: "    & $objErr.helpcontext    & @CRLF)
ConsoleWrite (@CRLF)

    SetError(1)
EndFunc

and i got :

We intercepted a COM Error!!

err.description is: An UPDATE or DELETE query cannot contain a multi-valued field.

fields are key|name|image1|image2

is there any method to adding an image attachment using SQL?

[Juvigy]

I think better way is to add the image full path on the harddisk to the SQL database (c:imagesimage1.gif).

And put all images to a folder.

[tn04462]

i was going to link each image with its specific product. I'm sort of maintaining a small database. and I was able to add new records and update records but part of it is adding images with that product.

[Juvigy]

In the SQL database you have something like:

field1 = productname , field2 = imagefolderpath

You fill it up with data like:

Computers , c:imagesPC1.gif

Mauses,c:imagesmauses.jpg

And there you go - you dont need to link the image itself in the database as a picture object. What exactly are you douing with this SQL database?

If you want to get the image of a mouse you do:

Sellect field2 from database where field1 = 'Mauses'

and you will get the picture file path - "c:imagesmauses.jpg"

Then you can use Shellexecute with the query result as parameter to open it.

[tn04462]

ah sorry for not replying, it's because i didn't understand what you meant. it's to attach to access database,, but i read about it increasing the size of the database if we attach the image to the database..

i read about linking it like you said and calling it when we need it... i'll look more into it. thanks for the concept.

I think i misinformed u as well.. I'm not using sql , I'm using ADO with auto it to do this.

what i'm doing is basically adding new inventory records to an access database. We have it were there's a part for image attachments so when we scann over the database we can see what the items look like as well.

I've got the whole importing records working already but I'm a little confused how to attach the images. I use the Access.au3 that was made by geosoft.