Append Records from one table to another from a DoCmd.RunSQL.

[DWolff]

I'd like to copy selected records from one Access table to another. I know how to create an Append Query, but this won't meet my needs, as I actually want to run 20 successive queries, and would prefer to do it with a single button rather than writing (and storing) 20 queries.

It seems that this should be easy, but I cannot seem to get the right syntax. I started with a normal SQL Insert as follows:

INSERT INTO TempFixCase17 ( ID, OrigLen, CaseNumber, NewCase, State, County, ann_fname, ann_lname, FilingDate, DocType )
SELECT TempFixCases.ID, TempFixCases.OrigLen, TempFixCases.CaseNumber, TempFixCases.NewCase, TempFixCases.State, TempFixCases.County, TempFixCases.ann_fname, TempFixCases.ann_lname, TempFixCases.FilingDate, TempFixCases.DocType
FROM TempFixCases
WHERE (((TempFixCases.LIST)="List017"));[

Not even close. When that didn't work, I found something here that told me to try:

DoCmd.RunSQL "Insert into [TempFixCase17] (Select from [TempFixCases] where [TempFixCases].
[List]="List017")

That didn't work either.

Can someone tell me the magic?

(P.S. This is my first time on the forum. Only after I posted did I notice the "FORMS" forum where, perhaps, this question belongs. My apologies)

[June7]

Why not working - error message, wrong results, nothing happens?

Why do you need to copy records as opposed to just filtering?

The queries look fine except for the VBA approach. Use apostrophes around List017 instead of quotes and then final quote at the end. Also not sure the parens are needed, might cause issue. I prefer CurrentDb.Execute method to run sql actions:

CurrentDb.Execute "INSERT INTO TempFixCase17 SELECT FROM TempFixCases WHERE List='List017'"

[DWolff]

Thanks so much, June7. I appreciate your help VERY much.

First, to answer your question (trying to keep it short). We use SharePoint, and I update 20 lists regularly. I run a query in Access to determine all the data that needs updating by list. I use SSIS to link directly from a table in Access to each SharePoint List (1 to 1 relation between access table and SharePoint list). For this reason, I need to parse all the data from the query into 20 separate Access tables to update each list

I was unaware of the CurrentDb.Execute function, which I much prefer. Unfortunately, when I tried it (exactly as you typed), it gave a a run-time error 3134 indicating an error in the INSERT INTO statement. I then realized you left out the * in the select statement ". . .SELECT * FROM TempFixCases WHERE List='List017'". It worked GREAT.

Why not working - error message, wrong results, nothing happens?

Why do you need to copy records as opposed to just filtering?

The queries look fine except for the VBA approach. Use apostrophes around List017 instead of quotes and then final quote at the end. Also not sure the parens are needed, might cause issue. I prefer CurrentDb.Execute method to run sql actions:

CurrentDb.Execute "INSERT INTO TempFixCase17 SELECT FROM TempFixCases WHERE List='List017'"

[June7]

Ooops! And I was copying from your post as i typed, didn't hit me missing the fields (or the *) in the SELECT.

Glad that works now. So this issue is resolved?