I have an Access database (.mdb) that was created and sent to me by someone else. I have tried running it in both nodejs with node-adodb and with python and pyodbc, and I get an error. Here is my query:

insert into sets (id, abbr, [name], [date], legality, path) values ( (SELECT max(id) + 1 FROM sets), 'ASD', 'The Name', '2025.01.01', '++++', 'path\\to\\file' )

I get this error:

[HY000] [Microsoft][ODBC Microsoft Access Driver] Query input must contain at least one table or query. (-3025) (SQLExecDirectW)

If I change the:

(SELECT max(id) + 1 FROM sets)

to a hard-coded number like:

1033

it works. Is Access not able to handle getting the next id number like this in an insert statement? One issue is the id column is not set to an autonumber field, and I have to manually set it.

Here is the code:

node:

const adodb = require('node-adodb');

var cn = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + process.env.ACCESS_DB_FILENAME + ';';
    
var connection = adodb.open(cn);
var q = `insert into sets (id, abbr, [name], [date], legality, path) values ( (SELECT max(id) + 1 FROM sets), 'ASD', 'The Name', '2025.01.01', '++++', 'path\\to\\file' )`
var ins = await connection.query(q).then((data) => {
    console.log(data)
    return data
}).catch(err => {
    console.error(JSON.stringify(err))
})

I get {"process":{"code":-2147467259,"message":"Unspecified error"},"exitCode":0}

python:

import pyodbc

driver = "Microsoft Access Driver (*.mdb, *.accdb)"
path = "c:\\path\\to\\file.mdb"

conn = pyodbc.connect(f"Driver={driver};DBQ={path};")

conn.setdecoding(pyodbc.SQL_CHAR, encoding='latin1')
conn.setencoding('latin1')

cursor = conn.cursor()

sql = "insert into sets (id, abbr, [name], [date], legality, path) values ( (SELECT max(id) + 1 FROM sets), 'ASDF', 'The Name', '2025.01.01', '++++', 'path\\to\\file' )"
print(sql)

res = cursor.execute(sql)
conn.commit() 

I get:

pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC Microsoft Access Driver] Query input must contain at least one table or query. (-3025) (SQLExecDirectW)')