| Cannot update |
| Author |
Message |
|
|
| Posted : 2008-05-27 02:16:30 |
I use ODBC primarily to make mass changes to a customized field we use for rates. We are running QB 2008 canadian version on our server. I always use an MS Access update query ie:UPDATE Customer SET Customer.CustomFieldRateVehicleCharge = "90/49"
WHERE (((Customer.CustomFieldRateVehicleCharge)="90/45"));
This has worked in the past but is nor working now.
I receiving the following message: ODBC - update on a linked table 'Customer' failed. QODBC 10080 - Optimizer is turned. I can run the query to a 2nd QB database we (different company) but cannot perform the updte on this database.
Bruce
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-27 07:40:13 |
Look at the bottom of the QODBC Message and SDK Log in the QODBC Setup Screen Messages Tab for the true reason for the error.
|
|
|
|
|
|
| Posted : 2008-05-28 00:07:47 |
The ODBC log is empty...I cleared it before trying the update query. The SDK log shoes the following:
20080526.114044 I 1860 SpecVersion Current version of qbXML in use: 6.0
20080526.114044 I 1860 QBSDKMsgSetHandler QUERY: Customer Job
20080526.114045 I 1860 QBSDKMsgSetHandler Request 1 completed successfully.
20080526.114045 I 1860 MsgSetHandler Finished.
20080526.114045 I 1860 QBSDKProcessRequest Application named 'FLEXquarters QODBC' finishing requests (process 3744), ret = 0.
20080526.120421 I 3744 RequestProcessor Connection closed by app named 'FLEXquarters QODBC'
20080526.120421 I 3744 RequestProcessor ========== Ended Connection ==========
Bruce
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-05-28 07:39:02 |
I didn't spot it before, but try just:
UPDATE Customer SET CustomFieldRateVehicleCharge = '90/49'
WHERE (((CustomFieldRateVehicleCharge)='90/45'));
as you can't use the table name within the SET and there should only be single quotes around values. |
|
|
|
|
|
| Posted : 2008-05-31 00:27:38 |
| The issue is not with the SQL syntax. This is built by the MS Access GUI and works perfectly with my 2nd QB company database for the exact same update query. If I display the table in Access and manually chnage the field I receive the same error as the Update query. |
|
|
|
|
|
| Posted : 2008-06-11 02:59:00 |
| Is there any more feedback or suggestions to resolve this problem? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-06-11 10:10:23 |
| Are you sure that the custom field VehicleCharge is set up in the 1st QB company file? |
|
|
|
|
|
| Posted : 2008-06-12 01:26:44 |
Absolutely. Here is the properties for the fiedl in both QB Databases:
\\CALW-SRV01\Users\bgraham\My Documents\Calgary Air.mdb 11-Jun-2008
Table: Customer Page: 30
SourceTable: Customer
CustomFieldRate Text 30
AllowZeroLength: True
Attributes: Variable Length
CollatingOrder: General
DataUpdatable: False
GUID: {guid {A3604DE6-7649-438B-800E-485CAB2CF4B5}}
OrdinalPosition: 93
Required: False
SourceField: CustomFieldRate
SourceTable: Customer
\\CALW-SRV01\Users\bgraham\My Documents\Calgary Air.mdb 11-Jun-2008
Table: Customer Page: 30
SourceTable: Customer
CustomFieldRate Text 30
AllowZeroLength: True
Attributes: Variable Length
CollatingOrder: General
DataUpdatable: False
GUID: {guid {A3604DE6-7649-438B-800E-485CAB2CF4B5}}
OrdinalPosition: 93
Required: False
SourceField: CustomFieldRate
SourceTable: Customer
|
|
|
|
