RE: VB6-ADO-COM-ODBC-INFORMIX : Problem running query. SQL State 37000

From: rmsterling (rmsterling_at_avaya.com)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 01:27:02 -0700

The solution to this problem is in the query.....

For some reason Informix doesn't support the semi-colon character at the end
of the SQL query.

MS Access obviously strips this character before it sends the query to
Informix via the ODBC.

Regards,
Richard.

"rmsterling" wrote:

>
> Problem running SELECT SQL query....
>
> Using....
> Visual Basic 6.0 SP5
> ADO COM Object
> Using ODBC
> MDAC 2.8 Installed
> Connecting to a Informix 7.3 Database located on a Unix Server.
>
> Connection statement below connects the database with no problems.....
>
> sConnectStr = "DSN=testname;UID=testuser;PWD=testpwd"
> With gadoConnection_Test
> .CursorLocation = adUseServer
> .Provider = "MSDASQL"
> .ConnectionString = sConnectStr
> .CommandTimeout = 300
> .Open
> End With
>
> When I go to execute a query .....
>
>
> gsSQL = "SELECT testdata FROM testtable WHERE testdata='TEST' ORDER BY
> testdata2;"
> adoRS_Test.Open gsSQL, gadoConnection_Test, adOpenDynamic, adLockReadOnly
> If Not adoRS_Test.BOF Then
>
> Above query produces this error....
>
> Error : -2147217900.
> Native Error : 0.
> SQL State : 37000.
> Source : Microsoft OLE DB Provider for ODBC Drivers.
> Description : [OpenLink][ODBC][Driver]Syntax error or access violation.
>
> Tried running query through the ODBC layer in Access 2003 and the Query
> executes fine.
>
> I think the problem lies in the statement below which tells VB what ODBC
> driver to use....
>
> .Provider = "MSDASQL"
>
> ODBC Driver details are below....
>
> OpenLink Generic ODBC Driver
> 5.10.00.00
> OpenLink Software
> OLOD5032.DLL
> 18Sep2003
>
> Could you tell me what I place in the .Provider property to make my
> application work?
>
> --
> ----------------------------
> Regards,
> Richard Sterling
> Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815
> Avaya ECS Ltd, United Kingdom
> mailto:rmsterling@avaya.com

Relevant Pages

  • Re: MS Access looks for .mdb rather than Progress schema
    ... # Same query previously worked in Progress 8.x using SQL89. ... This entry allows you to keep your existing code written with the ODBC ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... > I don't get the "Database has been placed in a state by ... Access runs the query and will prompt for any ... > my ODBC entry and click the "Configure" button, ... >>using OLEDB, it uses a more exclusive mode than it really ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Word 2000/2002 - Proper Mail Merge steps for ODBC?
    ... I was able to find the MS Query button and locate my ... in both is ODBC. ... >> be certain that I am using the OLEDB method? ... >from the list of possible connection options. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: MS Access looks for .mdb rather than Progress schema
    ... This is not passthrough query sql. ... and should be translated into ODBC SQL. ... BTW, if you put all of the connect information into the connect string, ... I also tried putting the whole thing in the connection string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Simple query executes fast but renders slow...
    ... After you restart Access and run your query, JET will write an extensive log ... "SQL Queries for Mere Mortals" ... >>Then I wrote queries and reports based on the local ... So, ODBC, right? ...
    (microsoft.public.access.queries)