469,356 Members | 2,013 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

2003 - 2010 MDE problems

365 100+
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
22 5459
Rabbit
12,516 Expert Mod 8TB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
2,321 Expert Mod 2GB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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
32,185 Expert Mod 16PB
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
Dan2kx
365 100+
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.

Similar topics

3 posts views Thread by Robert Scheer | last post: by
1 post views Thread by Philip Townsend | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.