IBM Support

Access ODBC error "Table 'xxxxx' has no columns and will not be shown"

Troubleshooting


Problem

I'm using ODBC to import an Access database into IBM SPSS Statistics. At the 2nd step of the Database Wizard, when I grab the table I want to bring over from the 'Available Tables' box and start to move it to the 'Retrieve fields in this order' box, up pops an Error Message dialog box that says: "Table 'xxxxx' has no columns and will not be shown." I know that's not true. What's wrong?

Resolving The Problem

The following issues are among those known to cause this error message:

1) The database file itself is buried in a deep directory structure (e.g., seven subdirectories away from the root). How deep is too deep seems to vary from installation to installation. Try moving the file closer to the root, and then re-configure your Data Source Name to follow the new path and re-import the file.

2) There's at least one period in the name of the database file. Filename.mdb is fine - file.name.mdb is not. This is actually a problem with the Microsoft driver itself and not SPSS. For instance, if you were to try importing a table from this file into Excel using ODBC, you would encounter the same error. Rename the file to remove the extra period (underscores are fine). Re-configure your Data Source Name to fit the new file name and try again.

3) The permissions are set in the database itself such that you don't have read access to the table from outside the application. Open the file in Access, and select Tools->Security->User and Group Permissions. Select the table you are trying to import, and then make sure that at least both 'Read Design' and 'Read Data' are checked for all varieties of users and groups. Click OK. Close the database and try importing again.

4) You are trying to import an Access query and not a table. Simple Select queries may import with no problems, but more complex ones may not. Again, it doesn't seem to be an exact science, but try converting your Select query to a Make-Table query in Access. Run the Make-Table query, close the database, and then try to import the table you just created.

5) You are trying to import an Access query that computes a new field using the Nz() function, or any other Access-only or user-defined function. User-defined functions are only usable inside Access, as are some other standard Access functions, such as Nz(). Because these functions are not recognized by any other application (even Excel), the presence of these functions in a query will cause an ODBC import of that query to fail. The Access ODBC driver itself can't work with them. As in 4) above, the workaround is to convert the query into a Make-Table query if it isn't already, run the query, and then import the created table.

6) A combination of 3) and 4) above: you are trying to import a Select query that is built on a linked table in Access. This table is linked to a table in an external database, and the permissions on that external database are not set such that you can read the table. Try the fix for either 3) or 4): get the permissions set correctly, or change the query to a Make-Table, make the table, and import the table.

If none of the above approaches help in resolving the problem, please contact IBM SPSS Technical Support.

[{"Product":{"code":"SSLVMB","label":"IBM SPSS Statistics"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Not Applicable","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

31919

Document Information

Modified date:
16 April 2020

UID

swg21477330