Query table with compound primary keys

I’m using pyodbc to connect to a machine database, and query a number of tables in that database using

pandas.read_sql(tbl,cnxn), where tbl = "SELECT * FROM TABLE", cnxn is pyodbc.connect('DSN=DATASOURCE;UID=USERID;PWD=PASSWORD').

It works on most tables, but some tables return:

DatabaseError: Execution failed on sql ‘SELECT * FROM TABLE’: (’42S02′, ‘[42S02] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00942: table or view does not exist (942) (SQLExecDirectW)’)

These tables that return an error, when opened in MS Access, have multiple columns with a key icon on the left when opened in design view (thus a compound primary key, made up from multiple columns).

Is this is the reason I’m having the error described above? How can I solve this?

Edit: as shown in this screenshot, there are multiple columns marked as making up the primary key in design view:

Edit2:Thanks for the feedbacks. After checking ODBC Data Source Administrator window, this data source is on 32-bit platform, and its driver is Microsoft ODBC for Oracle.

I don’t think table’s name is the issue, because other tables worked and they have the same naming convention (table name is in this format NAME_OF_THE_TABLE). Trying to avoid showing the table name because working on a company project.

I did research the concept of primary key and realized that there can only be one for a table, but as shown in the screenshot attached, there are a five fields shows a key icon on the left.

Answer

Before anything, understand MS Access is a unique, GUI tool that maintains its own default database, JET/ACE Engine, but can connect to other databases as well including Oracle, SQL Server, Postgres, etc. via OLEDB/ODBC connections. Essentially, both MS Access and Python are doing the same thing: make an ODBC connection to Oracle (the actual backend database).

Because all linked tables connect fine in MS Access, try matching connections and queries in Python. Likely, the issue involves table names, schema connection, or user access.

1. Table Name: Your table contains misspellings or reserved words, or a mix of upper or lower cases as defined in their CREATE TABLE setup causing case sensitivity, so Table as defined with CREATE TABLE "Table" is not the same as TABLE. For this reason, use the exact name in the MS Access linked table and wrap with double quotes.

   pandas.read_sql('SELECT * FROM "Table"', cnxn)
   

   (Do note: double quotes in SQL is entirely different meaning than double quotes in Python and are not interchangeable with single quotes.)

2. Connected Schema/User: Incorrect schema. Because schemas in Oracle are more or less users, you may have multiple connections for your MS Access linked tables. Though they all point to same database server with same ODBC driver, the user differs each with different underlying tables. To resolve, match the Python ODBC connection with the MS Access ODBC connection:

   You can locate the MS Access connection string under: Table Design (from Navigation Pane) > Property Sheet (from Ribbon) > Description. Use this in the pyodbc.connect(...) call. Likely only the uid and pwd would differ if working across schemas.

3. Unprivileged User: The connected user does not have select privilege on that table.