Reserved error -3025

[Kevlarhead]

I've got a database which imports 2 text files into temp tables, appends them to a single large table, and then drops the temp tables. That bit works perfectly.

I'm now trying to add some form of logging to this. My idea was to make a logging table, and call a query which would capture the user's name, file being added, date, time and the number of rows being imported. To avoid doing anything fancy, I decided I'd make one query for each file, so the filenames are hardcoded into the query. I made an insert query, which I thought was fairly straightforward, and found it failed each time reporting the ever-so-helpful 'Reserved error -3025'.

I've now changed the INSERT into a SELECT purely for test purposes and it's still giving me the same error.

My query for making up most of the log details works fine:
SELECT 'File1' AS Filename, Environ('Username') As User_ID, Now() AS Import_DateTime

My query for getting the count of the rows in the temp table works too.
SELECT COUNT(Part_No) FROM tbl_TempFile1 AS Record_Count

But when I put the two together i get the Reserved error
SELECT 'File1' AS Filename, (SELECT Count(Part_No) FROM tbl_TempFile1) AS Record_Count, Environ('Username') AS User_ID, Now() AS Import_DateTime

I can't see what I'm doing wrong here. Can anyone suggest what I need to do to make this work?

 

[SydneyGeek]

When you insert records you need a specific syntax. See... Creating a Log table in Access -- DataWright Information Services
Also the DateTime is a reserved word, meaning that you should (and in Access 2010, cannot) use it in code. See ... Access 2007 reserved words and symbols - Access - Office.com

Denis