SQL: Alter existing bool after printing

Applications Programming
1

I'm writing a DB to manage books & dvd's for the library.

So after they added more books/dvd's, they press the print button and all newly added entries are printed.

That is, as it prints all 'printed = false' entries, which (false) is the default value for that field for each new entry.

Anyway, its been several years since my last (my)sql queries, i'm very rusty, further as its access, it might be diffrent as well..

Main issue is, it error's on "UPDATE" and "ALTER" has a syntax error.
Specific part (its vba):
Note that "Gedruckt" is the fieldname and "tblBuchDruck" is the table's name

Dim blnVariable As Boolean
    blnVariable = True
    'SQL_NEW = "ALTER TABLE tblDataBuch ALTER COLUMN Gedruckt SET default (" & CInt(blnVariable) & ");"
    SQL_NEW = "ALTER tblDataBuch ALTER Gedruckt SET (" & CInt(blnVariable) & ") WHERE Gedruckt = 0;"
    'SQL_NEW= "UPDATE tblDataBuch, SET Gedruckt=" & CInt(blnVariable) & ") WHERE Gedruckt = 0;"
    CurrentDb.Execute SQL_NEW

Thank you in advance

2

The SQL string should be probably like:

SQL_NEW = "UDATE tblDataBuch SET Gedruckt = '" & CInt(blnVariable) & "' WHERE Gedruckt = 0;"

or

SQL_NEW = "UDATE tblDataBuch SET Gedruckt = " & CInt(blnVariable) & " WHERE Gedruckt = 0;"
1 Like
3

Misleaded from the first finding, it seems i had made too many unrequired changed while generating the command...

This one (from Oracle) helped me:

'SQL_NEW = "UPDATE customer SET name='Joe' WHERE customer_id=10;"        ' copied example
SQL_NEW = "UPDATE tblDataBuch SET Gedruckt=true WHERE Gedruckt=false;"  ' my changes

/solved

EDIT:
Thank you Franklin52, but that line results in a runtime error 3078 of VBA, allthough there is none (grammer-error) I see..

4

Try to run the sql string like this:

DoCmd.RunSQL SQL_NEW
1 Like