INSERT INTO query and Error 3073

I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel:

INSERT INTO [Excel 8.0;Database=C:\Test.xls;].[Sheet1$]
SELECT *
FROM Test;

This isn't the real table name or workbook name, but the logic is the same. This method is preferable to me because it's efficient and there's much less chance for user error. But when I use the query I get the dreaded Error 3073, "Operation must use an updateable query". I've tried it on both a network drive and my C drive with no success.

The strange thing is that I can use a Make Table query (Select...Into) to create an Excel workbook with the query copied to it. Both queries also work on my home PC.

I've checked with our system administrators at work and I have modified access to the drives in question, which means that I should be able to read, write, and delete with no problem. So despite the prevailing wisdom on other sites, this doesn't appear to be a permissions problem.

Jamie Collins, if you're still out there...Help!

re: INSERT INTO query and Error 3073

Hi. The INSERT INTO syntax you are using does not match what the current help file states for external data, which is

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

The externaldatabase part is the one where the path is provided. Unfortunately in trying to test this out I can't come up with a working version (not because of the external database syntax, which is similar to what you show, but because of a syntax error on the target table name - which is not optional).

Why not use Get External Data, Link Tables to link sheet 1 to Access as a linked table instead? You will then be able to run an append in just the same way as you can for any other Access table.

-Stewart

re: INSERT INTO query and Error 3073

Thanks, Stewart. You raise a good point. I only pursued this option because I could make it work at home. The workbook in question has 26 worksheets and I didn't like the idea of linking them all.

I did some more reseach and it appears another reason using Append Queries to write to Excel doesn't work has to do with which version of Access or Excel you're using and which SP you've upgraded to.

I decided to use some code I found that employs the CopyFromRecordset function in Excel and the xlCellTypeLastCell property. That solved my problem of inserting the query results at the end of the used range in Excel.

Therefore, I'm closing this post. If anyone is interested I'll post the code.