Append query

foxygrandma

This is a pretty simple question I believe. I am just running into a small problem.

I imported two Excel sheets into Access. They have the same fields and field types and I simply want to combine them into one. Actually just attach the second one to the bottom of the first one. I imagine it's a simple append query to get the job done, but for some reason every time I try it runs for a couple minutes and then says "Ran out of disk space." What do I do?

nspader

Please provide the method in which you are running it right now. It is possible it is in an endless loop. If you can provide the code youa re using, then we can all possibly help you further.

Thanks

Nick

foxygrandma

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 1HSearchResults, 2HSearchResults;

foxygrandma

Quote:

Originally Posted by foxygrandma

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 1HSearchResults, 2HSearchResults;

If I try:

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 2HSearchResults;

Then it throws a different error.

"Microsoft Access can't append all the records in the append query. It didn't add 49,576 record(s) to the table due to key violations. Do you want to add them anyway?"

I am trying to append around 51,000 records.

FishVal

Quote:

Originally Posted by foxygrandma

If I try:

INSERT INTO 1HSearchResults
SELECT [2HSearchResults].*
FROM 2HSearchResults;

Then it throws a different error.

"Microsoft Access can't append all the records in the append query. It didn't add 49,576 record(s) to the table due to key violations. Do you want to add them anyway?"

I am trying to append around 51,000 records.

Hello.

Quote:

Originally Posted by foxygrandma

Expand|Select|Wrap|Line Numbers

 
INSERT INTO 1HSearchResults

SELECT [2HSearchResults].*

FROM 1HSearchResults, 2HSearchResults;

The query above will try to insert cartesian product of both tables - no wonder this could exhaust resources.

The second query looks correct. The reason for the error is that you try to insert records containing values that are breaking table rules (unique indexes, constraints etc.). If you want to add all records, then you should change table rules. If not, then just do nothing - Access will add all valid data omitting the rest.

Regards,
Fish

foxygrandma

I figured it out. Had to remove the primary key by deleting the ID columns. Worked like a charm after that.

Append query

re: Append query

re: Append query

re: Append query

re: Append query

re: Append query

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about