I have a frustratingly strange situation here where I believe the ODBC driver may not be working properly.
I'm running:
- Windows 7 64-bit
- MS Access 2013 64-bit
- 64-bit Oracle full client I believe is 11g, which is installed under the sub directory: \product\11.2.0\client_1\
- SQL*Plus: Release 11.2.0.2.0
- I have a working TNS setup for an Oracle11g database on the local network that I have user level read only access to
- I have a 64-bit windows ODBC DSN setup using SQORA32.DLL Version 11.01.00.07 that I presume came with the oracle client install
I recently got a new PC and with it made the migration from 32 to 64 bit. I thought I'd gotten all the 64 bit gremlins finally worked out until this latest one popped up on Monday.
Steps to reproduce the issue:
- Open MS Access
- Create a new TestFilterACCDB file
- External Data(ribbon-tab)>ODBC Database(ribbon-control)>Link...(radio-bttn)>OK(bttn)>Machine Data Source(tab)>MyOracleDSN(ListBox selection)>OK>MySchema.MyTable(ListSelection)>OK
- Create(ribbon-tab)>Query Design(ribbon-control)>MySchema_MyTable(ListSelection)>Add(bttn)>Close(bttn)
- From the list of fields drag only the DataDateFld to the query grid
- under criteria add #1/1/2017#
- Design(ribbon-tab)>View Datasheet(ribbon-control)
What happens:
- The data grid shows about 17 records all with the value: #Name?
- and a Microsoft Access ! dialog box shows with the text: 'ODBC--call failed.' and two buttons (OK) and (Help)less.
and right here is where we go down the rabbit hole into a place where things start to make no since.
right about now you're likely thinking he just didn't setup the TNS or DSN correctly; however continuing on past step 7 above
- click OK to get rid of the ! dialog
- go back into design view
- Design(ribbon-tab)>Property Sheet(ribbon-control)
- General(tab on the right panel)>Unique Values(OptionBox)>Yes(change value from No to Yes)
- View Datasheet
now the data shows up and no pesky ODBC--call failed dialogs...? if you're not familiar with access, by setting this option I've just added DISTINCT to the MS Access side of the SQL.
but yet there's more, doing the original steps 1-7 on a different MyTable2 works just fine too?
on the oracle side both MySchema.MyTable1.DataDateFld and MySchema.MyTable2.DataDateFld fields are of datatype DATE
in both SQLplus and MS Access passthrough I can run the following code just fine
SELECT DataDateFld FROM MySchema.MyTable1 WHERE (DataDateFld = {d '2017-01-01'} );
(sorry for the edit, the forum was wigging out on me)
I went in to window's ODBC admin and ran a trace while access throws the error and this is what it was giving:
TestFilterACCDB 1030-1e4c ENTER SQLExecDirectW HSTMT 0x0000000009905800 WCHAR * 0x00000000184457B0 [ -3] "SELECT "MySchema"."MyTable"."KeyFld1","MySchema"."MyTable"."KeyFld2","MySchema"."MyTable"."KeyFld3","MySchema"."MyTable"."KeyFld4" FROM "MySchema"."MyTable" WHERE ("DataDateFld" < {d '2017-01-01'} ) \ 0" SDWORD -3TestFilterACCDB 1030-1e4c EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 WCHAR * 0x00000000184457B0 [ -3] "SELECT "MySchema"."MyTable"."KeyFld1","MySchema"."MyTable"."KeyFld2","MySchema"."MyTable"."KeyFld3","MySchema"."MyTable"."KeyFld4" FROM "MySchema"."MyTable" WHERE ("DataDateFld" < {d '2017-01-01'} ) \ 0" SDWORD -3TestFilterACCDB 1030-1e4c ENTER SQLFetch HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x000000000031B508TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x000000000031B508 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x000000000031B508TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x000000000031B508 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x000000000031B508TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x000000000031B508 (14)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 4 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 456 SQLLEN * 0x000000000031B508TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 4 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 456 SQLLEN * 0x000000000031B508 (8)TestFilterACCDB 1030-1e4c ENTER SQLAllocStmt HDBC 0x0000000009890110 HSTMT * 0x0000000018445AA8TestFilterACCDB 1030-1e4c EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS) HDBC 0x0000000009890110 HSTMT * 0x0000000018445AA8 ( 0x0000000009905DD0)TestFilterACCDB 1030-1e4c ENTER SQLGetStmtAttrW SQLHSTMT 0x0000000009905DD0 SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT> SQLPOINTER 0x0000000000319A20 SQLINTEGER -5 SQLINTEGER * 0x0000000000000000TestFilterACCDB 1030-1e4c EXIT SQLGetStmtAttrW with return code -1 (SQL_ERROR) SQLHSTMT 0x0000000009905DD0 SQLINTEGER 0 <SQL_ATTR_QUERY_TIMEOUT> SQLPOINTER 0x0000000000319A20 SQLINTEGER -5 SQLINTEGER * 0x0000000000000000 DIAG [S1C00] [Oracle][ODBC]Driver not capable. (0) TestFilterACCDB 1030-1e4c ENTER SQLErrorW HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x0000000000319950 SDWORD * 0x0000000000319924 WCHAR * 0x0000000009F8E760 SWORD 4095 SWORD * 0x0000000000319920TestFilterACCDB 1030-1e4c EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x0000000000319950 [ 5] "S1C00" SDWORD * 0x0000000000319924 (0) WCHAR * 0x0000000009F8E760 [ 66] "[Oracle][ODBC]Driver not capable.\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0" SWORD 4095 SWORD * 0x0000000000319920 (66)TestFilterACCDB 1030-1e4c ENTER SQLErrorW HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x0000000000319950 SDWORD * 0x0000000000319924 WCHAR * 0x0000000009F8E7B4 SWORD 4053 SWORD * 0x0000000000319920TestFilterACCDB 1030-1e4c EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x0000000000319950 SDWORD * 0x0000000000319924 WCHAR * 0x0000000009F8E7B4 SWORD 4053 SWORD * 0x0000000000319920TestFilterACCDB 1030-1e4c ENTER SQLPrepareW HSTMT 0x0000000009905DD0 WCHAR * 0x0000000018446578 [ -3] "SELECT "KeyFld1","KeyFld3","KeyFld4","KeyFld2","DataDateFld" FROM "MySchema"."MyTable" WHERE "KeyFld1" = ? AND "KeyFld2" = ? AND "KeyFld3" = ? AND "KeyFld4" = ?\ 0" SDWORD -3TestFilterACCDB 1030-1e4c EXIT SQLPrepareW with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 WCHAR * 0x0000000018446578 [ -3] "SELECT "KeyFld1","KeyFld3","KeyFld4","KeyFld2","DataDateFld" FROM "MySchema"."MyTable" WHERE "KeyFld1" = ? AND "KeyFld2" = ? AND "KeyFld3" = ? AND "KeyFld4" = ?\ 0" SDWORD -3TestFilterACCDB 1030-1e4c ENTER SQLBindParameter HSTMT 0x0000000009905DD0 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 3 <SQL_DECIMAL> SQLULEN 4 SWORD 0 PTR 0x0000000000319B78 SQLLEN 0 SQLLEN * 0x0000000000319B70TestFilterACCDB 1030-1e4c EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 3 <SQL_DECIMAL> SQLULEN 4 SWORD 0 PTR 0x0000000000319B78 SQLLEN 0 SQLLEN * 0x0000000000319B70 (4)TestFilterACCDB 1030-1e4c ENTER SQLBindParameter HSTMT 0x0000000009905DD0 UWORD 2 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 1 <SQL_CHAR> SQLULEN 4 SWORD 0 PTR 0x0000000000319B84 SQLLEN 0 SQLLEN * 0x0000000000319B7CTestFilterACCDB 1030-1e4c EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 UWORD 2 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 1 <SQL_CHAR> SQLULEN 4 SWORD 0 PTR 0x0000000000319B84 SQLLEN 0 SQLLEN * 0x0000000000319B7C (4)TestFilterACCDB 1030-1e4c ENTER SQLBindParameter HSTMT 0x0000000009905DD0 UWORD 3 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 1 <SQL_CHAR> SQLULEN 14 SWORD 0 PTR 0x0000000000319B90 SQLLEN 0 SQLLEN * 0x0000000000319B88TestFilterACCDB 1030-1e4c EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 UWORD 3 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 1 <SQL_CHAR> SQLULEN 14 SWORD 0 PTR 0x0000000000319B90 SQLLEN 0 SQLLEN * 0x0000000000319B88 (14)TestFilterACCDB 1030-1e4c ENTER SQLBindParameter HSTMT 0x0000000009905DD0 UWORD 4 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 3 <SQL_DECIMAL> SQLULEN 4 SWORD 0 PTR 0x0000000000319BA8 SQLLEN 0 SQLLEN * 0x0000000000319BA0TestFilterACCDB 1030-1e4c EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 UWORD 4 SWORD 1 <SQL_PARAM_INPUT> SWORD 99 <SQL_C_DEFAULT> SWORD 3 <SQL_DECIMAL> SQLULEN 4 SWORD 0 PTR 0x0000000000319BA8 SQLLEN 0 SQLLEN * 0x0000000000319BA0 (8)TestFilterACCDB 1030-1e4c ENTER SQLExecute HSTMT 0x0000000009905DD0TestFilterACCDB 1030-1e4c EXIT SQLExecute with return code -1 (SQL_ERROR) HSTMT 0x0000000009905DD0 DIAG [22005] [Oracle][ODBC][Ora]ORA-01722: invalid number (1722) TestFilterACCDB 1030-1e4c ENTER SQLErrorW HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x00000000003198C0 SDWORD * 0x0000000000319894 WCHAR * 0x0000000009F8E760 SWORD 4095 SWORD * 0x0000000000319890TestFilterACCDB 1030-1e4c EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x00000000003198C0 [ 5] "22005" SDWORD * 0x0000000000319894 (1722) WCHAR * 0x0000000009F8E760 [ 90] "[Oracle][ODBC][Ora]ORA-01722: invalid number\ a\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0" SWORD 4095 SWORD * 0x0000000000319890 (90)TestFilterACCDB 1030-1e4c ENTER SQLErrorW HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x00000000003198C0 SDWORD * 0x0000000000319894 WCHAR * 0x0000000009F8E7D2 SWORD 4038 SWORD * 0x0000000000319890TestFilterACCDB 1030-1e4c EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND) HENV 0x000000000988EA00 HDBC 0x0000000009890110 HSTMT 0x0000000009905DD0 WCHAR * 0x00000000003198C0 SDWORD * 0x0000000000319894 WCHAR * 0x0000000009F8E7D2 SWORD 4038 SWORD * 0x0000000000319890TestFilterACCDB 1030-1e4c ENTER SQLFreeStmt HSTMT 0x0000000009905DD0 UWORD 0 <SQL_CLOSE>TestFilterACCDB 1030-1e4c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905DD0 UWORD 0 <SQL_CLOSE>TestFilterACCDB 1030-1e4c ENTER SQLFetch HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x0000000000315FD8TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x0000000000315FD8 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x0000000000315FD8TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x0000000000315FD8 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x0000000000315FD8TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x0000000000315FD8 (14)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 4 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 456 SQLLEN * 0x0000000000315FD8TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 4 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 456 SQLLEN * 0x0000000000315FD8 (8)TestFilterACCDB 1030-1e4c ENTER SQLFetch HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c EXIT SQLFetch with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x0000000000314C58TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 1 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 504 SQLLEN * 0x0000000000314C58 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x0000000000314C58TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 2 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 492 SQLLEN * 0x0000000000314C58 (4)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x0000000000314C58TestFilterACCDB 1030-1e4c EXIT SQLGetData with return code 0 (SQL_SUCCESS) HSTMT 0x0000000009905800 UWORD 3 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 480 SQLLEN * 0x0000000000314C58 (14)TestFilterACCDB 1030-1e4c ENTER SQLGetData HSTMT 0x0000000009905800 UWORD 4 SWORD 99 <SQL_C_DEFAULT> PTR <unknown type> SQLLEN 456 SQLLEN * 0x0000000000314C58