ODBC / Access problem with Stata 16

[Martyn Sherriff]

I can describe and query a database but am unable to load a table from Access using ODBC in Stata 16. Using the Northwind database:

.  odbc describe "Employees", dsn("Northwind")

DataSource: Northwind (query)
Table:      Employees (load)
-------------------------------------------------------------------------------
Variable Name                               Variable Type
-------------------------------------------------------------------------------
ID                                          COUNTER
Company                                     VARCHAR
Last Name                                   VARCHAR
First Name                                  VARCHAR
E-mail Address                              VARCHAR
Job Title                                   VARCHAR
Business Phone                              VARCHAR
Home Phone                                  VARCHAR
Mobile Phone                                VARCHAR
Fax Number                                  VARCHAR
Address                                     LONGCHAR
City                                        VARCHAR
State/Province                              VARCHAR
ZIP/Postal Code                             VARCHAR
Country/Region                              VARCHAR
Web Page                                    LONGCHAR
Notes                                       LONGCHAR
Attachments                                 LONGCHAR
-------------------------------------------------------------------------------

. odbc query "Northwind"

DataSource: Northwind
Path      : C:\Users\marty\Documents\Northwind.accdb
-------------------------------------------------------------------------------
Customers
Employee Privileges
Employees
Inventory Transaction Types
Inventory Transactions
Invoices
Order Details
Order Details Status
Orders
Orders Status
Orders Tax Status
Privileges
Products
Purchase Order Details
Purchase Order Status
Purchase Orders
Sales Reports
Shippers
Strings
Suppliers
-------------------------------------------------------------------------------

. odbc load, table("Employees")
The ODBC driver reported the following diagnostics
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
SQLSTATE=42000
r(682);

If I set debug on there is no additional output, and as far as I can find SQLSTATE=42000 appears to be a generic error.

I would be grateful for any suggestions on how to resolve this.

Thank you,
Martyn

[Joseph Coveney]

Confirmed. Also for MS SQLServer. Even a simple -odbc exec("SELECT COUNT(*) FROM . . .")- gives this SQLSTATE error. I think that this needs to go to Technical Services.

[Brian Landy]

I'm also having trouble with odbc, but in my case under Linux using unixodbc. However, I did find a workaround that might help. Try running "set odbcdriver ansi" and see if it works. "unicode" worked fine under Stata 15 (and unicode is the default under 15 and 16).

[Martyn Sherriff]

Hello Joseph, Brian;

Thank you for the confirmation of the problem.

I will try "set odbcdriver ansi" and see what happens.

I will send the problem to Technical Services and let you know what happens.

Martyn

[Joseph Coveney]

Try running "set odbcdriver ansi" and see if it works.

Confirmed that that does allow -odbc load- to work for MS Access under Windows 10 Pro. (Of course, any Unicode content in the Access table is rendered ????? upon loading into Stata.) Nevertheless, Brain's discovery should give Technical Services something to go on.

[Hua Peng (StataCorp)]

We have reproduced the issue and have it fixed internally. Pending our full certification process, the fix will be released in the next executable update of Stata 16, which we expect to happen soon barring any unforeseen circumstances. I will notify the list and the users who wrote to tech support as soon as the update is available.

[Hua Peng (StataCorp)]

An update containing the fix is available, type

update all

in Stata command window to apply the update.