Help required with SQLite syntax (attached db)

Code:

---

    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.

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

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

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"

Remove all main. Including the dot

Thank you, but that doesn't work either.

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

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.