Query issues - ODBC Microsoft Access Driver error
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.
Code:
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().
Code:
nRetCode = SQLExecDirect(m_hSTM, (SQLTCHAR *) szQuery, (SQLINTEGER) wcslen(szQuery));
And now, surprise: nRetCode = -1.
I'm using HandleError() in order to find the error:
Code:
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?
Re: Query issues - ODBC Microsoft Access Driver error
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.
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
Maximus_X
When I run a trivial query ("SELECT * FROM myTable") I use the SQLExecDirect().
Code:
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
Code:
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;
}
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
itsmeandnobodyelse
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.)
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
Maximus_X
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?
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
VictorN
Is it your actual query?
Sorry, I don't understand what you mean.
Quote:
Originally Posted by
VictorN
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.
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
Maximus_X
Quote:
Originally Posted by
VictorN
Is it your actual query?
Sorry, I don't understand what you mean.
Did you get this error while using exactly this query
Quote:
Code:
SELECT * FROM myTable
or, perhaps, there were some "special" words that may be not allowed by Access 2007 or its drivers?
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
Maximus_X
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
Quote:
[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.
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
VictorN
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.
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
itsmeandnobodyelse
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.
Quote:
Originally Posted by
itsmeandnobodyelse
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.
Re: Query issues - ODBC Microsoft Access Driver error
Perhaps, you don't have enough permissions while connecting to Access via ODBC?
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
VictorN
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". :)
Re: Query issues - ODBC Microsoft Access Driver error
Quote:
Originally Posted by
Maximus_X
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?
Quote:
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.
