versions of ACCESS

[jcsuarez]

I have an application made in ACCESS 2003 that runs nicely in any computer with ACCESS 2003. When I want to run my application in ACCESS 2007 it doesn´t work (an important query doesn´t work)
The error message that I receive every time I try to run the query doesn´t explain much (it can be seen in the attached image).
Please, I hope somebody can help me solve this problem. Thanks a lot

 

[bob fitz]

For the benefit of those who only understand English, perhaps you could translate the error message.

 

[jcsuarez]

Sure, Bob. Sorry I didn´t realize the language factor.
The error message says: Reserved error (-1524) No message for this error.
When I press the Help button it says: Reserved error <Elements> There´s no message for this error (3000)
Unexpected error. The specified code identifies the conditions under which this error can be produced. Get in touch with Technical Assistant Services of Microsoft to obtain more info

 

[namliam]

Google translate: reserved error element not found message for this error

have you tried doing a compact and repair?
If so try to create a new empty database and importing the tables and offending query and see if it still generates the same error.

 

[jcsuarez]

Thanks a lot Namliam. I´ll try it and get back to you.

 

[jcsuarez]

I already tried to compact and repair my file unsuccessfully. I couldn´t find how to repair my file in ACCESS 2007, y just checked the option for compacting the file each time I close it, and then close it and open it a couple of times with no good results on the query that is not working.
I did compact and repaired it in ACCESS 2003, and then I copied it again to my PC with ACCESS 2007, with no good results either

 

[David R]

Can you post the SQL of the query that doesn't work?

 

[David R]

Where is Compact & Repair in 2007/2010/2013

 

[jcsuarez]

Here goes the SQL version of the query. I´m sorry it´s in Spanish, but all the spanish words are just names of Tables or former Queries. I´ve also tried to make a fresh A 2007 file, but it does throw the same error when importing this query:
SELECT [RESULTADOS CALCULADOS].CódECR, [RESULTADOS CALCULADOS].Híbrido, Avg([RESULTADOS CALCULADOS].DAF) AS DAF, Avg([RESULTADOS CALCULADOS].FCos) AS FCos, Avg([RESULTADOS CALCULADOS].Altura) AS Altura, Avg([RESULTADOS CALCULADOS].VERT) AS VERT, Avg([RESULTADOS CALCULADOS].[% SCL]) AS SCL, Avg([RESULTADOS CALCULADOS].[% Inc SCL]) AS [Inc SCL], Avg([RESULTADOS CALCULADOS].[Roya Blanca]) AS [Roya Blanca], Avg([RESULTADOS CALCULADOS].DAM) AS DAM, Avg([RESULTADOS CALCULADOS].Plts) AS Plts, Avg([RESULTADOS CALCULADOS].[Plts/Ha]) AS [Plts/Ha], Avg([RESULTADOS CALCULADOS].[% Queb]) AS Queb, Avg([RESULTADOS CALCULADOS].[% Vuelco]) AS Vuelco, Avg([RESULTADOS CALCULADOS].[% Oleico]) AS [% Oleico], Avg([RESULTADOS CALCULADOS].Humedad) AS Humedad, Avg([RESULTADOS CALCULADOS].RindeAc) AS RindeAc, Avg([RESULTADOS CALCULADOS].Rinde) AS Rinde, Avg([RESULTADOS CALCULADOS].[% Ac]) AS [% Ac], [MADRE PARA INFORME RESULTADOS].MADRE, [PADRE PARA INFORME RESULTADOS].PADRE, IIf(([Observ]=";;"),(Null),([Observ])) AS Observaciones, [RESULTADOS CALCULADOS].TRAT
FROM [MADRE PARA INFORME RESULTADOS] INNER JOIN ([PADRE PARA INFORME RESULTADOS] INNER JOIN ([RESULTADOS CALCULADOS] LEFT JOIN [Juntar Observaciones] ON ([RESULTADOS CALCULADOS].CódECR = [Juntar Observaciones].CódECR) AND ([RESULTADOS CALCULADOS].Híbrido = [Juntar Observaciones].Híbrido) AND ([RESULTADOS CALCULADOS].TRAT = [Juntar Observaciones].TRAT)) ON [PADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido) ON [MADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido
GROUP BY [RESULTADOS CALCULADOS].CódECR, [RESULTADOS CALCULADOS].Híbrido, [MADRE PARA INFORME RESULTADOS].MADRE, [PADRE PARA INFORME RESULTADOS].PADRE, IIf(([Observ]=";;"),(Null),([Observ])), [RESULTADOS CALCULADOS].TRAT
ORDER BY [RESULTADOS CALCULADOS].CódECR, Avg([RESULTADOS CALCULADOS].RindeAc) DESC , Avg([RESULTADOS CALCULADOS].Rinde) DESC , [RESULTADOS CALCULADOS].TRAT
WITH OWNERACCESS OPTION;

The problem here is when I join the query "PADRE PARA INFORME RESULTADOS",which is a query that runs well by itself, but corrupts the bigger query when it joins it

 

[namliam]

Holy SQL Splash, when pasting code please use the code wraps [cade]Code here[/cade] (replacing the a by an o, obviously)

SELECT [RESULTADOS CALCULADOS].CódECR
     , [RESULTADOS CALCULADOS].Híbrido
     , Avg([RESULTADOS CALCULADOS].DAF) AS DAF
     , Avg([RESULTADOS CALCULADOS].FCos) AS FCos
     , Avg([RESULTADOS CALCULADOS].Altura) AS Altura
     , Avg([RESULTADOS CALCULADOS].VERT) AS VERT
     , Avg([RESULTADOS CALCULADOS].[% SCL]) AS SCL
     , Avg([RESULTADOS CALCULADOS].[% Inc SCL]) AS [Inc SCL]
     , Avg([RESULTADOS CALCULADOS].[Roya Blanca]) AS [Roya Blanca]
     , Avg([RESULTADOS CALCULADOS].DAM) AS DAM
     , Avg([RESULTADOS CALCULADOS].Plts) AS Plts
     , Avg([RESULTADOS CALCULADOS].[Plts/Ha]) AS [Plts/Ha]
     , Avg([RESULTADOS CALCULADOS].[% Queb]) AS Queb
     , Avg([RESULTADOS CALCULADOS].[% Vuelco]) AS Vuelco
     , Avg([RESULTADOS CALCULADOS].[% Oleico]) AS [% Oleico]
     , Avg([RESULTADOS CALCULADOS].Humedad) AS Humedad
     , Avg([RESULTADOS CALCULADOS].RindeAc) AS RindeAc
     , Avg([RESULTADOS CALCULADOS].Rinde) AS Rinde
     , Avg([RESULTADOS CALCULADOS].[% Ac]) AS [% Ac]
     , [MADRE PARA INFORME RESULTADOS].MADRE
     , [PADRE PARA INFORME RESULTADOS].PADRE
     , IIf(([Observ]=";;"),(Null),([Observ])) AS Observaciones
     , [RESULTADOS CALCULADOS].TRAT
FROM        [MADRE PARA INFORME RESULTADOS] 
INNER JOIN ([PADRE PARA INFORME RESULTADOS] 
INNER JOIN ([RESULTADOS CALCULADOS] 
LEFT JOIN   [Juntar Observaciones] ON ([RESULTADOS CALCULADOS].CódECR  = [Juntar Observaciones].CódECR) 
                                  AND ([RESULTADOS CALCULADOS].Híbrido = [Juntar Observaciones].Híbrido) 
                                  AND ([RESULTADOS CALCULADOS].TRAT    = [Juntar Observaciones].TRAT)     ) 
                                   ON  [PADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido) 
                                   ON  [MADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido
GROUP BY [RESULTADOS CALCULADOS].CódECR
       , [RESULTADOS CALCULADOS].Híbrido
       , [MADRE PARA INFORME RESULTADOS].MADRE
       , [PADRE PARA INFORME RESULTADOS].PADRE
       , IIf(([Observ]=";;"),(Null),([Observ]))
       , [RESULTADOS CALCULADOS].TRAT
ORDER BY [RESULTADOS CALCULADOS].CódECR
       , Avg([RESULTADOS CALCULADOS].RindeAc) DESC 
       , Avg([RESULTADOS CALCULADOS].Rinde) DESC 
       , [RESULTADOS CALCULADOS].TRAT
WITH OWNERACCESS OPTION;

Left join's are killing to performance

I wont go into the whole "its a bad idea in general to have spaces ... " having an undefined error is hard to catch, I would suggest trying to re-hash the query to this:

SELECT [RESULTADOS CALCULADOS].CódECR
     , [RESULTADOS CALCULADOS].Híbrido
     , Avg([RESULTADOS CALCULADOS].DAF) AS DAF
     , Avg([RESULTADOS CALCULADOS].FCos) AS FCos
     , Avg([RESULTADOS CALCULADOS].Altura) AS Altura
     , Avg([RESULTADOS CALCULADOS].VERT) AS VERT
     , Avg([RESULTADOS CALCULADOS].[% SCL]) AS SCL
     , Avg([RESULTADOS CALCULADOS].[% Inc SCL]) AS [Inc SCL]
     , Avg([RESULTADOS CALCULADOS].[Roya Blanca]) AS [Roya Blanca]
     , Avg([RESULTADOS CALCULADOS].DAM) AS DAM
     , Avg([RESULTADOS CALCULADOS].Plts) AS Plts
     , Avg([RESULTADOS CALCULADOS].[Plts/Ha]) AS [Plts/Ha]
     , Avg([RESULTADOS CALCULADOS].[% Queb]) AS Queb
     , Avg([RESULTADOS CALCULADOS].[% Vuelco]) AS Vuelco
     , Avg([RESULTADOS CALCULADOS].[% Oleico]) AS [% Oleico]
     , Avg([RESULTADOS CALCULADOS].Humedad) AS Humedad
     , Avg([RESULTADOS CALCULADOS].RindeAc) AS RindeAc
     , Avg([RESULTADOS CALCULADOS].Rinde) AS Rinde
     , Avg([RESULTADOS CALCULADOS].[% Ac]) AS [% Ac]
     , [MADRE PARA INFORME RESULTADOS].MADRE
     , [PADRE PARA INFORME RESULTADOS].PADRE
     , IIf(([Observ]=";;"),(Null),([Observ])) AS Observaciones
     , [RESULTADOS CALCULADOS].TRAT
FROM        
INNER JOIN  [RESULTADOS CALCULADOS] 
INNER JOIN  [MADRE PARA INFORME RESULTADOS] ON  [MADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido
INNER JOIN  [PADRE PARA INFORME RESULTADOS] ON  [PADRE PARA INFORME RESULTADOS].Nombre = [RESULTADOS CALCULADOS].Híbrido
LEFT JOIN   [Juntar Observaciones]          ON ([RESULTADOS CALCULADOS].CódECR  = [Juntar Observaciones].CódECR) 
                                           AND ([RESULTADOS CALCULADOS].Híbrido = [Juntar Observaciones].Híbrido) 
                                           AND ([RESULTADOS CALCULADOS].TRAT    = [Juntar Observaciones].TRAT)     
GROUP BY [RESULTADOS CALCULADOS].CódECR
       , [RESULTADOS CALCULADOS].Híbrido
       , [MADRE PARA INFORME RESULTADOS].MADRE
       , [PADRE PARA INFORME RESULTADOS].PADRE
       , IIf(([Observ]=";;"),(Null),([Observ]))
       , [RESULTADOS CALCULADOS].TRAT
ORDER BY [RESULTADOS CALCULADOS].CódECR
       , Avg([RESULTADOS CALCULADOS].RindeAc) DESC 
       , Avg([RESULTADOS CALCULADOS].Rinde) DESC 
       , [RESULTADOS CALCULADOS].TRAT
WITH OWNERACCESS OPTION;

To atleast try and have some logical order to your tables and joins, perhaps that will help access.
Failing that I will suggest you try removing field by field of the avg calculations, perhaps one of those fields has some faulty data someplace

 

[jcsuarez]

So far I have no solution for the problem. So I try to make a Table of the Query that was bothering me. And I had the same problem, with the same error.

So I built 2 little ACCESS files with only 2 Tables and one Query: The files are "NO PROBLEM A 2000.mdb" and "PROBLEM A 2007.accdb".
The Tables are necessary to provide the data for the Query. The query is intended for building a new Table. When you look the Query in Design View, it´s fine. When you look for the data, it shows I think 5103 registers, I think. But when you try to execute the query and actually build the Table, you get different results: It runs nicely and builds the Table in ACCESS 2003. In ACCESS 2007, both versions give the same result: You can see the data in the query, but when you try to execute the query and build the new Table, it shows the same error.

I think these 2 files will help you find the problem. So I´m attaching them to this post. Thank you, folks, for the help you have given me so far. I know that you´ll help me work this out.

 

[namliam]

Both your databases have linked tables in them, which we obviously cant use.

 

[CJ_London]

if you search the web for

'access 2007 reserved error 1524 there is no message for this error'

You will find a number of links to this - including ones on this forum.

In each case, the problem has been caused by a data corruption

look at the last entry to this thread

http://answers.microsoft.com/en-us/...messages/9be2581f-7115-4049-a476-ecbedbaaf9d8

and this one

http://www.access-programmers.co.uk/forums/showthread.php?t=243537

and the last but one entry here

http://bytes.com/topic/access/answers/755132-reserved-error-1524-there-no-message-error

So suggest you review your data

 

[jcsuarez]

Sorry about my linked tables, Namliam. I realized that 4 or 5 hours ago. So I tried to copy them into another Table and make them local, but when I tried to do so, I received the same error message. It was evident then, that JC_London post was right, there were some corrupted data in the Table Hybrids. So I tried to see which records were originating the problem, which was difficult because I couldn´t copy the Table. So I had to go to and from the different versions of ACCESS to work it out. To make a long story short, I found that 2 characters included in the Field "NPadre" in the Table "Híbridos" made all the trouble. Characters were "@" and "/". I changed them both, and everything works just fine. I thank all of you for the tips you share with me. They made me realize were the error was, and how to solve it. Thanks a lot

 

[CJ_London]

Glad you got it sorted