Access 2013 - Invalid Operation

Question

Hello all,

I'm having issues running a query in Access 2013 (Windows Server 2008 R2 Standard). When I run this query:

SELECT [Query 2 - Total charges fall 2007].ID_NUM, [Query 2 - Total charges fall 2007].[Total @C], [Query 3 - Total fin aid fall 2007].[Total @F], [Query 4 - Excluded aid - FWS].[FWS awd], [Query 5 - Excluded aid - HBV].HBV, [Query 6 - Excluded aid - LBV].LBV, [Query 3 - Total fin aid fall 2007]![Total @F]+IIf([Query 4 - Excluded aid - FWS]![FWS awd ]<0,-[Query 4 - Excluded aid - FWS]![FWS awd],0) AS [FA for tuition], [Query 2 - Total charges fall 2007]![Total @C]+IIf([FA for tuition]<>0,+[FA for tuition],+0) AS [Charges less FA for tuition], [Query 7 - Students with TMS budget]![Total @T] AS [@T], [Charges less FA for tuition]-IIf([Query 7 - Students with TMS budget]![Total @T]<0,-[Query 7 - Students with TMS budget]![Total @T],0) AS [Charges less FA less @T]

FROM (((([Query 2 - Total charges fall 2007] LEFT JOIN [Query 3 - Total fin aid fall 2007] ON [Query 2 - Total charges fall 2007].ID_NUM = [Query 3 - Total fin aid fall 2007].ID_NUM) LEFT JOIN [Query 4 - Excluded aid - FWS] ON [Query 2 - Total charges fall 2007].ID_NUM = [Query 4 - Excluded aid - FWS].ID_NUM) LEFT JOIN [Query 5 - Excluded aid - HBV] ON [Query 2 - Total charges fall 2007].ID_NUM = [Query 5 - Excluded aid - HBV].ID_NUM) LEFT JOIN [Query 6 - Excluded aid - LBV] ON [Query 2 - Total charges fall 2007].ID_NUM = [Query 6 - Excluded aid - LBV].ID_NUM) LEFT JOIN [Query 7 - Students with TMS budget] ON [Query 2 - Total charges fall 2007].ID_NUM = [Query 7 - Students with TMS budget].ID_NUM

ORDER BY [Query 2 - Total charges fall 2007].ID_NUM;

or this query: 

SELECT [Query 8 - Fall 2007 balance after financial aid and TMS budget].ID_NUM, [Query 1 - Total balance by student id]![Stmt bal] AS [Stmt bal], [Query 8 - Fall 2007 balance after financial aid and TMS budget]![Charges less FA less @T] AS [Chgs less FA less @T], [Query 1 - Total balance by student id]![Stmt bal]-[Query 8 - Fall 2007 balance after financial aid and TMS budget]![Charges less FA less @T] AS [Prior bal], [Query 1 - Total balance by student id]![Stmt bal] AS [Stmt bal wo LBV and HBV], dbo_STUDENT_MASTER.CUR_STUD_DIV

FROM ([Query 1 - Total balance by student id] RIGHT JOIN [Query 8 - Fall 2007 balance after financial aid and TMS budget] ON [Query 1 - Total balance by student id].ID_NUM = [Query 8 - Fall 2007 balance after financial aid and TMS budget].ID_NUM) LEFT JOIN dbo_STUDENT_MASTER ON [Query 8 - Fall 2007 balance after financial aid and TMS budget].ID_NUM = dbo_STUDENT_MASTER.ID_NUM

ORDER BY [Query 8 - Fall 2007 balance after financial aid and TMS budget].ID_NUM;

I receive an 'Invalid Operation' error. I've seen other posts in relation to this problem, however a copy of this program does work on the server. It's when I changed the data source that I get this error. I initially experienced this issue when I moved this Access program to the server but after multiple attempts, the problem just went away. Now, it's back again because I need to change the data source due to a database upgrade. Can anyone give me some advice on how to solve this issue?

Answer 1

I don't see anything wrong with these queries. Not my naming convention, but that's beside the point.

Make sure the application has no broken references, and compiles.

Also run a /decompile for good measure.

Also re-link the tables.

Answer 2

Hello all,

We were able to resolve the issue. Initially, when I re-linked a database table to another database, that would cause the Invalid Operation error to appear. So I created a view out of that table, linked it to the Access program and modified the code to include that view. The program worked just fine after that. I too believe that this is a bug that Microsoft needs to address because the queries and the original database table worked just fine on another copy of the same program.

Latitia

Answer 3

Thank you for responding. the tables have been re-linked. There aren't any broken references and the it compiles. All the other steps worked just fine except for the two that I displayed.

Latitia