Want to export some but not all records

[Guest]

I want to export select table records from database1 to another database's
table (database2). I have designed a select query in database1 to find the
desired records. I have created a macro in database1 with the intent of
exporting the query results to a table in database2 but when I run the macro
I get the message that my TABLE in database1 can't be deleted due to
relationships it is participating in. I don't want to delete the table, just
some of the records. Here are the parameters I have set in the macro...

Action TransferDatabase

Transfer Type Export
Database Type Microsoft Access
Database Name full path including extension of the database2 (where I want
to export to)
Object Type Query (got the same error message when I had this set to
Table)
Source name of my select query in database1
Destination Name of table in database2 where I want to export the
records
Structure Only No

Another thought crosses my mind; should I attempt to do this through VB
code? If so, do you have code you could share?

 

[Ken Snell [MVP]]

It would be better if you link to the table in database2, and then run an
append query to copy data from the query in database1 to the table in
database2.

 

[Guest]

Great idea! Would the append query delete the records in database1 or would
I also have to follow the append query with a delete query to accomplish this?

 

[Ken Snell [MVP]]

You would need to run a delete query after the append query in order to
delete the records in database1.

 

[Guest]

Thank you oh sultan of Access! Your solution is so much simpler than mine.
Thanks!

 

[Ken Snell [MVP]]

This is one title that I've not been called before.....

You're welcome.

 

[Guest]

Ken, I'm having trouble with the delete query. When I view the results of
the query I see the records I want to delete but when I run it I get an error
message that states I need to specify the table containing the records I want
to delete. Here is the SQL on the delete query.

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));

I first started with a select query named qFreeToGrow which queried the
"Free" field in the S table and returned 19 records.

Then I created a second query named qFreeToGrow_Silv_Records that takes the
values returned in the "Type" field of the first query and searches for all
records in the S table with that "Type". It returns the 137 records I want
to export and then delete.

The delete query is based on an inner join between the second query
(qFreeToGrow_Silv_Records) and the S table.

--
Lorraine

This is one title that I've not been called before.....

You're welcome.

 

[Ken Snell [MVP]]

Delete queries often can be picky when you use a join between tables.

Try this variation for your SQL statement - it might work:

DELETE DISTICNT S.[Silv Primary Key], S.[Completion Date of Exam or
Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));


If that doesn't work, then this revised query should do the trick (note: not
tested, obviously):

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S
WHERE S.[S Primary Key] IN
(SELECT [qFreeToGrow_Silv_Records].[S Primary Key]
FROM [qFreeToGrow_Silv_Records]
WHERE [qFreeToGrow_Silv_Records].[Type] = S.Type);


--

Ken Snell
<MS ACCESS MVP>

Ken, I'm having trouble with the delete query. When I view the results of
the query I see the records I want to delete but when I run it I get an
error
message that states I need to specify the table containing the records I
want
to delete. Here is the SQL on the delete query.

DELETE S.[Silv Primary Key], S.[Completion Date of Exam or Activity],
S.[Target Date], S.Type, S.[Silv Issue or Forest Health], S.[Planned
Activity], S.Comments, S.Completed, S.CompDate, S.FreeToGrow
FROM S INNER JOIN qFreeToGrow_Silv_Records ON S.Type =
qFreeToGrow_Silv_Records.Type
WHERE (((S.[S Primary Key])=[qFreeToGrow_Silv_Records].[S Primary Key]));

I first started with a select query named qFreeToGrow which queried the
"Free" field in the S table and returned 19 records.

Then I created a second query named qFreeToGrow_Silv_Records that takes
the
values returned in the "Type" field of the first query and searches for
all
records in the S table with that "Type". It returns the 137 records I
want
to export and then delete.

The delete query is based on an inner join between the second query
(qFreeToGrow_Silv_Records) and the S table.