Append query won't insert into linked ( MS-Access ) table....

K

KD-Mudgee

..... but I want it to.


I am supporting a database which consists of two mdb files. The first is a
front-end to the user, and the second is the back-end and solely consists of
the tables being maintained.

I have a very simple little table called "Extras" - the primary Key [Extra
ID] has an autonumber datatype. None of the other fields are indexed or have
any validation rules (other than those constraints enforced by the choice of
datatype). viz:

Field DataType
--------------------------
[Extra ID] AutoNumber
[Invoice Number] Number
[Extra Items] Text
Quantity Number
[Cost of Item] Currency
GST Currency


When I run the following append query :
INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST
)
SELECT
TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST
FROM TimeCardSummary_0;

the following error message appears:

LabManager set 0 field(s) to Null due to type conversion failure,
and it didn't add 3 record(s) to the table due to
key violations, 0 record(s) due to lock violations, and 0 record(s)
due to validate rule violations.
Do you want to run the action query anyway?

(Regardles of what options are chosen, no records are inserted).



If, I import the Extras table into the front-end mdb the query works when
run on the same test data. (This cannot be a permanent solution as the
back-end is to be housed on a network, while the front end has been
distributed to user PCs).



I have tried modifying the query to each of the following, with the same
outcome:


INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST,
[Extra ID]
)
SELECT
TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST,
0 AS [extra ID]
FROM TimeCardSummary_0;


INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST,
[Extra ID]
)
SELECT TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST,
null AS [extra ID]
FROM TimeCardSummary_0;




Is there a AutoNumber place-holder that I can use for this type of query?



My PC is running Office XP on Windows ME.
The same behaviour has been produced on a PC running Office XP sp1 on
Windows XP sp1.


Thanks for any suggestions.


KD.
 
K

KD-Mudgee

Thanks Duane.

I had an error in the formula calculating the invoice number.
Couldn't see it for looking.



Duane Hookom said:
Check your relationships (display all) and see if there are any constraints.

--
Duane Hookom
MS Access MVP


KD-Mudgee said:
.... but I want it to.


I am supporting a database which consists of two mdb files. The first is
a
front-end to the user, and the second is the back-end and solely consists
of
the tables being maintained.

I have a very simple little table called "Extras" - the primary Key [Extra
ID] has an autonumber datatype. None of the other fields are indexed or
have
any validation rules (other than those constraints enforced by the choice
of
datatype). viz:

Field DataType
--------------------------
[Extra ID] AutoNumber
[Invoice Number] Number
[Extra Items] Text
Quantity Number
[Cost of Item] Currency
GST Currency


When I run the following append query :
INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST
)
SELECT
TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST
FROM TimeCardSummary_0;

the following error message appears:

LabManager set 0 field(s) to Null due to type conversion failure,
and it didn't add 3 record(s) to the table due to
key violations, 0 record(s) due to lock violations, and 0 record(s)
due to validate rule violations.
Do you want to run the action query anyway?

(Regardles of what options are chosen, no records are inserted).



If, I import the Extras table into the front-end mdb the query works when
run on the same test data. (This cannot be a permanent solution as the
back-end is to be housed on a network, while the front end has been
distributed to user PCs).



I have tried modifying the query to each of the following, with the same
outcome:


INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST,
[Extra ID]
)
SELECT
TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST,
0 AS [extra ID]
FROM TimeCardSummary_0;


INSERT INTO Extra (
[Invoice Number],
[Extra Items],
Quantity,
[Cost of Item],
GST,
[Extra ID]
)
SELECT TimeCardSummary_0.[Invoice Number],
TimeCardSummary_0.Activity,
TimeCardSummary_0.SumOfUnits,
TimeCardSummary_0.SumOfCharge,
TimeCardSummary_0.SumOfChargeGST,
null AS [extra ID]
FROM TimeCardSummary_0;




Is there a AutoNumber place-holder that I can use for this type of query?



My PC is running Office XP on Windows ME.
The same behaviour has been produced on a PC running Office XP sp1 on
Windows XP sp1.


Thanks for any suggestions.


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

Cannot determine casue of overflow error 1
Access Append Query Failure 1
Update query with autonumber field 4
is this possible in excel 2007 3
Figuring out what field is causing key violation on append 6
Append Query - key violation - no duplicates, records exist in par 4
Linked Tables Append Query(ies) 4
How to write this inter-table update/append query? 1

Top