By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

2003 - 2010 MDE problems

100+
P: 365
Hi Fellas, its been a while,

Got a little problem with a project written in 2003 (access obviously).... we recently upgraded to 2010 at work, and now the MDE appears to not work (in 2010) however the MDB works fine so it seems.

I dont have the time right now to do any heavy work on this so i was hoping it would "just work", if i converted it to the new file formats would i encounter any difficulties?

the message i get (when running the MDE in 2010) is this:

Expand|Select|Wrap|Line Numbers
  1. Err.number = 3075
  2. Err.desc = Function is not available in query expression '(((tblStaff.Deleted)=False) AND ((tblAppraisal.AppMan)=1 AND ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date()))'.
Any ideas??
Apr 27 '12 #1
Share this Question
Share on Google+
22 Replies


Rabbit
Expert Mod 10K+
P: 12,357
That query shouldn't run in any version. I assume that SQL is from the WHERE clause of a query. You can not use aggregate functions in the WHERE clause, move it to a HAVING clause.
Apr 27 '12 #2

100+
P: 365
Thats the specific error received during execution of the code, it works perfectly fine in 2003 so i dont expect there is anything wrong with the query itself and as i say, the full version works in 2010, just not the MDE file.
Apr 27 '12 #3

NeoPa
Expert Mod 15k+
P: 31,418
Dan2kx:
it works perfectly fine in 2003 so i dont expect there is anything wrong with the query itself
That is not reliable logic. It may point towards that, but it's perfectly possible for problems to be overlooked in one version but not another. Changes between versions are to be expected of course. I'm not saying that is the problem, just that you cannot rely on such logic to assume it cannot be.

Are you saying that the MDB file works fine in 2010 when doing exactly the same thing as you did when trying it with the MDE when you got the above problem?
Apr 27 '12 #4

100+
P: 365
Yep NeoPa, works fine as MDB in 2010, VBA compiles fine, interestingly, if I create a new MDE within 2010, upon execution it just loads access (not the file) then from 2003 it says it's too new!
Apr 27 '12 #5

NeoPa
Expert Mod 15k+
P: 31,418
I didn't know 2010 could create MDEs as well as ACCDEs. There may be a compatibility problem somewhere, and it sounds like it's a call to a function from within SQL (which would explain why compiling didn't catch anything). The function calls in the listed SQL excerpt are :
Max()
DateAdd()
Date().

I guess you'll have to play around until you find the culprit. Share with us which clause this excerpt is found in too, as that would be interesting. It looks like a WHERE or HAVING clause, but which one of those it is could be illuminating.
Apr 27 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi, this is something I got from Mark Burns, but as I see it, it only relates to opening .mde with a previous version of access (not 2010):
BACKWARDS COMPATIBLE .MDB/.MDE SOLVED:
set
Access 2002-2003 database format
(Access Options->General->Default file format for Blank Database)
set
Encryption Method = Use Legacy Encryption
(Access Options->Client Settings->Encryption Method)

Picture Property Storagre Format = Convert all picture data to bitmaps
(Access Options->Current Database->Picture Property Storage Format)
I have also found that one of the functions I previously had used in a query Environ("UserName") was no longer available to be run inside a query when switching from AC2003 to AC2010, but I could still use it in VBA so I just made my code parse the string before sending it to the query engine. However, none of the functions you have listed should behave like the environ, so I might suspect that the problem is somewhere else and not directly related to the error message. Please post the full SQL of your query.
Apr 28 '12 #7

100+
P: 365
I don't think there is anything wrong with the SQL, it's a pretty standard query, and has been working fine in 2003, I have just found a Microsoft site changes in access 2010 this mentions the removal of a reference to MSCAL.OCX, I had used this in 2003, and re-referenced it in 2010 (MDB) (because it said it was missing), I guess the reference must not be carried into the MDE file?

What do you guys think?

If so, the only way to solve is to remove the calendar controls I've been using, or change to 2010 format and use the new date switcher. Would I expect to find many differences between VBA 6.0 and 7.0 if I changed?
May 3 '12 #8

NeoPa
Expert Mod 15k+
P: 31,418
Dan, you won't win many friends by deciding not to bother posting your SQL when you've been asked to. It might make sense if we were coming to you for help, but the other way around really doesn't ;-)
May 3 '12 #9

100+
P: 365
Sorry NeoPa, it was late (LOL), here is the code:
Expand|Select|Wrap|Line Numbers
  1. Function AppCheck() As Boolean
  2. Dim rst As DAO.Recordset, msg As Boolean
  3.     Set rst = CurrentDb.OpenRecordset("SELECT tblAppraisal.AppID, tblAppraisal.StaffID, [Forename] & ' ' & [Surname] AS Name, tblAppraisal.Date FROM tblAppraisal INNER JOIN tblStaff ON tblAppraisal.StaffID = tblStaff.StaffID GROUP BY tblAppraisal.AppID, tblAppraisal.StaffID, [Forename] & ' ' & [Surname], tblAppraisal.Date, tblStaff.Deleted, tblAppraisal.AppMan HAVING (((tblStaff.Deleted)=False) AND ((tblAppraisal.AppMan)=" & LogStaffID & ") AND ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date())));")
  4.     If rst.RecordCount > 0 Then
  5.         rst.MoveLast
  6.         msg = True
  7.     End If
  8.     If msg = True Then
  9.         If Application.CurrentProject.AllForms("frmStaffHols").IsLoaded Then
  10.             Forms.frmStaffhols.Visible = False
  11.         End If
  12.             If MsgBox("You have " & rst.RecordCount & " pending employee appraisals to perform, would you like to view/action the list?", vbInformation + vbYesNo, "Pending on your Appraisal List...") _
  13.                     = vbYes Then AppCheck = True
  14.         If Application.CurrentProject.AllForms("frmStaffHols").IsLoaded Then
  15.             Forms.frmStaffhols.Visible = True
  16.         End If
  17.     End If
  18. End Function
The SQL is obviously on line 3, "LogStaffID" is a Public variable (Long) set elsewhere based on (custom) login, this code runs after authentication, but before the user is presented with a (custom) main menu.

Any more let me know
May 3 '12 #10

NeoPa
Expert Mod 15k+
P: 31,418
Thank you Dan. I've looked through your posted SQL and see nothing that catches my attention other than the three function calls already commented on in post #6.

I would just make a further comment, not to be critical, but simply for you to bear in mind in future dealings with the site :
When posting SQL there are two fairly important points to remember :
  1. Post the actual SQL used rather than the VBA code which creates the SQL. I don't imagine it makes a lot of difference in this situation, but it means that anyone looking at it can concentrate on the SQL itself rather than using half a mind working out what it would look like when produced. This is particularly important when variables are used to create bits of the SQL (EG. LogStaffID).
  2. Display the SQL in human-readable form. A single line of text requiring continuous scrolling to read and make sense of, is fine for a SQL parser, but makes it hard to work with as a human. Very few members bother to think of this to be fair, so you can make your questions stand out by showing your SQL as below.
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS Name
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. GROUP BY tblAppraisal.AppID
  10.        , tblAppraisal.StaffID
  11.        , [Forename] & ' ' & [Surname]
  12.        , tblAppraisal.Date
  13.        , tblStaff.Deleted
  14.        , tblAppraisal.AppMan
  15. HAVING   (((tblStaff.Deleted)=False)
  16.    AND   ((tblAppraisal.AppMan)=???)
  17.    AND   ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date())))
May 5 '12 #11

100+
P: 365
Noted, sorry again NeoPa, what do you think to the invalid reference theory i suggested? i havent tried it yet because it would mean quite a few UserForm modifications.
May 5 '12 #12

NeoPa
Expert Mod 15k+
P: 31,418
Having now had a chance to see it in a more readable form it occurs to me that using Max() on a field which is included in the GROUP BY clause (tblAppraisal.Date) can never work. I've never seen SQL such as this be accepted by Access (of any version). I suspect you want to lose the function call Max() from your HAVING clause. Try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS [Name]
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. WHERE    (NOT tblStaff.Deleted)
  10.   AND    (tblAppraisal.AppMan=???)
  11. GROUP BY tblAppraisal.AppID
  12.        , tblAppraisal.StaffID
  13.        , [Forename] & ' ' & [Surname]
  14.        , tblAppraisal.Date
  15. HAVING   (tblAppraisal.Date<DateAdd('yyyy',-1,Date()))
I expect your reporting this as working in an earlier version is an honest mistake. I'm confident you believed that when you posted it, but that rather misled the searches I expect. I'm sure it's not important now, but if you go back to an earlier version and try to enter your original SQL into a new QueryDef then I'm sure it will give you an error message. It might be interesting to hear how this came about though, if you find out.
May 5 '12 #13

NeoPa
Expert Mod 15k+
P: 31,418
Dan2kx:
Noted, sorry again NeoPa, what do you think to the invalid reference theory I suggested? I haven't tried it yet because it would mean quite a few UserForm modifications.
No need to apologise Dan. I wasn't trying to criticise. Simply illustrate a better way forward for you ;-)

I suspect, now I've seen the SQL in its full glory, that the problem is identified and the invalid reference theory was a red herring. We only know that now, of course. Ideas are always worth checking, until you find an answer.
May 5 '12 #14

100+
P: 365
Hello again,

I have just tried the SQL as a new Query, and it works error free (in the MDB, 2010), i used MAX because in the lookup table there can be multiple dates per StaffID and i wanted the last one.

Expand|Select|Wrap|Line Numbers
  1. tblAppraisal:
  2. AppID, Auto, PK
  3. StaffID, Num, FK
  4. AppMan, Num, FK 
  5. Date, D/T
May 5 '12 #15

NeoPa
Expert Mod 15k+
P: 31,418
Dan2kx:
I have just tried the SQL as a new Query, and it works error free (in the MDB, 2010)
Very strange. Perhaps you might clarify exactly what you tried. Are you saying that the same SQL you ran before, which threw up the error, is now running perfectly without errors after you put it in a new QueryDef? This is quite a statement.

Can you appreciate that Max([X]) is fairly meaningless when there is only one possible value of [X] (as ensured by GROUP BY [X])? That is why it will never be accepted by Jet (Access SQL).

Dan2kx:
I used MAX because in the lookup table there can be multiple dates per StaffID and i wanted the last one.
That indicates you have more work to do with your query to get it to work as intended. None of the SQL discussed so far will give you that result (Neither yours nor mine).

I suggest we get to the bottom of this seeming anomaly with using Max() on a field which is also included in the GROUP BY clause first, and then you may want to ask a separate question about how to select only the item that matches the maximum date within a group. It would be pointless to do that at this stage as your understanding of the issue would not be clear enough now to ask the question clearly. The primary problem would have to be cleared up first for it to make contextual sense.
May 5 '12 #16

100+
P: 365
Very strange. Perhaps you might clarify exactly what you tried. Are you saying that the same SQL you ran before, which threw up the error, is now running perfectly without errors after you put it in a new QueryDef? This is quite a statement.
The error only occurs in the MDE file, and only if the MDE file is run in 2010, the MDB file works in all versions, and the Same SQL as a new Query also works and returns results.
Can you appreciate that Max([X]) is fairly meaningless when there is only one possible value of [X] (as ensured by GROUP BY [X])? That is why it will never be accepted by Jet (Access SQL).
As i see it, if grouping by date, it will list all the variable dates, if selecting the Max of date, and then grouping, i will only get one date, the maximum, and that also appears to work as expected.

That indicates you have more work to do with your query to get it to work as intended. None of the SQL discussed so far will give you that result (Neither yours nor mine).

I suggest we get to the bottom of this seeming anomaly with using Max() on a field which is also included in the GROUP BY clause first, and then you may want to ask a separate question about how to select only the item that matches the maximum date within a group. It would be pointless to do that at this stage as your understanding of the issue would not be clear enough now to ask the question clearly. The primary problem would have to be cleared up first for it to make contextual sense.
As above, the problem is only with MDE, i cannot reproduce the error code that the MDE provides anywhere else, other than in the MDE, but as you know, that is not for degugging.

PS, how do you quote proberly on here? lol
May 5 '12 #17

NeoPa
Expert Mod 15k+
P: 31,418
Dan2kx:
The error only occurs in the MDE file, and only if the MDE file is run in 2010, the MDB file works in all versions, and the Same SQL as a new Query also works and returns results.
I'll assume this is an answer of "Yes". I just tested it out myself, and to my great surprise, this illogical SQL is allowed by Jet in MDB format.

Dan2kx:
As i see it, if grouping by date, it will list all the variable dates, if selecting the Max of date, and then grouping, i will only get one date, the maximum, and that also appears to work as expected.
I'm afraid that illustrates some flawed logic. The GROUP BY clause occurs before the HAVING clause, so only one date is ever available to it. The Max() of [X] therefore, is simply [X]. Including Max() is thus illogical. Something I guess 2010 MDE files notice, even if MDB files don't.

Have you tried the changed SQL yet, that I suggested in post #13? Posting how you got on with that may well save further time, and will definately help the understanding of the situation.

Dan2kx:
PS, how do you quote properly on here? lol
Here is an example from post #14 :
NeoPa:
[quote=Dan2kx][highlight]Dan2kx:[/highlight]
Noted, sorry again NeoPa, what do you think to the invalid reference theory I suggested? I haven't tried it yet because it would mean quite a few UserForm modifications.[/quote]
No need to apologise Dan. I wasn't trying to criticise. Simply illustrate a better way forward for you ;-)
I have a macro to help me do it in TextPad (My preferred text editor).
May 5 '12 #18

100+
P: 365
NeoPa
Have you tried the changed SQL yet, that I suggested in post #13? Posting how you got on with that may well save further time, and will definately help the understanding of the situation.
Just tried (with your SQL from #13), now in the MDE i get the same error, but with the reduced parameters

Expand|Select|Wrap|Line Numbers
  1. Function is not available in query expression '(((tblAppraisal.Date<DateAdd('yyyy',-1,Date()))'
May 5 '12 #19

NeoPa
Expert Mod 15k+
P: 31,418
Interesting. Not what I expected, but try this for me if you would :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS [Name]
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. WHERE    (NOT tblStaff.Deleted)
  10.   AND    (tblAppraisal.AppMan=???)
  11.   AND    (tblAppraisal.Date<DateAdd('yyyy',-1,Date()))
  12. GROUP BY tblAppraisal.AppID
  13.        , tblAppraisal.StaffID
  14.        , [Forename] & ' ' & [Surname]
  15.        , tblAppraisal.Date
I'd overlooked the fact that tblAppraisal.Date, without the Max() call, could be checked in the WHERE clause (and this is a better idea where possible - even though Access defaults to using the HAVING clause for criteria in any aggregate query).
May 5 '12 #20

100+
P: 365
NeoPa (et al), i have solved the problem, (apart from the obvious MAX logic you pointed out), and it was to do with the MSCAL.OCX file, i checked the reference again, and it was pointing to the C:\program........\office11\....

Office 11 as you will know will not exist on a 2010 install

i had repointed it to the network location within the MDB from 2010, but everytime i re-made the MDE on another PC (in 2003) it adjusted the reference back to the local version, hence the problem. Strange presentation though i'm sure you will agree.

Next question... how to overcome the Max problem...

Non of the SQL variations give me the last date, including mine (whoops) as you said, is the only way to do this with two seperate queries?
May 5 '12 #21

NeoPa
Expert Mod 15k+
P: 31,418
Dan2kx:
Next question... how to overcome the Max problem...
Absolutely.

I'll look out for this when you post it (Separate thread as I mentioned before).

PS. Well done for finding that. I admit it surprises me, but you checked it anyway, so well done. Which function was effected? I wouldn't expect either Date() or DateAdd() to be found in there. A real surprise.

PPS. Typically MS Office products replace any missing reference to older MS Office libraries with the one for the latest version. It's quite unclear how you would arrive at this problem.
May 5 '12 #22

100+
P: 365
NeoPa
absolutely.

I'll look out for this when you post it (Separate thread as I mentioned before).
I will put it in a new thread as you request, i will do that soon (nearly hometime).

NeoPa
PS. Well done for finding that. I admit it surprises me, but you checked it anyway, so well done. Which function was effected? I wouldn't expect either Date() or DateAdd() to be found in there. A real surprise.

PPS. Typically MS Office products replace any missing reference to older MS Office libraries with the one for the latest version. It's quite unclear how you would arrive at this problem.
Well i found from that link (from my post #8) that the MSCAL.OSX function/feature has been been specifically removed from 2010 to allow for their date selector tool (which i havent tried).

I doubt that any of the functions were particularly affected, and up until that break in the code i dont think it was even used (its just a custom date picker in effect), maybe it was just the first piece of SQL code to be executed.

Thanks anyway NeoPa, you have obviously pointed out something i had overlooked.
May 5 '12 #23

Post your reply

Sign in to post your reply or Sign up for a free account.