Link to home
Create AccountLog in
Avatar of BobRosas
BobRosas

asked on

SQL works for Access 2000 not for 2007. Why

I know this is a long query but does anyone have an idea why it works in Access 2000 and not Access 2007?  When I run it in Access 2000 it runs quickly (2 seconds maybe).  When I try it in 2007 I've let it run for 3 minutes and still nothing.  I finally just stop it.  I tried re-creating it one field at a time and it will run for fields:
EmpId
TimeInFmt
TimeSheetMinutes
BreakFlag
MissedInPunch
MissedOutPunch
But I tried adding the rest one at a time and it hangs up for each one.  What is up???


SELECT dbo_UserList.UserName AS Manager, dbo_EmployeeHours.EmployeeId AS EmpId, Format([TimeIn],"Short Date") AS TimeInFmt, dbo_EmployeeHours.TimeIn, dbo_EmployeeHours.TimeOut, dbo_EmployeeHours.TimeSheetMinutes, dbo_EmployeeHours.BreakFlag, dbo_EmployeeHours.MissedInPunch, dbo_EmployeeHours.MissedOutPunch, dbo_EmployeeList.Suspend, dbo_UserList.Company INTO tblTimeLog
FROM ((dbo_EmployeeHours INNER JOIN dbo_EmployeeCustomFieldsData ON (dbo_EmployeeHours.EmployeeId = dbo_EmployeeCustomFieldsData.EmployeeId) AND (dbo_EmployeeHours.Company = dbo_EmployeeCustomFieldsData.Company)) INNER JOIN dbo_EmployeeList ON (dbo_EmployeeCustomFieldsData.EmployeeId = dbo_EmployeeList.EmployeeId) AND (dbo_EmployeeCustomFieldsData.Company = dbo_EmployeeList.Company)) LEFT JOIN dbo_UserList ON dbo_EmployeeList.ManagerUID = dbo_UserList.UID
WHERE (((dbo_EmployeeHours.EmployeeId)<>500 And (dbo_EmployeeHours.EmployeeId)<>999) AND ((dbo_EmployeeHours.TimeIn) Between ([Forms]![frmTCHours]![txtFrom]) And (DateAdd("d",1,[Forms]![frmTCHours]![txtTo]))) AND ((dbo_EmployeeList.Suspend)=0) AND ((dbo_UserList.Company)=1 Or (dbo_UserList.Company) Is Null) AND ((dbo_EmployeeCustomFieldsData.CustomFieldData)="DH" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="CL" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="EX") AND ((dbo_EmployeeList.Company)=1)) OR (((dbo_EmployeeHours.EmployeeId)<>500 And (dbo_EmployeeHours.EmployeeId)<>999) AND ((dbo_EmployeeHours.TimeOut) Between ([Forms]![frmTCHours]![txtFrom]) And (DateAdd("d",1,[Forms]![frmTCHours]![txtTo]))) AND ((dbo_EmployeeList.Suspend)=0) AND ((dbo_UserList.Company)=1 Or (dbo_UserList.Company) Is Null) AND ((dbo_EmployeeCustomFieldsData.CustomFieldData)="DH" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="CL" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="EX") AND ((dbo_EmployeeList.Company)=1))
ORDER BY dbo_UserList.UserName, dbo_EmployeeHours.EmployeeId, dbo_EmployeeHours.TimeIn, dbo_EmployeeHours.TimeOut;

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, can you try if this works?
SELECT dbo_UserList.UserName AS Manager, dbo_EmployeeHours.EmployeeId AS EmpId, Format([TimeIn],"Short Date") AS TimeInFmt, dbo_EmployeeHours.TimeIn, dbo_EmployeeHours.TimeOut, dbo_EmployeeHours.TimeSheetMinutes, dbo_EmployeeHours.BreakFlag, dbo_EmployeeHours.MissedInPunch, dbo_EmployeeHours.MissedOutPunch, dbo_EmployeeList.Suspend, dbo_UserList.Company INTO tblTimeLog
FROM ((dbo_EmployeeHours INNER JOIN dbo_EmployeeCustomFieldsData ON (dbo_EmployeeHours.EmployeeId = dbo_EmployeeCustomFieldsData.EmployeeId) AND (dbo_EmployeeHours.Company = dbo_EmployeeCustomFieldsData.Company)) INNER JOIN dbo_EmployeeList ON (dbo_EmployeeCustomFieldsData.EmployeeId = dbo_EmployeeList.EmployeeId) AND (dbo_EmployeeCustomFieldsData.Company = dbo_EmployeeList.Company)) LEFT JOIN dbo_UserList ON dbo_EmployeeList.ManagerUID = dbo_UserList.UID
WHERE (((dbo_EmployeeHours.EmployeeId)<>500 And (dbo_EmployeeHours.EmployeeId)<>999) AND ((dbo_EmployeeList.Suspend)=0) AND ((dbo_UserList.Company)=1 Or (dbo_UserList.Company) Is Null) AND ((dbo_EmployeeCustomFieldsData.CustomFieldData)="DH" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="CL" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="EX") AND ((dbo_EmployeeList.Company)=1)) OR (((dbo_EmployeeHours.EmployeeId)<>500 And (dbo_EmployeeHours.EmployeeId)<>999) AND ((dbo_EmployeeList.Suspend)=0) AND ((dbo_UserList.Company)=1 Or (dbo_UserList.Company) Is Null) AND ((dbo_EmployeeCustomFieldsData.CustomFieldData)="DH" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="CL" Or (dbo_EmployeeCustomFieldsData.CustomFieldData)="EX") AND ((dbo_EmployeeList.Company)=1))
ORDER BY dbo_UserList.UserName, dbo_EmployeeHours.EmployeeId, dbo_EmployeeHours.TimeIn, dbo_EmployeeHours.TimeOut;

Open in new window

Avatar of BobRosas
BobRosas

ASKER

Thank you for responding.  I'm hoping my problem is something simple that relates to upgrading to 2007.  I tried your code above and it still does the same thing.  I let it run for almost 2 minutes and then went ahead and stopped it.  I'm really at a loss.
I haven't a clue, but want to ask some diagnostic questions.
Linked table? From SQL DB?
If not, could you post a sample db, with either representative data, or empty if you cannot do that, to www.ee-stuff.com? (Access 200 version, please.)
I have brought Access 2000 db's into Access 2007 (eval copy), and never had a problem with SQL working when tables were in the actual DB.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I've done some pretty major projects with both environments and have done conversions.   Access 2k7 does not work like the previous versions.  MS appears to be trying to kill the product.  There are a lot of changes made to 2k7 as well as instability. I don't know if I missed these questions but:
1.  Are you using an Access 2007 ADP, MDB or ACCD file?
2.  Do all of your related tables have clearly defined Primary Keys?
3.  Do you have a database diagram drawn defining the PK/FK relationships and restraints?

The best way to troubleshoot this is to reduce to just a few fields.  Use all the tables you intend to but only add a small amount of fields.  Start with one field from each desired table and just add fields until it stops.  It might be that the fields that stop the query from working are either blank(Null) or they are somehow tied to other tables that don't contain data.

Access 2k7 really stinks and there are a lot of "gotchas" I found out the hard way during a development project.  Make sure and do all available updates for Office/Access 2k7 and XP.  Also, if you are new to 2k7, make sure and save your code frequently and backup your DB frequently.  I had Access 2k7 corrupt nearly 4 hours of code development.  This link is also an excellent source for answers about 2007:

http://allenbrowne.com/Access2007.html#Bugs
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I really appreciate all your comments.  I apologize for my delay in getting back to you.  It's been one of those days.  

jerryb30
I'm linked to a SQL database from a program that was purchased so I don't have much control except the ability to link to the tables.

JImD
I have refreshed and done a compact and repair with no luck.  I'm still trying to check indexes.  I'll let you know.

Arji
I've tried runing the query without any criteria attached (Ex: ([Forms]![frmTCHours]![txtFrom]))
and that didn't help but next I will try to hard code the values and see if that helps.  

Thanks again to all of you for your input.  I will do a little more testing with your suggestions and try to get back with you in a more timely matter.


The query is a make table query.  By breaking the query up into 2 queries it looks like the created table has the correct data - atleast the record count is right.  And it now runs quickly.  Unfortunately it falls thru the remainder of my code and only gives me about 1/2 of what I need but your comments have helped and I will use them to continue to work thru this.  Thanks for all your help.