Hi,
I use MsAccess97 as frontend and Postgresql as backend and till now I thought, that there is no difference between a pass-through-query and a query,that I send via ODBCdirect connection to postgresql. But now I have the followig miracle.
If I make a pass-through-query with LEFT JOINs, I get the correct rows, but by developing the syntax via VBA and transferring this code by an ODBCdirect connection, I get only the rows of a normal INNER JOIN.
This is the code for my 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;")
and this is the code for my query,I want to send to the server:
Dim qry As QueryDef
Dim strSQL As String
Set qry = odbcConn.CreateQueryDef("")
strSQL = "SELECT artikel.a_nr,artikel.a_bez1,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 ) " & _
"WHERE (((artikel.a_nr)=-1) AND ((artikel.kl_ean)='HARD'));"
qry.SQL = strSQL
Me.RecordSource = qry.SQL
Recordsource is the datasource for a report.
I can't use pass-through-queries, because the select statement depends on variables.
Please, can someone show me the way to transferr this query directly to the server.
Many thanks in advance
Irina