sql question

[savok]

I have a table that contains a SSN field. I need to create a new field in the same table called SSN2 and copy all the data from SSN to SSN2.

how would i do that?

thanks

 

[HebieBug]

Depends on how you would like to do it.
The easiest is to create a Data enviroment connect it to the Database set acommand to he table and then
Private Sub Command1_Click()
DataEnvironment1.rsCommand1.Open
With DataEnvironment1.rsCommand1
Do Until DataEnvironment1.rsCommand1.EOF
if not isnull(!SSN) then
!SSN2 = !SSN
.Update
.MoveNext
Loop
End With
DataEnvironment1.rsCommand1.Close
MsgBox "Update complete"
End Sub

The second is to do it through ADO
Dim aMainrecord As New ADODB.Recordset
Dim strSql As String
Dim aMainrecConn As New ADODB.Connection
strSql = "Select * from TABLENAME"
aMainrecConn.Open "Provider=SQLOLEDB; Data Source=SERVERNAME; " & "Initial Catalog=DATABASENAME; User Id=**; Password=**"
aMainrecord.CursorLocation = adUseClient
aMainrecord.Open strSql, aMainrecConn, adOpenStatic, adLockUnspecified
While Not aMainrecord.EOF
If Not IsNull(aMainrecord("SSN&quot) Then
aMainrecord("SSN2&quot = aMainrecord("SSN&quot
aMainrecord.Update
aMainrecord.MoveNext
Wend
aMainrecConn.Close
Set aMainrecConn = Nothing
Set aMainrecord = Nothing
End Sub

 

[moki]

savok,

Perform an Update SQL process:

Update tblMytable_Name SET SSN2 = [SSN]

You do have to define SSN2 in your table prior to the SQL above.

Hope this helps.

 

[savok]

thank you!