473,883 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

2003 - 2010 MDE problems

365 Contributor
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 5716
12,516 Recognized Expert Moderator MVP
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
365 Contributor
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
32,584 Recognized Expert Moderator MVP
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
365 Contributor
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
32,584 Recognized Expert Moderator MVP
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 :

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
2,322 Recognized Expert Moderator Top Contributor
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):
Access 2002-2003 database format
(Access Options->General->Default file format for Blank Database)
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.
Apr 28 '12 #7
365 Contributor
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
32,584 Recognized Expert Moderator MVP
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
365 Contributor
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

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

Similar topics

by: Robert Scheer | last post by:
Hi. When installing a windows 2003 server, the option to install IIS 6.0 also shows an item called ASP.NET. What is the purpose of this option? I heard that windows 2003 already has the .NET Framework inside it. Can I install VS.NET 2003 without problems on a windows 2003 server machine, or will I overwrite the version that comes with windows 2003? Thanks,
by: Philip Townsend | last post by:
I have a datagrid that contains a dropdown list that is part of an EditItemTemplate. I need to run some code when the dropdown list builds in order to preselect an item in the list. Preselecting the item is not the problem. The problem is that the dropdown object is not instantiated at the point at which the Edit button is clicked, or at any other obvious step in the process. In other words, when a user clicks the Edit (EditCommand)...
by: CharlieR | last post by:
Hi I have mangage to install the prerequisites disk for Vis Studio.net and am trying to now install VB.net 2003 Standard edition, using the setup wizard. When I click on the setup link the product opens my printer/scaner utility. I have no other options and if I close this window the setup also closes. Tried to bypass this by going via vs_setup file, but this insists I use the other setup and I go round the loop again.
by: ripvonboomer | last post by:
I am fairly new to programming, and have purchased and installed VS 2003. No problems with windows apps, but I tried to create a new ASP.Net web app, and it won't even build the solution. I am getting the following error: "Visual Studio .NET cannot create the application <application>. No Web server was detected at this URL: <URL> Internet Information Services (IIS) is not installed on this computer. You need to install IIS and then...
by: Chris Gage | last post by:
I'm a longtime *nix guy and am fairly comfortable "over there" although I am a developer and not a sysadmin, mostly J2EE stuff. I recently got the Microsoft "Action Pack" as a partner. It has legit licenses for a bunch of Microsft software for development/evaluation purposes. I decided to install Windows Server 2003 - the first time I have ever installed a Windows Server. I decided to install Apache rather than try to learn IIS, and...
by: kashif73 | last post by:
Hi, I have a strange problem in displaying of reports on Windows XP and Vista. My crosstab query display mission records for a particular month, for employees of my organisation. For example if I search by month of "April", the crosstab query will display all staff ( of different departments) who were on mission in April. On Windows Vista the report displays fine, that is the query only fetches records for APRIL. However on an XP system,...
by: sierra7 | last post by:
It seems Access 2010 is associating an 'input mask' or field type with a combo box when a form is opened, even though there is no Format setting on the control. I have a form which has been running OK for years in Access 2003; it adds an item selected from an inventory listing to either an existing Sales Order or Purchase Order. For historic reasons Purchase orders are alphanumeric and Sales orders are numeric (long integer). The RowSource...
by: pokemon | last post by:
Friends, The error logs are pasted below. Kindly advise remedial steps. Thanks Log Name: Microsoft-Windows-Diagnostics-Performance/Operational Source: Microsoft-Windows-Diagnostics-Performance Date: 11-09-2010 11:26:45 Event ID: 100 Task Category: Boot Performance Monitoring Level: Critical
by: Alan Yim | last post by:
Hi folks, My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question used work in 2003 (see below code). Private Sub engSave_Click() Dim strSql3 As String 'Archive order number and cost data for engine.
by: b wesenberg | last post by:
Good Morning, I am hoping that someone can assist me with a code problem. Here is the history of the issue. We had a database that was created in Access 2003 the database had a module in it called Median: Public Function MedianOfRst(RstName As String, Company_T As String, Company_V As String, State_T As String, State_V As String, Group_T As String, Group_V As String, Sub_Group_T As String, Sub_Group_V As String, Band_T As String,...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.