2003 - 2010 MDE problems

[Dan2kx]

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:

Err.number = 3075
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??

[Rabbit]

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.

[Dan2kx]

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.

[NeoPa]

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?

[Dan2kx]

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!

[NeoPa]

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.

[TheSmileyCoder]

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("UserNa me") 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.

[Dan2kx]

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?

[NeoPa]

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 ;-)

[Dan2kx]

Sorry NeoPa, it was late (LOL), here is the code:

Function AppCheck() As Boolean
Dim rst As DAO.Recordset, msg As Boolean
    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())));")
    If rst.RecordCount > 0 Then
        rst.MoveLast
        msg = True
    End If
    If msg = True Then
        If Application.CurrentProject.AllForms("frmStaffHols").IsLoaded Then
            Forms.frmStaffhols.Visible = False
        End If
            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...") _
                    = vbYes Then AppCheck = True
        If Application.CurrentProject.AllForms("frmStaffHols").IsLoaded Then
            Forms.frmStaffhols.Visible = True
        End If
    End If
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

[NeoPa]

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.

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)=???)
   AND   ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date())))

[Dan2kx]

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.

[NeoPa]

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.D ate) 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 :

SELECT   tblAppraisal.AppID
       , tblAppraisal.StaffID
       , [Forename] & ' ' & [Surname] AS [Name]
       , tblAppraisal.Date
FROM     tblAppraisal
         INNER JOIN
         tblStaff
  ON     tblAppraisal.StaffID = tblStaff.StaffID
WHERE    (NOT tblStaff.Deleted)
  AND    (tblAppraisal.AppMan=???)
GROUP BY tblAppraisal.AppID
       , tblAppraisal.StaffID
       , [Forename] & ' ' & [Surname]
       , tblAppraisal.Date
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.

[NeoPa]

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.

[Dan2kx]

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.

tblAppraisal:
AppID, Auto, PK
StaffID, Num, FK
AppMan, Num, FK 
Date, D/T