Help required with SQLite syntax (attached db)

[tmighty2]

    Dim cOld As cConnection
    Set cOld = New cConnection
    m_CN.AttachDataBase "D:\backup.db", "old"
    
    m_CN.Execute "UPDATE main.stuff SET " & _
    "main.resultsfrombatchaligner=old.resultsfrombatchaligner," & _
    "main.acatranscriptionforbatchaligner=old.acatranscriptionforbatchaligner," & _
    "main.f0text=old.f0text," & _
    "main.pmtext=old.pmtext " & _
    "WHERE main.rowid=old.rowid"
    
    Debug.Assert m_CN.AffectedRows > 0
    m_CN.DetachDataBase "old"

I am getting the error "Cannot compile SQL-Statement: near ".": syntax error".

What I want to do it restore the value of the columns resultsfrombatchaligner, acatranscriptionforbatchaligner, f0text and pmtext as I had accidentally set them to ''.
No other harm was done to the db, so I wanted to just restore these values using an older backup db.

Thank you for any help as I don't see my mistake.

Thank you.

[Schmidt]

You have to take care with the explicit naming of columns.
The prefixed namespaces "main" and "old" refer to DBs (not Tables).

A full name-referenc to a column needs to be given via DBName.TableName.ColumnName -
and not (as currently) DBName.ColumnName.

Olaf

[jmcilhinney]

Thread moved from VB6 CodeBank, which is for sharing working VB6 code snippets, to the Database Development forum.

[tmighty2]

Olaf, thank you, but the error message does not go away even with this code:

Dim cOld As cConnection
Set cOld = New cConnection
m_CN.AttachDataBase "D:\backup.db", "old"

m_CN.Execute "UPDATE main.stuff SET " & _
"main.stuff.resultsfrombatchaligner=old.stuff.resultsfrombatchaligner," & _
"main.stuff.acatranscriptionforbatchaligner=old.stuff.acatranscriptionforbatchaligner," & _
"main.stuff.f0text=old.stuff.f0text," & _
"main.stuff.pmtext=old.stuff.pmtext " & _
" WHERE main.stuff.rowid=old.stuff.rowid"

Debug.Assert m_CN.AffectedRows > 0
m_CN.DetachDataBase "old"

[Zvoni]

Remove all main. Including the dot

[tmighty2]

Thank you, but that doesn't work either.

[Zvoni]

Does the query work in a SQLite-client, e.g. DB Browser for SQLite?

[jpbro]

Is this SQL part of a TRIGGER? If so, try changing the "main." to "new.". If that doesn't work, please post a minimally reproducible code sample for us to work with.