Can not update columns - on certain conditions 
Author Message
 Can not update columns - on certain conditions

TPR table has about 70 columns, two of which are SQLServer "Text"
datatypes.
The TPR_Test table just four columns, two of which are "Text"
datatypes.

1) I can add a new record and update the columns by opening the TPR
table directly in MSAccess 2000 (open table).

2) I can add a new row and update the columns from the MSAccess VB
script (using .AddNew method) on the smaller TPR_Test table.

3) I can not update the columns on a row that was created from the
MSAccess VB script (using .AddNew method) on the larger TPR table.

4) Even after I go completely out of MSAccess and even shutdown and
reboot my WindowsNT workstation, I can not update these columns on
rows created in step (3).

All the above actions were done through the one and same ODBC
configured connection to this database.
So It does not appear to be ODBC related.

Thanks for anybody's assistance here.
-Martin



Wed, 09 Jun 2004 02:13:40 GMT  
 Can not update columns - on certain conditions
Yes, there is a primary key on this table.

BUT I found what was causing the database message, the system message,
stating that another user has made changes, and prevent your changes
to be update and committed to the SQLServer database table.

Read on:
*****************************
Table with Yes/No (in MSaccess) , Bit datatypes (in SQLServer):

Brief Explanation:
In the MSAccess database, mine is called C2K, the TPR table and other
C2K tables contain several columns with that can cause some problems
with creating and editing table records. This was initially discovered
with testing against the TPR table that was converted over to the
SQLServer database.
Several columns have the datatype of “Yes/No” (as defined
in MSAccess), or “Bit” (as defined in SQLServer).
When creating or adding a new record through VB Script, if these
fields are not set to a known default value of, say, zero (0) then the
next time you try to update any other column on this record you will
get the system message, stating that another user has made changes,
and prevent your changes to be updated and committed to the SQLServer
database table.

Conversion Notes and Explanation:
1)The solution to this problem is implemented in the following manner.
2)Identify all columns with the datatype of “Yes/No”
(MSAccess) or “Bit” (SQLServer).
3)When creating or adding a new table record make sure all columns
that make up the primary key are populated (can not be
“Null”).
4)Also make sure all columns with the datatype of “Yes/No”
(MSAccess) or “Bit” (SQLServer) are set to a know value,
for example, zero (0).
5)Setting the value to zero (0) can be done programmatically, i.e., in
VBScript, or in the table definition in the SQLServer database
back-end.
6)Setting the table definition in the SQLServer database back-end:
7)Set the attributes in the database table, by first opening up the
table definition, then open and edit the column’s attributes for
that specific column.
8)Set the column’s default attribute to value of zero (0).

-Martin

*************************

Quote:

> TPR table has about 70 columns, two of which are SQLServer "Text"
> datatypes.
> The TPR_Test table just four columns, two of which are "Text"
> datatypes.

> 1) I can add a new record and update the columns by opening the TPR
> table directly in MSAccess 2000 (open table).

> 2) I can add a new row and update the columns from the MSAccess VB
> script (using .AddNew method) on the smaller TPR_Test table.

> 3) I can not update the columns on a row that was created from the
> MSAccess VB script (using .AddNew method) on the larger TPR table.

> 4) Even after I go completely out of MSAccess and even shutdown and
> reboot my WindowsNT workstation, I can not update these columns on
> rows created in step (3).

> All the above actions were done through the one and same ODBC
> configured connection to this database.
> So It does not appear to be ODBC related.

> Thanks for anybody's assistance here.
> -Martin



Wed, 16 Jun 2004 01:14:44 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Ado

2. Query.RecordCount with Oracle?

3. Breaking a field using textstream at certain column number (73)

4. Make field accessible on certain condition

5. Getting a UserForm to open if certain condition is met

6. Couting records under certain conditions

7. Try again: Condition for URL not found

8. Condition for URL not found

9. CAB files not updating on client side when VBscript is updated

10. SQL Update not Updating

11. Info on DBLIB.TPW functions in BP7.0 with Objects

12. How can BP7 high resolution graphics with ATI Mach32 video card ?

 

 
Powered by phpBB® Forum Software