I need to add some files uploaded from an HTML form to an MS Access DDBB. Using PHP, SQL and ODBC I haven't had any problem queryng to the Access file, except for attachment fields.
INSERT INTO TEMAS (DATOSADJUNTOS.FILENAME, DATOSADJUNTOS.FILEDATA) VALUES ("ExampleName.txt", "Wathever") WHERE ID = 4;
This query returns the following error:
SQLSTATE[07009]: Invalid descriptor index: -1003 [Microsoft][Controlador ODBC Microsoft Access] Argumento no válido. (SQLExecute[-1003] at ext\pdo_odbc\odbc_stmt.c:260)
No matter what I put instead of "Wathever", the error is always the same. Except if it is an empty string, then the query runs with no problem, and actually works, as you can see in the next image.
Any idea of what should I put in the query to make it work, or any other way of inserting an attachment into a MS Access DDBB?
Thanks
Erik is right: You can't insert an Attachment using ODBC. You need to use Access DAO, like so:
$dbe = new COM("DAO.DBEngine.120") or die("Cannot create DBEngine object.");
$db = $dbe->OpenDatabase("C:\\Users\\Public\\Database1.accdb");
$rsMain = $db->OpenRecordset("SELECT DATOSADJUNTOS FROM TEMAS WHERE ID=4", 2); // dbOpenDynaset
$rsMain->Edit;
$rsAttach = $rsMain->Fields("DATOSADJUNTOS")->Value;
$rsAttach->AddNew;
$fldAttach = $rsAttach->Fields("FileData");
$fldAttach->LoadFromFile("C:\\Users\\Gord\\Desktop\\sample.pdf");
$rsAttach->Update;
$rsAttach->Close;
$rsMain->Update;
$rsMain->Close;
$db->Close;
Note that Access DAO is a component of the Access Database Engine, so this approach does not require a full install of the Microsoft Access application.
As far as I know, this is not possible when only using PHP and ODBC. You will need something that can interface directly with Access through COM or the command-line.
You could create a macro to either import all files from a certain folder in Access, or to listen to the command-line and import a single file. Then you could trigger that using shell_exec in PHP. That means you will need to have Access on the server running the commands.
It isn't easy. I can give you pointers on steps if you really want to do this.
See this google discussion where a Microsoft employee stated ACE ODBC has no support for attachment fields.
(If someone does know a way, I'd be happy to be corrected)