Advantage Database Error - The table is a database table. It cannot be opened as a free table. Table name

Question

I am trying to connect to Advantage Database which running on local server on windows machine. Using pyodbc python lib, I am able to establish a connection to a database. Below is the code

import pyodbc

conn = pyodbc.connect('DSN=DemoDB;UID=User;PWD=PSWD')
if conn:
    print('Connected!')
    sql = "select * from test;"
    cur = conn.cursor()
    data = cur.execute(sql)
    print(data)
    conn.close()

But the program is failing when it is trying to execute a query against a connected database with below error

pyodbc.Error: ('HY000', '[HY000] [iAnywhere Solutions][Advantage SQL][ASA] Error 7200: AQE Error: State = HY000; NativeError = 5159; [SAP][Advantage SQL Engine][ASA] Error 5159: Error encountered when trying to open a database table. The table is a database table. It cannot be opened as a free table. Table name: alb (7200) (SQLExecDirectW)')

I am not able find much information about the error on the web nor in the SAP community forum.

Database Details:

• ODBC: Advantage StreamlineSQL ODBC
• Version: Advantage Data Architect 11.10

Answer 1

The DSN should be to an .add file which is the the container of the database. Advantage DB has 2 kinds of connections, free table connection and database connection. The database connection is more secured and offers many additional features through the .add file. It appears that you should be able to download the documentation here (after creating an account): https://wiki.scn.sap.com/wiki/display/EIM/Advantage+Online+Help+Files