Re: VB6-ADO-COM-ODBC-INFORMIX : Problem running query. SQL State 3

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

Date: Fri, 22 Oct 2004 00:35:05 -0700

TestData2 is a column.
As stated in the email the query works through MS Access 2003.

The query has been altered to a Test query as the query contained sensitive
company information that I cannot disclose. The query is essentially the same
but I have changed the name. If the ORDER BY is wrong don't worry about it.
The query isn't the issue here. All you need to know is that the original
query executes fine in Access but won't work through the VB6 ADO/ODBC layer.

I have spoken with my Boss and he has come to the same conclusion that I
have. The problem is the Provider String for the OpenLink driver. I need to
know what to put here.

Look forward to your reply.

Regards,
Richard.
"William (Bill) Vaughn" wrote:

> Ah, is TestData2 a column? You didn't include it in the SELECT. Is it a
> TextBox that contains the column to sort on?
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "rmsterling" <rmsterling@avaya.com> wrote in message
> news:83F560BE-5F3A-4BA4-87A5-1D6E53F26C59@microsoft.com...
> >
> > 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)