SQL Update question

U.N.C.L.E. · December 9th, 2004, 08:03 PM

Using Access 2003 as an interface to large SQL 2000 table containing million+ records.

I've added a new column that is a bit with a default value of 1. Unfortunately, SQL Server initializes the value of my new field to an unknown state for the records that already exist in the table. For new records, the field is initialized as 1 (true).

What I require is to set all values for the field to 1. I tried executing the following SQL statement in a stored procedure:

"UPDATE tblTable1 SET myVar = DEFAULT"

Unfortunately, when I run the update query, Access immediately reports that the procedure executed successfully without returning any records.

I then tried to executing the statement using VBA code:

Currentproject.Connection.Execute "UPDATE tblTable1 SET myVar = DEFAULT"

But Access keeps reporting a connection timeout. Wondering if anyone can explain if the UPDATE statement is acted upon immediately by SQL server. And/or another way to set all the values of my new field...

Thanks.

jemacc · December 9th, 2004, 08:47 PM

Code:

update tbltable1
set yourcolumn=1

Jaime E. Maccou

To create a default use

Code:

CREATE DEFAULT yourcolumn AS '1'