append queries

N

NAS

NONE of the append queries in any of my databases will work! When I try to
run them the progress bar stays at 2 green blocks and thats it. I have let
one of them run overnight thinking I just needed to be patient, but nothing.
When I change them to select queries, they work fine.
 
J

Jerry Whittle

What happens when you make them a Make Table Query? Will it create a new table?

Try running one of the Append queries. When it freezes up, press the Alt +
Tab keys at the same time to go to another program. Then Alt + Tab back to
Access. See if a Warning popup shows. Sometimes these warnings get hidden
behind Access and things just come to a halt until you click something like
OK on the warning.
 
J

Jerry Whittle

Post the SQL for a SELECT query that works and the similar APPEND query.

Do you see any other error messages ever? For example if you delete a record
in a table, do you get a warning message first? Possibly someone has turned
off Set Warnings which will stop a lot of error messages from displaying.

How many records are you trying to append? How many records already in the
table.

Do you have Read, Write, Create, and Delete privileges to both the database
file that you are trying to append to AND the entire folder where the table
is located?

How big is the database file?
 
N

NAS

The tabel currently has 0 records because when we tried to update the
database, all the delete queries run but not the appends. When I run the
select query there are tens of thousands of records (and keep in mind the
query is only pulling records for the last 6 months). Here is the SQL for the
Select Query:

SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

And the Append Query:

INSERT INTO [TVL CLAIM] ( SSN, TYPE_TRANSACTION_CODE, EFFECTIVE_DATE,
ENGLISH_STATEMENT_TEXT, HISTORY_DATA_TEXT, PRESENT_ADMIN_RPT_UNIT_CD )
SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

Upon running the update, or the query by itself, the user is prompted to
enter the current date. I can delete records in the table with no problems
and I havent gotten any other warnings. Where do I check to see if Set
Warnings is on or off? The database file is 1.01G.
 
J

John Spencer

Have you tried Compact and Repair option on the database?

If this is Access 97 or before the database size is limited to 1 gigabyte.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The tabel currently has 0 records because when we tried to update the
database, all the delete queries run but not the appends. When I run the
select query there are tens of thousands of records (and keep in mind the
query is only pulling records for the last 6 months). Here is the SQL for the
Select Query:

SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

And the Append Query:

INSERT INTO [TVL CLAIM] ( SSN, TYPE_TRANSACTION_CODE, EFFECTIVE_DATE,
ENGLISH_STATEMENT_TEXT, HISTORY_DATA_TEXT, PRESENT_ADMIN_RPT_UNIT_CD )
SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

Upon running the update, or the query by itself, the user is prompted to
enter the current date. I can delete records in the table with no problems
and I havent gotten any other warnings. Where do I check to see if Set
Warnings is on or off? The database file is 1.01G.

Jerry Whittle said:
Post the SQL for a SELECT query that works and the similar APPEND query.

Do you see any other error messages ever? For example if you delete a record
in a table, do you get a warning message first? Possibly someone has turned
off Set Warnings which will stop a lot of error messages from displaying.

How many records are you trying to append? How many records already in the
table.

Do you have Read, Write, Create, and Delete privileges to both the database
file that you are trying to append to AND the entire folder where the table
is located?

How big is the database file?
Click to expand...
Click to expand...
 
J

Jerry Whittle

If you are running all these queries by a macro or code, you should see
something about Set Warnings there.

One common mistake is to Set Warnings Off then forget to turn them back on
again. If this happens, the warnings stay off until you close and reopen the
database. If the startup form has the set warning off, then you'll never see
many warnings.

You can clear it out by creating a new macro. If using Access 2007 click on
Show All Actions. Next in the Action column select SetWarnings. In the
Arguments column put Yes. Save the macro and run it. Then try one of the
problem queries.

Also like John said, some of the older versions of Access only go to 1 GB.
Heck the original Version 1 only went to 100 MB file size!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


NAS said:
The tabel currently has 0 records because when we tried to update the
database, all the delete queries run but not the appends. When I run the
select query there are tens of thousands of records (and keep in mind the
query is only pulling records for the last 6 months). Here is the SQL for the
Select Query:

SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

And the Append Query:

INSERT INTO [TVL CLAIM] ( SSN, TYPE_TRANSACTION_CODE, EFFECTIVE_DATE,
ENGLISH_STATEMENT_TEXT, HISTORY_DATA_TEXT, PRESENT_ADMIN_RPT_UNIT_CD )
SELECT ODSE_TRANSACTION_RESEARCH.SSN,
ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE,
ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE,
ODSE_TRANSACTION_RESEARCH.ENGLISH_STATEMENT_TEXT,
ODSE_TRANSACTION_RESEARCH.HISTORY_DATA_TEXT,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD
FROM ODSE_MARINE_COMMAND INNER JOIN ODSE_TRANSACTION_RESEARCH ON
ODSE_MARINE_COMMAND.SSN = ODSE_TRANSACTION_RESEARCH.SSN
WHERE (((ODSE_TRANSACTION_RESEARCH.TYPE_TRANSACTION_CODE)="0520") AND
((ODSE_TRANSACTION_RESEARCH.EFFECTIVE_DATE)=[ENTER DATE]-182) AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124")) OR
(((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124"));

Upon running the update, or the query by itself, the user is prompted to
enter the current date. I can delete records in the table with no problems
and I havent gotten any other warnings. Where do I check to see if Set
Warnings is on or off? The database file is 1.01G.

Jerry Whittle said:
Post the SQL for a SELECT query that works and the similar APPEND query.

Do you see any other error messages ever? For example if you delete a record
in a table, do you get a warning message first? Possibly someone has turned
off Set Warnings which will stop a lot of error messages from displaying.

How many records are you trying to append? How many records already in the
table.

Do you have Read, Write, Create, and Delete privileges to both the database
file that you are trying to append to AND the entire folder where the table
is located?

How big is the database file?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

update and append queries 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Outlook emails loading very slowly 0
Setting column width in a table with a macro is not working 0
linked worksheet connection error 5
Word Web App & Keyboard Shortcut 0
Can't save my Office documents (after computer rebuild and reauthorization) 0
Query Delete and append 0

Top