Query issues - ODBC Microsoft Access Driver error

[Maximus_X]

I have some issues with the execution of an ordinary query over MS Access database table. On my machine I installed Access 2007 and my database Access format is 2002 to 2003 is compatible mode.

My connection string is: Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:\\myAppPath\\myDatabase.mdb.
I connect to the database successfully using SQLDriverConnect() - result returned by this function is 0.

 nRetCode = SQLDriverConnect(m_hDBC, NULL, (SQLTCHAR *) m_szFullDSN.c_str(), (SQLSMALLINT) m_szFullDSN.length(), 
               (SQLTCHAR *) szFullDSN, (SQLSMALLINT)sizeof(szFullDSN)/sizeof(szFullDSN[0]), &nLen, SQL_DRIVER_NOPROMPT);

When I run a trivial query ("SELECT * FROM myTable") I use the SQLExecDirect().

nRetCode = SQLExecDirect(m_hSTM, (SQLTCHAR *) szQuery, (SQLINTEGER) wcslen(szQuery));

And now, surprise: nRetCode = -1.
I'm using HandleError() in order to find the error:

TCHAR *szErr = HandleError(m_hSTM, SQL_HANDLE_STMT, nRetCode);
sErrorMsg = szErr ? szErr : _T("");

sErrorMsg = [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

This is considering that if I open my database in Access 2007 I have: Security Warning Certain content in the database HAS Been disabled. I tried to enable the content but is vain ...

I assume that programming is not a problem but rather the configuration.
Have you encountered this problem? If yes, what is the solution?

[Maximus_X]

I found a way to avoid this issue but I'm not satisfied as long as this one persists.
I exported database to my MS SQL Server, I set the proper connection string and my query runs fine with this code.

[itsmeandnobodyelse]

When I run a trivial query ("SELECT * FROM myTable") I use the SQLExecDirect().

nRetCode = SQLExecDirect(m_hSTM, (SQLTCHAR *) szQuery, (SQLINTEGER) wcslen(szQuery));

And now, surprise: nRetCode = -1.

I don't know the HandleError but you should know that there could be multiple error messages. So it needs to calling SQLError in a loop in order to get full information.

You may find out how to program that by evaluating the code below

Bool OdbcEnvironment::checkSqlReturn(RETCODE ret, String* pInfo, Bool bGetInfo, Bool bUpdate)
{
   if (ret == SQL_SUCCESS || (ret == SQL_SUCCESS_WITH_INFO && !bGetInfo))
      return True;

   if (pInfo == NULL)
      return False;

   SQLHSTMT& hStmt = bUpdate? m_hStmtUpd : m_hStmt;
   UChar szSqlState[10];
   UChar szErrorMsg[SQL_MAX_MESSAGE_LENGTH];
   Long  nativeError;
   Short nLenErrorMsg;

   String&  info = *pInfo;
   String   newLine;

   if (ret == SQL_SUCCESS_WITH_INFO)
      info = "SQL_SUCCESS_WITH_INFO " + info;
   else
      info = "SQL_ERROR " + info;

   for (;;)
   {
       if (SQLError(m_hEnv, m_hDbc, hStmt, szSqlState, &nativeError,
          szErrorMsg, SQL_MAX_MESSAGE_LENGTH-1, &nLenErrorMsg) != SQL_SUCCESS)
          break;


       info << newLine << " | " << String((CharPtr)szSqlState) <<
                        " : " << String(nativeError) << " | " << String((CharPtr)szErrorMsg);
       if (newLine.isEmpty())
          newLine = "\n\t";
   }
   return False;
}

[Maximus_X]

I don't know the HandleError but you should know that there could be multiple error messages. So it needs to calling SQLError in a loop in order to get full information.

Man, the error message is clear and this is not an issue.
The issue is generated by SQLExecDirect() and I can see it in the returned value. (I mean nRetCode = -1 which is a fail value.)

[VictorN]

When I run a trivial query ("SELECT * FROM myTable") I use the SQLExecDirect().

Is it your actual query?
What type of data does the myTable contain?

[Maximus_X]

Is it your actual query?

Sorry, I don't understand what you mean.

What type of data does the myTable contain?

I tested on few tables where I have types like: long integer (auto-increment), long, integer, double or text. I got same fail results for all the tables.

[VictorN]

Is it your actual query?

Sorry, I don't understand what you mean.

Did you get this error while using exactly this query

SELECT * FROM myTable

or, perhaps, there were some "special" words that may be not allowed by Access 2007 or its drivers?

[itsmeandnobodyelse]

Man, the error message is clear and this is not an issue.
The issue is generated by SQLExecDirect() and I can see it in the returned value. (I mean nRetCode = -1 which is a fail value.)

You are not friendly to someone who tried to help you. I made ODBC programming for many years and an error message like

[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

may be clear for you if you have more experience than me. However, Ã*n my opinion this message is only the last of many messages where one probably says which optional feature wasn't implemented.

BTW, I never used a select * with ODBC but always used column names which I fetched with full binding.

[Maximus_X]

Did you get this error while using exactly this queryor, perhaps, there were some "special" words that may be not allowed by Access 2007 or its drivers?

Nothing special "SELECT * FROM CLINETS".
As I said, I exported the Access data to MS SQL Sever, I changed the connection string and this query runs fine. So, I continue to conclude that is a MS Acccess issue.
I think the problem is the driver and the wonderful setting in Access 2007 which I meet every time I open a database: Security Warning Certain content in the database HAS Been disabled. For example, I must enable it to see a relationship between tables visually.

[Maximus_X]

You are not friendly to someone who tried to help you. I made ODBC programming for many years and an error message like

Please, don't take it like this. This code runs fine on MS SQL Server... So, I continue to believe that is MS Access driver issue.

BTW, I never used a select * with ODBC but always used column names which I fetched with full binding.

Trust me that I tried like this and I got same fail results, too.

[VictorN]

Perhaps, you don't have enough permissions while connecting to Access via ODBC?

[Maximus_X]

Perhaps, you don't have enough permissions while connecting to Access via ODBC?

Indeed, this could be an issue. Every time when I open my Access 2007 and I want to see my tables's relational view, I have to enable that option.
I'll try to find a way to disable this "secure feature".

[itsmeandnobodyelse]

Please, don't take it like this. This code runs fine on MS SQL Server... So, I continue to believe that is MS Access driver issue.

Trust me that I tried like this and I got same fail results, too.

It is out of the question that it is an MS Access driver issue. But do you know what the 'Optional Feature' not implemented actually is?

I think the problem is the driver and the wonderful setting in Access 2007 which I meet every time I open a database: Security Warning Certain content in the database HAS Been disabled. For example, I must enable it to see a relationship between tables visually.

Can you tell more about that?

Did you ever try to access the database from Access2003 or from Excel?

Did you add a datasource with the ODBC Applet (odbcadm.exe)?

If yes, it is a 'user' or 'system' datasource?

If yes, what ODBC driver did you choose? I ask that cause you could choose a driver that came with Access2003 or a driver that came with VS2003/VS2005 if available. If doing so, the Access 2007 settings shouldn't have any influence.

I also had bad experience with access drivers that came with MS-Office. They sometimes were restricted versions for use with word or excel.