Thread: ODBCdirect and MsAccess97

ODBCdirect and MsAccess97

From

DI Hasenöhrl

Date:

29 March 2002, 08:28:45

Hi,

does anyone in the whole world wide web know, how I can transferr a query (developed by VBA) directly to postgresql without beeing checked by the Jet engine.

In the documentations I found, that I have to use *dbQunprepare*. But when I create a query, that uses functions from postgresql, I get an error like *undefined function upper*. If I send exactly the same syntax via pass-through-query to postgresql, it works fine. Please, can anyone show me, what I'm making wrong or which dll's I need or .........?????????

I can't use pass_through queries, because I have to use several paramters.

My select statement is the datasource for a report. It contains several tables with inner and left joins, therefor the query is much faster on the server than on the client

this is a simlified example only to demonstrate what I'm doing:

create an ODBCdirect connection:

    'ODBC Direct herstellen
    Set odbcWksp = CreateWorkspace("odbcWkspc", "Ina", "Mai123", dbUseODBC)
    Set odbcConn = odbcWksp.OpenConnection("odbcConn", dbDriverNoPrompt, , "ODBC;DATABASE=WAWI;UID=ina;PWD=mai123;DSN=WAWI;")

create my sql statement and send it by a query to the server:

    Dim qry As QueryDef
    Dim strSQL As String

    Set qry = odbcConn.CreateQueryDef("")
    strSQL = "SELECT artikel.a_nr,upper(artikel.a_bez1),lower(artikel.a_bez2),artikel.kl_ean,artikel.ws_nr,artikel.wg_nr, " & _
              "vartikellieferantbez.lief_nr,vartikellieferantbez.kl_kurzbez " & _
              "FROM Artikel " & _
              "LEFT JOIN vartikellieferantbez ON (artikel.a_nr = vartikellieferantbez.a_nr AND vartikellieferantbez.f_nr=1 ); "

    qry.Prepare = dbQUnprepare
    qry.SQL = strSQL

    Me.RecordSource = qry.SQL       *this is the datasource for my report

by executing it this way I get the error *undefined function upper*, but executing via pass-through-query it works correct.

Please help, I don't know where I can get more informations.

Many thanks in advance

Irina

Re: ODBCdirect and MsAccess97

From

"Hiroshi Inoue"

Date:

30 March 2002, 07:00:51

-----Original Message-----
From: DI Hasenöhrl

> Hi,

> does anyone in the whole world wide web know, how I can transferr a query
(developed by
> VBA) directly to postgresql without beeing checked by the Jet engine.

> In the documentations I found, that I have to use *dbQunprepare*. But when
I create a
> query, that uses functions from postgresql, I get an error like *undefined
function upper*.

How about UCASE instead of upper ?

regards,
Hiroshi Inoue

Re: ODBCdirect and MsAccess97

From

DI Hasenöhrl

Date:

31 March 2002, 07:58:15

-----Original Message-----
From: DI Hasenöhrl

> Hi,

> does anyone in the whole world wide web know, how I can transferr a query
(developed by
> VBA) directly to postgresql without beeing checked by the Jet engine.

> In the documentations I found, that I have to use *dbQunprepare*. But when
I create a
> query, that uses functions from postgresql, I get an error like *undefined
function upper*.

How about UCASE instead of upper ?

I used UPPER only to check, if the query is executed by postgresql or Access. Because of the error *undefined function upper* I saw, that the query is executed by Access.

My main problem is, what's to do, in order to achive, that the query is executed by postgresql and Access makes only the transferr without checking anything. I think, postgresql is much quicker in doing this query than Access.

Any ideas????
Thanks for your response
Irina