Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Upgrade from Access 2007 to 2010 problem with a Query

Status
Not open for further replies.

notconfident

Programmer
Aug 9, 2008
18
GB
This query works fine with Access 2007 and on 2 other PCs running Access 2010 but not on mine since a bought a new laptop and loaded 2010.
In real time I run the Query from my VBA Code but get the same error if I just try to run the Query direct. This is the error I get :-
Undefined function ‘DCount’ in expression
This is the troublesome Query:-
UPDATE TblIMC SET TblIMC.IMCcount = DCount("Ref_No","Current_Marshal","[BookedIMC] = " & [TblIMC]![IMCid])
WHERE (((TblIMC.IMCdate)>Now()));

When I try to run the Query direct I get the error every time but at normal run time when I get the error as :-
Run-time error '3085':
Undefined function ‘DCount’ in expression
with buttons End Debug Help
Now here is the strange bit.
If I click on 'End' the program runs fine after that until I restart the program.
If I click on 'Debug' it highlights the underlying VBA at DoCmd.OpenQuery "QryIMC_Count" and after closing the Form the error will happen again.
As it appears to only be my Laptop on Access 2010 I compared the References to one of the other PCs that is ok on Access 2010.
They both say the same ?????
There are 6 lines ticked :-
Visual Basic For Applications
Microsoft Access 14.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5......
Microsoft Office 14.0 Access database engine Objec......
Microsoft Office 14.0 Object Library

If I use ECount instead of DCount all is ok but I dont want to do this as it only appears to be my Laptop that has the issue and am worried that the error could happen elsewhere in the Database.

Sorry for all the typing but I couldnt use copy /paste into this box

Hope someone can help
Thanks



 
The error is typical when one of the references are missing. When you see the references checked, one or more typically has the prefix "Missing:". It seems like you would have spotted this if it were the case.

The database file could maybe be corrupt on your machine. You could try getting a fresh copy or trying that file on another system.

While I have not encountered it personally, the next reasonable assumption is it is a corrupt install. I would try uninstalling Access, rebooting and then Installing Access fresh.

After that I would consider a hardware issue and malware...

If you find it, I would be curious as to what worked as it may be my first guess if I ever see it again.
 
Thanks Lameid
I am away from home at the moment with my new laptop.
The database is in use back home at our club headquarters. I have access to it via TeamViewer but I dont want to do anything dramatic until back home next week.
I am a little concerned about reinstalling Office as I used up the last licence on a CD, which I copied to a pen drive as this new Laptop has no DVD Drive.
Will Microsoft "allow" me to install it again on the same Laptop ?????

I have had the same results on various copies of the database.
I ran compact and repair and scanned all my database files using Norton.

While away I have been doing various mods and all have seemed ok but I used this Query "QryIMC_Count" the other day with this result....
I use DCount in many places and it appears to be ok.

Lets see if anyone else has a less frightening idea until I return home.

Thanks again
 
Sorry I did not see you last post until now. I would expect a repair install would be fine but on the other hand I am suspicious of the install itself... if there was an error copying from DVD to external media, you may have an issue with your installation file.

There are so many permutations to how Microsoft does licensing that without knowing exactly what you have, I am hesitant to hazard a guess. There should be a way to reinstall it even if it requires a phone call.
 
Thanks again Lameid
I am back home this week end so can actually visit the working database if things go wrong with the re-install or repair install. I did have the same thoughts as you about my original method so I think I will copy the CD mounted in my old Laptop (with a DVD Drive) direct to the New Laptop Hard Drive and run it from there.
I will post the result early next week
Thanks
 
Here I am again Lameid with my "report"
I followed your instructions :-
Ran Office 2010 Repair, still the same error.
Uninstalled and reinstalled Office 2010, still the same error.
Oh Dear I was confused now but had another think.
The Office 2010 CD included Service Pack 1 !!!
I installed Service Pack 2 and all was ok, it was great to see no error.
The same CD was used to install Office 2010 on the 2 PCs that did not display the error but I checked and they had Service Pack 2 Installed (not by me).
So Mr Microsoft must have discovered there was a problem. These 2 were installed some time ago, I dont know when SP2 was installed but nobody told me of any issues. It was when I bought a new Laptop and used the same CD to install Office 2010 on it. Actually it was very lucky that I noticed myself. I just happened to use the only process that used the Query that had DCount included. DCount is used all over my code with no problem.

Thanks for your interest and now you can remind future members to check they have the latest Service Pack installed
Thanks
Keith
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top