append queries

[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.

 

[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.

 

[NAS]

The make table didnt work either and I didnt see any errors when I switched
programs.

 

[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?

 

[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.

 

[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.

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?

 

[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.

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.

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?