Insert SQL data into Access

IBMJunkman
IBMJunkman used Ask the Experts™
on
Error messageI have an empty Access table.  I need to select data from a SQL DB and insert into the Access table.  I eventually will do this using VBA but for now I am just trying to get the major steps working.
The Access table is defined. Named TSHrs. Contains 3 fields (TSEmpID, TSHrs, TSDate)
I am doing a Pass-Through query
The ODBC connection is defined.
The query is:
Insert into TSHrs ([TSEmpID], [TSHrs], [TSDate])
Select EmployeeID as [EmpID]
,isnull(sum(HoursWorked),0) as [Hours]
,DatePart(Year,DateWorked)*100+Datepart(Month,DateWorked) AS [RptMonth]
from Hours
where DatePart(Year,DateWorked)*100+Datepart(Month,DateWorked)=201202
group by EmployeeID, DatePart(Year,DateWorked)*100+Datepart(Month,DateWorked)
order by EmployeeID

When I run the query in Access I get the pictured error.  It appears I am trying to reference TSHrs in the SQL DB the ODBC references.  Have I approached this wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
A passthrough query is expecting to find everything in the sql server database.  

Why not create a linked table in Access to the SQL Server table and then you can write a local query?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial