I'm trying to insert and/or update to the EmployeeEarning table in QB Enterprise via Access (yuck!) for 220 employees. I keep getting an error '10047' with the message Field not allowed in update.

I've checked the fields that are no-nos and am not using one of them.

Perhaps I'm going about it the wrong way. What I want to do is to insert or update (if already exists) each Payroll item (the 'PayrollInfoEarniingsPayrollItemWageRefFullName' field) for each employee, with the new pay rate going into the PayrollInfoEarningsRate field.

Without pulling out the big guns (aka Visual Studio and the QuickBooks SDK), is there a magic secret handshake that is required to accomplish what I want?

Gotta love those field names!! Reminds me of my childhood..

Regardevous,

Glen

Whoops!!! Spoke too soon.

I've got an Access table with employee wage info, named EmployeeMaster. The table has all of the Payroll.... info listed in the insert statement, plus matching Employee ListID's, and was derived from the QuickBooks Employee table, plus the PayrollItemWage table (to get the payroll ListID).

Trying to test an insert of one record, I get a preview in Access that is the correct number of records (three), and the actual  Append Query in MS Access 2002:

INSERT INTO EmployeeEarning ( ListID, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate )
SELECT EmployeeMaster.ListID, EmployeeMaster.PayrollInfoEarningsPayrollItemWageRefListID, EmployeeMaster.PayrollInfoEarningsRate
FROM EmployeeMaster
WHERE (((EmployeeMaster.EmployeeID)="20628031"));

- or -

INSERT INTO EmployeeEarning ( ListID, IsActive, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate )
SELECT EmployeeMaster.ListID, -1 AS IsActive, EmployeeMaster.PayrollInfoEarningsPayrollItemWageRefListID, EmployeeMaster.PayrollInfoEarningsRate
FROM EmployeeMaster
WHERE (((EmployeeMaster.ListID)="B4E0000-1142441032"));

- or -

INSERT INTO EmployeeEarning ( ListID, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate )
Values ('B4E0000-1142441032','A0000-1142291222',55);

- or -

INSERT INTO EmployeeEarning ( ListID, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate )
Values ("B4E0000-1142441032","A0000-1142291222",55);

(The last two are a test of a single record. The employee is the same in all cases)

Gives this error:

"ODBC - Insert on a linked table EmployeeEarning failed.

[QODBC] Incorrectly built XML from Update Start (#10045) "

Any ideas?

Thanks

Glen

Sorry the supported syntax is:

Create a new Employee and EmployeeEarning Line
INSERT INTO EmployeeEarning (FirstName, MiddleName, LastName, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate, FQSaveToCache) VALUES ('Tom','T.','QODBC', '10000-933272656', 30000.0, 0)

Create a new EmployeeEarning Line for an Existing Employee
INSERT INTO EmployeeEarning (ListID, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate) VALUES ('300000-934380930', '20000-934380928', 40000)

Using the following syntax gives me an error 3100 and message stating that the name of the list element is already in use (the employee is NOT in the EmployeeEarning table list yet, but is in the Employee table):

INSERT INTO EmployeeEarning ( FirstName, MiddleName, LastName, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate, FQSaveToCache )
SELECT EmployeeMaster.FirstName, EmployeeMaster.MiddleName, EmployeeMaster.LastName, EmployeeMaster.PayrollInfoEarningsPayrollItemWageRefListID, EmployeeMaster.PayrollInfoEarningsRate, 0 AS FQSaveToCache
FROM EmployeeMaster
WHERE (((EmployeeMaster.PayrollInfoEarningsPayrollItemWageRefListID)="190001-1146004368") AND ((EmployeeMaster.EmployeeID)="147483"));

-- Where EmployeeMaster.EmployeeID is the same as QuickBooks Employee.AccountNumber (I've tried both AccountNumber and Employee.ListID with same results of course).

===========================================================

Using the second syntax in your previous post as before gives the 'XML' error:

INSERT INTO EmployeeEarning ( ListID, PayrollInfoEarningsPayrollItemWageRefListID, PayrollInfoEarningsRate )
Values('B220000-1142440970','C0000-1142376543',16);

-- Where ListID is the Employee.ListID. Note that several Payroll item codes were tried, ditto employees.

To be sure that I've explained things correctly, we have all employees entered into QuickBooks, but only a few in the EmployeeEarning table. I've tried the syntax with both employees that are in the EE table, and those not yet in. Same results.

System: QB Enterprise 6.0 (not yet upgraded to latest version 6 patch) on MS2003 Server, Client running MS Access 2002, latest download of QODBC, Windows XP Professional.

Thanks

Glen

Hopefully I can get all 7 Payroll items for 122 employees loaded before month end closing!

I've retested the examples that I have given and they work without a problem. The create a new Employee and EmployeeEarning line insert example will fail if the employee is already in the Employee table or a entity already exists with the same name.

OK, thanks for your efforts.

The XML message smells a bit like an intermediate language error; when the data from Access is being converted to a universal (XML) format before sending to QB - but that's just a guess.

Right now I guess I'm stuck, but will continue trying. The data entry clerks are going to have overtime!

If you get any ideas please let me know.

Thanks

Glen

I've got the registered version - thanks.

Glen

Tom

Success!

The problem was (blush) that the ListIDs for Employees were taken from the Development Quickbooks platform and used to try and match the Production platform. The file that we used to view the ListIDs was not the load source file, although it was supposed to be - I should have checked sooner and not taken the word on it.

Our fault - but I wanted to set the record straight in case others come up with the same XML message.

I'm impressed with the speed and helpfulness of your replies! Sorry to have bothered you with such a "Duh!" problem though.

Thanks again

Glen