ODBC acts different depending on order when querys are run

Question

0.00/5 (No votes)

See more:

Hi,

I have got a strange behavior within a c++ application. It connects through DAO to an Access 2010 database. The Access database has got its tables linked to a SQL Server 2008 through ODBC.

When I run a particular query, containing a BETWEEN-statement, on one table before running another query, also containing a BETWEEN-statement, on another table, the second query will fail with ODBC errors indicating that the syntax is incorrect. The ODBC driver will constantly continue to return syntax error after this.

But if I run the second query first, there are no problems running any of the queries. No error will be displayed as long as the session is running.

I tried to trace the ODBC through the ODBC Manager, but was unsuccessful since the tracing seemed to lock my application.

Any ideas on how to find out what is going wrong?

Posted 18-Nov-10 4:45am

Sulan

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2010-11-18T06:09:00

Solution 1

You know DAO has been deprecated for quite some time now, right? It also won't work on 64-bit Windows.

I highly suggest abandoning DAO and switching to OLE DB for using Access databases.

Do you have any particular reason for using Access and not going direct to the SQL Server using ? Removing Access would eliminate a level of complexity and improve performance.

Data Access Technologies Roadmap[^] circa December 2009

Posted 18-Nov-10 6:09am

Dave Kreskowiak

Comments

Sulan 19-Nov-10 2:49am

Hi, yes I know it has been deprecated for some time. Although it seems to work most of the time. If this application was written from scratch today DAO would never be used, but since it is an legacy from the times when SQL Server was very expensive and 64-bit systems was a fantasy we are stuck with it for the moment.

We have been discussing switching to ADO, but don't know if it is possible to use ADO.NET within an unmanaged C++ software. Could you give me an hint on what technology to put the effort into?

Regarding the use of Access is that we have multiple users that use the software as standalone and have been doing so for more than a decade. Completly removing the use of Access isn't an option. But being able to use either Access or SQL Server without always going through Access is favourable.

Dave Kreskowiak · Answer 2 · 2010-11-19T01:46:00

I posted the link to the Roadmap so YOU could pick a technology. Keep in mind, it's entirely likely that Windows 8 will NOT be available in 32-bit. So if your app is going to last that long, you're going to have to rewrite it with something x64 compatible.

You could use ADO.NET, but that would require that your unmanaged app be rewritten to host the .NET Framework and a component that does the data access. But, if you were going to go through that much trouble, why not just rewrite the unmanaged app into a managed one using the .NET Framework and C++/CLI...