Re: Bekend: Volgorde niet juist bij queries in Access via ODBC?
Hugo Kornelis
>Hallo,
>
>Normaal stel ik mijn vragen bij Office/Access, maar dit lijkt mij meer een
>SQL server vraag. Ons personen pakket draait op een SQL 2000 Server database
>en de makers van dat pakket hebben ons verwezen naar Access voor het maken
>van queries.
>De laatste tijd konstateer ik echter dat soms (vooral de eerste keer) de
>ORDER BY niet (of niet goed) is uitgevoerd bij een Select statement. Hierdoor
>hebben personen niet op een lijst gestaan, die daar wel op hadden moeten
>staan en hangt ons een boete van €100.000,- boven het hoofd.
>Is dit probleem bekend of is dit de eerste keer?
Beste Jos,
Sorry dat je vraag zo lang onbeantwoord is gebleven.
Dit is geen bekend probleem. Kun je wellicht iets meer informatie geven?
(Liefst een compleet repro script, dus een CREATE TABLE opdracht, een
aantal INSERT opdrachten en dan de SELECT opdracht die de resultaten in
de ongewenste volgorde laat zien).
--
Hugo Kornelis, SQL Server MVP
Jos
Ik heb een van mijn queries nader onderzocht en volgens mij gevonden,
wanneer het fout gaat.
Function VolgOrdeFout()
Dim Rcd As Long
Dim Con As DAO.Recordset
Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,
prh_einde, prs_id, prs_iw3nummer" & _
", prs_achternaam, prs_voorletters, vrv_omschrijving,
prs_roepnaam" & _
" From H_Persoon, Persoon" & _
" Where prh_Consulent_prs_id = prs_id" & _
" Order By prh_prs_id, prh_begin desc;")
PrintRecord Con, "Na Open"
Stop
End Function
Function VolgOrdeGoed()
Dim Rcd As Long
Dim Con As DAO.Recordset
Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,
prh_einde, prs_id, prs_iw3nummer" & _
", prs_achternaam, prs_voorletters, vrv_omschrijving,
prs_roepnaam" & _
" From H_Persoon, Persoon" & _
" Where prh_Consulent_prs_id = Persoon.prs_id" & _
" Order By prh_prs_id, prh_begin desc;")
PrintRecord Con, "Na Open"
Stop
End Function
Public Function PrintRecord(Rcs As DAO.Recordset, Txt As String) As Boolean
Dim J As Integer
For J = 0 To Rcs.Fields.Count - 1
Debug.Print Txt, Rcs.AbsolutePosition, J, Rcs.Fields(J).Name,
Rcs.Fields(J).Value
Next J
End Function
Resultaat Window Direkt na aanroep VolgordeFout():
Na Open 0 0 prh_prs_id 2469
Na Open 0 1 prh_begin 01-07-01
Na Open 0 2 prh_einde 09-09-2999
Na Open 0 3 prs_id 4908
Na Open 0 4 prs_iw3nummer 100002
Na Open 0 5 prs_achternaam Bruin
Na Open 0 6 prs_voorletters Ton
Na Open 0 7 vrv_omschrijving de
Na Open 0 8 prs_roepnaam
Resultaat Window Direkt na aanroep VolgordeGoed():
Na Open 0 0 prh_prs_id 2457
Na Open 0 1 prh_begin 01-02-01
Na Open 0 2 prh_einde 09-09-2999
Na Open 0 3 prs_id 4908
Na Open 0 4 prs_iw3nummer 100002
Na Open 0 5 prs_achternaam Bruin
Na Open 0 6 prs_voorletters Ton
Na Open 0 7 vrv_omschrijving de
Na Open 0 8 prs_roepnaam
Zoals te zien is, is na aanroep VolgordeFout() het eerste record ongelijk
(hoger) dan na aanroep VolgordeGoed() (lager).
Het enige verschil is de kwalificatie Persoon.prs_id bij VolgordeGoed(), die
ontbreekt bij VolgordeFout().
Je zou natuurlijk kunnen zeggen: altijd kwalificeren, probleem opgelost.
Maar het punt is dat ik had het niet verwacht en je zou tenminste een
foutmelding moeten krijgen.
Als meer informatie nodig is, kan en wil ik dat graag horen.
--
Jos
Hugo Kornelis
>Hugo,
>
>Ik heb een van mijn queries nader onderzocht en volgens mij gevonden,
>wanneer het fout gaat.
(knip)
Hoi Jos,
Goed, dat geeft een mooi startpunt. Als ik de VB (?) code weglaat en
alleen de queries bekijk, dan heb ik deze query die een verkeerde
volgorde geeft:
SELECT prh_prs_id, prh_begin, prh_einde, prs_id, prs_iw3nummer,
prs_achternaam, prs_voorletters, vrv_omschrijving, prs_roepnaam
FROM H_Persoon, Persoon
WHERE prh_Consulent_prs_id = prs_id
ORDER BY prh_prs_id, prh_begin DESC;
En deze query die een goede volgorde geeft:
SELECT prh_prs_id, prh_begin, prh_einde, prs_id, prs_iw3nummer,
prs_achternaam, prs_voorletters, vrv_omschrijving, prs_roepnaam
FROM H_Persoon, Persoon
WHERE prh_Consulent_prs_id = Persoon.prs_id
ORDER BY prh_prs_id, prh_begin DESC;
Het enige verschil is de kwalificatie van Persooon.prs_id. Nu is sowieso
mijn advies om in elke query die twee of meer tabellen gebruikt altijd
alle kolommen te kwalificeren (bij voorkeur via een mnemonische alias
voor de tabel, om onnodig tikwerk en onleesbare code te voorkomen), maar
ik zie niet hoe dat in dit geval tot een andere sortering kan leiden.
Om te beginnen wil ik je vragen de twee bovenstaande queries via knippen
en plakken in Query Analyzer (SQL Server 2000) of SQL Server Management
Studio (SQL Server 2005) over te nemen en dan uit te voeren. Hoe zien de
resultaten er uit? Zoals verwacht? Een foutmelding? Of krijg je nu
dezelfde verkeerde volgorde te zien?
Als de queries in QA/SSMS wel werken zoals jij dat wilt, dan kan de fout
alleen maar liggen in de middleware of in jouw code. Ik weet zelf vrij
weinig van VB, dus ik kan je dan niet verder helpen. Wellicht kun je in
dat geval je licht eens opsteken op de Forums van VBcentral; je komt
daar via www.vbcentral.nl, en dan klikken op "Forums".
Herhaalt het probleem zich in QA of SSMS, dan is het dus wel een SQL
probleem en dan kan ik het vrijwel zeker oplossen - maar alleen als je
de repro code post waar ik in mijn vorige antwoord al om vroeg.
Graag hoor ik het resultaat van je!
Jos
Ik heb gedaan wat je gevraagd hebt: ik heb deze query in QA geplakt:
Database: iw3_test
SELECT prh_prs_id, prh_begin, prh_einde, prs_id,
prs_iw3nummer,
prs_achternaam, prs_voorletters,
vrv_omschrijving, prs_roepnaam
FROM VHPersoon, VDPersoon
WHERE prh_Consulent_prs_id = prs_id
ORDER BY prh_prs_id, prh_begin DESC;
Reslutaat: Juiste volgorde: (Export uit QA)
2457,2001-02-01 00:00:00.000,2999-09-09 00:00:00.000,4908,100002,Bruin,Ton,de,
2457,1899-12-31 00:00:00.000,2001-02-01 00:00:00.000,4908,100002,Bruin,Ton,de,
......
2465,1999-01-01 00:00:00.000,2999-09-09 00:00:00.000,4908,100002,Bruin,Ton,de,
2465,1993-06-03 00:00:00.000,1999-01-01 00:00:00.000,4908,100002,Bruin,Ton,de,
2465,1899-12-31 00:00:00.000,1993-06-03 00:00:00.000,4908,100002,Bruin,Ton,de,
2467,2005-09-01 00:00:00.000,2999-09-09
00:00:00.000,4929,100036,Scharbay,A.E.,,Audrey
2467,2004-06-01 00:00:00.000,2005-09-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2468,2004-06-01 00:00:00.000,2999-09-09
00:00:00.000,4968,100079,Laar,Melanie,van,
2468,2003-01-01 00:00:00.000,2004-06-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2468,2000-07-01 00:00:00.000,2003-01-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2468,1999-01-01 00:00:00.000,2000-07-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2468,1899-12-31 00:00:00.000,1999-01-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2469,2001-07-01 00:00:00.000,2999-09-09 00:00:00.000,4908,100002,Bruin,Ton,de,
2469,1999-01-01 00:00:00.000,2001-07-01 00:00:00.000,4908,100002,Bruin,Ton,de,
2469,1899-12-31 00:00:00.000,1999-01-01 00:00:00.000,4908,100002,Bruin,Ton,de,
2471,2003-01-01 00:00:00.000,2999-09-09 00:00:00.000,4908,100002,Bruin,Ton,de,
2471,2002-11-07 00:00:00.000,2003-01-01
00:00:00.000,4925,100032,Rodenhuis,Linda,,
2471,2002-07-01 00:00:00.000,2002-11-07
00:00:00.000,4925,100032,Rodenhuis,Linda,,
Als ik met de cursor over de (gekoppelde) tabel H_Persoon beweeg zie ik:
ODBC;DATABASE=iw3_test;DSN=iw3_test;TABLE=dbo.VHPersoon
en bij tabel Persoon:
ODBC;DATABASE=iw3_test;DSN=iw3_test;TABLE=dbo.VDPersoon
Daarna het de query in Access nogmaals uitgevoerd (om te kijken of ook nu de
fout weer optrad) en het resultaat is nog steeds gelijk (dus foute volgorde)
Mijn eerste voorzichtig vermoeden is dat het dan misschien (toch wee) aan de
ODBC koppeling ligt.
Enkele jaren geleden was er een ODBC koppeling aan (een oudere versie van
Iw3) de FoxPro database van het personen pakket.
Hierbij waren ook problemen, die volgens MicroSoft niet meer opgelost
(konden) worden, maar zouden problemen met SQL 2000 en Access koppeling zich
niet voordoen.
Mijn baas wil weten of en hoe wij (een deel van) de boete kunnen verhalen
Wat is hierbij uw advies?
--
Jos
Hugo Kornelis
>Hallo Hugo,
>
>Ik heb gedaan wat je gevraagd hebt: ik heb deze query in QA geplakt:
>Database: iw3_test
(knip)
>Reslutaat: Juiste volgorde: (Export uit QA)
(knip)
>Als ik met de cursor over de (gekoppelde) tabel H_Persoon beweeg zie ik:
>ODBC;DATABASE=iw3_test;DSN=iw3_test;TABLE=dbo.VHPersoon
>en bij tabel Persoon:
>ODBC;DATABASE=iw3_test;DSN=iw3_test;TABLE=dbo.VDPersoon
>
>Daarna het de query in Access nogmaals uitgevoerd (om te kijken of ook nu de
>fout weer optrad) en het resultaat is nog steeds gelijk (dus foute volgorde)
>
>Mijn eerste voorzichtig vermoeden is dat het dan misschien (toch wee) aan de
>ODBC koppeling ligt.
>Enkele jaren geleden was er een ODBC koppeling aan (een oudere versie van
>Iw3) de FoxPro database van het personen pakket.
>Hierbij waren ook problemen, die volgens MicroSoft niet meer opgelost
>(konden) worden, maar zouden problemen met SQL 2000 en Access koppeling zich
>niet voordoen.
Hoi Jos,
Het lijkt inderdaad een probleem met de koppeling, en ik zou je dan ook
zeker adviseren eens een berichtje achter te laten in een groep die meer
op ODBC (al dan niet in combinatie met Access en/of SQL Server) gericht
is. Je moet dan wel uitwijken naar Engelstalige groepen. Een paar
suggesties (ik heb geen idee hoe actief ze zijn; ik heb ze gevonden door
te zoeken op "odbc" in een lijst met groepen):
microsoft.public.access.odbcclientsvr
microsoft.public.data.odbc
microsoft.public.sqlserver.odbc
Overigens durf ik op grond van de informatie in deze thread ook niet
voor 100% zeker uit te sluiten dat er toch iets aan de hand is in jullie
code, al kan ik niet bedenken wat dat dan is. (Bij sorteringsproblemen
denk ik zelf vaak aan sortering op een naar string geconverteerde waarde
in plaats van op de waarde zelf; dit kan sorteringen op datums of op
numerieke waardes behoorlijk overhoop gooien - hebben jullie daar al
naar gekeken?)
>Mijn baas wil weten of en hoe wij (een deel van) de boete kunnen verhalen
>Wat is hierbij uw advies?
Dat moet je aan een advocaat vragen. Ik ben geen advocaat.
Jos
Ik dank je voor het trouw beantwoorden.
Ik zal de vraag op de jouw voorgestelde sites deponeren.
Ik ben ook geen advocaat, maar als ik het vergelijk met een auto: je koopt
legaal een nieuwe auto bij een autodealer en er blijkt schade voortgekomen
uit een constructie fout, dan is de maker van de auto toch ook aansprakelijk
voor de schade?
--
Jos
Hugo Kornelis
>Hoi Hugo,
>
>Ik dank je voor het trouw beantwoorden.
Hoi Jos,
Graag gedaan!
>Ik zal de vraag op de jouw voorgestelde sites deponeren.
Laat je het nog even weten als je een duidelijk antwoord krijgt? Ik ben
wel benieuwd, namelijk!
>Ik ben ook geen advocaat, maar als ik het vergelijk met een auto: je koopt
>legaal een nieuwe auto bij een autodealer en er blijkt schade voortgekomen
>uit een constructie fout, dan is de maker van de auto toch ook aansprakelijk
>voor de schade?
Helaas weet ik inmiddels dat wat het meest logisch klinkt niet altijd
overeen komt met de juridische realiteit. Dat is dan ook de reden dat ik
me niet waag aan het beantwoorden van dit soort vragen. :-)
Jos
Ik heb onderstaande gezet op de genoemde site, maar buiten het ene antwoord
van Sylvain Lafontaine nog verder geen reacties.
Zou ik verder nog wat kunnen ondernemen?
Jos
Microsoft Discussion Groups Home | Communities Home | Communities Worldwide
Out of order sequence in Select .. Order By in
microsoft.public.access.odbcclientsvr
Jos 12/8/2006 7:39 AM PST
Question
Hi,
I have initialy posted this question on the Dutch community newsgroup, but
they recommended I post it here.
Our office (a workplace for disabled people) uses an application for
Personel tracking made by Baas & Roost. This application (Iw3) runs on an Sql
2000 Server and Baas & Roost recommend we (Baanstede) use MS Access (2002)
and ODBC for reporting tools, that I program.
In the past Imwin (previous version of Iw3) runned on a FoxPro database and
MS Access (97) and ODBC. This latter connection used to give different query
result with the same input. Microsoft aknowlegd this, but no corrections
would be made.
These problems would be over with Sql 2000 Server and Access 2002.
However in the following query there is an error (not reported) in the
sequence of the result set:
Function Sequence_Error()
Dim Rcd As Long
Dim Con As DAO.Recordset
Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,... From
H_Persoon, Persoon Where prh_consulent_prs_id = prs_id Order By prh_prs_id,
prg_begin desc;")
This results (most of the time) in prh_prs_id: 2469 in the first record
returned
However in the following query:
Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,... From
H_Persoon, Persoon Where prh_consulent_prs_id = prs_id Order By
Persoon.prh_prs_id, prg_begin desc;")
The result of prh_prs_id: 2457 is the correct value.
The only difference is the qualification of prs_id by Persoon.prs_id.
Because of this malfunction our company has to pay a fine of several hunderd
thousands of euros because certain persons where not on a list (because of
the sequence error) that should have been on it, so they could be announced,
the governement fines a certain amount for each person for the duration of
the lack of announcement to Baanstede.
My boss wants to know if Microsoft can be made liable and if so how we can
or should proceed in this matter.
--
Jos
Sylvain Lafontaine 12/9/2006 3:49 PM PST
A wild guess here as you don't provide any detail about the schema of these
two tables (nor about the provided used, either) but is it possible that the
table H_Persoon also has a field with the name prh_prs_id (or the name
prs_id as it is later written) ?
Second, as to the possibility of suing MS, I don't know anything about the
laws in your country but in general, it's your responsability to test and
debug everything to make sure that everything is fine before going into
production. You can read the user's licenses if you want more info about
this. Of course, the laws in your country might give you additional rights.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Jos 12/14/2006 1:48 AM PST
Sylvain,
I have tested the queries after converting Imwin to Iw3 and during testing
(comparing Imwin output to Iw3 output) I could not find any differences.
The problem is that the malfunction is not consistent, that is it does not
always occur. The conversion was in 2005 and in that year there were no
problems. Only recently this particular malfunction could be isolated. It
seems like it only occurs the first time access is started and the first
query is run. Running the query again (and again) sometimes produces the
correct result.
You asked for details of the schema of the two tables (alas in dutch):
\\schermer\s_apps\iw3\Access\Br3_test.mdb donderdag 14 december 2006
Tabel: H_Persoon Pagina: 1
Eigenschappen
Attributes: Gekoppeld ODBC Connect: ODBC;DATABASE=iw3_test;DSN
=iw3_test;
DateCreated: 16-11-06 21:14:15 DefaultView: Gegevensblad
LastUpdated: 16-11-06 21:14:18 OrderBy: H_Persoon.prh_prs_id,
H_Persoon.prh_begin
OrderByOn: Waar Orientation: Links-naar-rechts
RecordCount: -1 SourceTableName: dbo.VHPersoon
Updatable: Onwaar
Kolommen
Naam Type Omvang
prh_id Lange integer 4
prh_prs_id Lange integer 4
prh_begin Datum/tijd 8
prh_einde Datum/tijd 8
<knip>
\\schermer\s_apps\iw3\Access\Br3_test.mdb donderdag 14 december 2006
Tabel: PERSOON Pagina: 4
Eigenschappen
Attributes: Gekoppeld ODBC Connect: ODBC;DATABASE=iw3_test;DSN
=iw3_test
DateCreated: 14-12-06 09:59:47 LastUpdated: 14-12-06 09:59:47
RecordCount: -1 SourceTableName: dbo.VDPersoon
Updatable: Onwaar
Kolommen
Naam Type Omvang
prs_id Lange integer 4
prs_iw3nummer Lange integer 4
prs_gsl_id Lange integer 4
prs_voorletters Tekst 15
<knip>
--
Jos
"Sylvain Lafontaine" wrote:
Click to show or hide original message or reply text.
> A wild guess here as you don't provide any detail about the schema of these
> two tables (nor about the provided used, either) but is it possible that the
> table H_Persoon also has a field with the name prh_prs_id (or the name
> prs_id as it is later written) ?
>
> Second, as to the possibility of suing MS, I don't know anything about the
> laws in your country but in general, it's your responsability to test and
> debug everything to make sure that everything is fine before going into
> production. You can read the user's licenses if you want more info about
> this. Of course, the laws in your country might give you additional rights.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Jos" <J...@discussions.microsoft.com> wrote in message
> news:B79AE476-331F-4DF7...@microsoft.com...
> > Hi,
> >
> > I have initialy posted this question on the Dutch community newsgroup, but
> > they recommended I post it here.
> >
> > Our office (a workplace for disabled people) uses an application for
> > Personel tracking made by Baas & Roost. This application (Iw3) runs on an
> > Sql
> > 2000 Server and Baas & Roost recommend we (Baanstede) use MS Access (2002)
> > and ODBC for reporting tools, that I program.
> >
> > In the past Imwin (previous version of Iw3) runned on a FoxPro database
> > and
> > MS Access (97) and ODBC. This latter connection used to give different
> > query
> > result with the same input. Microsoft aknowlegd this, but no corrections
> > would be made.
> > These problems would be over with Sql 2000 Server and Access 2002.
> >
> > However in the following query there is an error (not reported) in the
> > sequence of the result set:
> >
> > Function Sequence_Error()
> > Dim Rcd As Long
> > Dim Con As DAO.Recordset
> >
> > Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,... From
> > H_Persoon, Persoon Where prh_consulent_prs_id = prs_id Order By
> > prh_prs_id,
> > prg_begin desc;")
> >
> > This results (most of the time) in prh_prs_id: 2469 in the first record
> > returned
> >
> > However in the following query:
> >
> > Set Con = CurrentDb.OpenRecordset("Select prh_prs_id, prh_begin,... From
> > H_Persoon, Persoon Where prh_consulent_prs_id = prs_id Order By
> > Persoon.prh_prs_id, prg_begin desc;")
> >
> > The result of prh_prs_id: 2457 is the correct value.
> >
> > The only difference is the qualification of prs_id by Persoon.prs_id.
> >
> > Because of this malfunction our company has to pay a fine of several
> > hunderd
> > thousands of euros because certain persons where not on a list (because of
> > the sequence error) that should have been on it, so they could be
> > announced,
> > the governement fines a certain amount for each person for the duration of
> > the lack of announcement to Baanstede.
> >
> > My boss wants to know if Microsoft can be made liable and if so how we can
> > or should proceed in this matter.
> >
> > --
> > Jos
>
>
--
Jos
Hugo Kornelis
>Hi Hugo,
>
>Ik heb onderstaande gezet op de genoemde site, maar buiten het ene antwoord
>van Sylvain Lafontaine nog verder geen reacties.
>
>Zou ik verder nog wat kunnen ondernemen?
Hoi Jos,
Dit soort "af en toe wel, af en toe niet" problemen zijn altijd erg
lastig op te lossen, vooral via een nieuwsgroep.
Als je het toch nog eens wilt proberen, dan zou je een poging kunnen
wagen in microsoft.piblic.programming of mcroosoft.public.server,
simpelweg omdat die door meer experts bezocht worden dan alle andere
groepen. (Meldt wel weer even dat je de vraag ook al elders gesteld hebt
maar geen bevredigend antwoord gekregen hebt).
Het alternatief is een support incident te openen bij Microsoft. Daar
zijn wel kosten aan verbonden, tenzij blijkt dat de oorzaak een bug in
een Microsoft produkt is die je niet zelf via bijvoorbeeld de knowledge
base had kunnen vinden. (De exacte voorwaarden voor kwijtschelding van
de rekening voor een support incident ken ik niet uit mijn hoofd, dus
pin me er niet op vast).
Sorry dat ik je hier niet verder mee heb kunnen helpen.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jos
Dat zal ik doen; bedankt voor het advies.
--
Jos