Why a field was not updated in an SQL 2000 Server Table

[maupiti]

Access 2003 and SQL 2000 Server.

Why the field "Last_Access_Date" was not
updated in table User_Account.

cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' "& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"

''''''''''''''''''''''''''''''''

THe Table User_Account has the following structure

Column Name Data Type Length Allow Nulls
Name nvarchar 50 0
Email_Address nvarchar 50 1
P nvarchar 20 1
Department_Group nvarchar 50 1
Title nvarchar 50 1
Access_Status nvarchar 50 1
Last_Access_Date nvarchar 50 1


The code below does ran without an error but the
field "Last_Access_Date" was not updated in table User_Account.

//////////////////////////////////////////////////////////

Private Sub Command46_Click()
Call User_Login_Using_SQL_String1
End Sub

//////////////////////////////////////////////////////////


Private Sub User_Login_Using_SQL_String1()
Dim cnThisConnect As ADODB.Connection
Dim RecordSet_User_Account As New ADODB.Recordset
Set cnThisConnect = CurrentProject.Connection

Dim cmd As ADODB.Command
Dim Str_SQL As String

Str_SQL = "SELECT * FROM User_Account WHERE User_Account.Name = '" & Forms!Main_Login!User_Name & "'; "

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
'cmd.CommandType = adCmdText

RecordSet_User_Account.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

If IsNull(Me!User_Name) Then
MsgBox "Please enter your user name", vbInformation
Exit Sub
End If

If IsNull(Me!Password) Then
MsgBox "Please enter your password", vbInformation
Exit Sub
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''' RECORDSET CODE BEGINS '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If RecordSet_User_Account.RecordCount = 0 Then
MsgBox "The user account does not exist.", vbExclamation

Else
RecordSet_User_Account.MoveFirst

If RecordSet_User_Account!Name = Me!User_Name Then
If RecordSet_User_Account!P = Me!Password Then

Public_Current_User = RecordSet_User_Account!Name
Public_Access_Status = RecordSet_User_Account!Access_Status

cmd.CommandText = "INSERT INTO Table_User_Log_On ( Name, Date_And_Time_Log_On, Department_Group, Access_Status) Values " _
& "('" & RecordSet_User_Account!Name & "', '" & Now() & "', '" & RecordSet_User_Account!Department_Group & "', '" & RecordSet_User_Account!Access_Status & "')"
cmd.Execute , , adCmdText


cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' " _
& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cmd.Execute , , adAsyncExecute


DoCmd.Close acForm, "Main_Login"
DoCmd.OpenForm "Main_Switch_Board"
End If
End If
End If

RecordSet_User_Account.Close

End Sub

 

[PHV]

Why not simply replace this:
cmd.CommandText = "UPDATE User_Account " _
& "SET Last_Access_Date = '" & Now() & "' " _
& "WHERE Name = '" & Forms!Main_Login!User_Name & "';"
cmd.Execute , , adAsyncExecute
with this ?
RecordSet_User_Account!Last_Access_Date = CStr(Now)
RecordSet_User_Account.Update

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maupiti]

Hi PHV (MIS), and thank you for your help.
I did as you have suggested. I removed the cmd.CommandText
and I use the two line below and it gives me an error.
On the second section I also use .EditMode but it does not work either.

////////////////////////////////////

Cannot update - Database or object is read-only.
Yellow higlight at the first line below

1) RecordSet_User_Account!Last_Access_Date = CStr(Now())
2) RecordSet_User_Account.Update

////////////////////////////////////

Error: Invalid use of property
Yellow higlight at the first line (EditMode)

1) RecordSet_User_Account.EditMode
2) RecordSet_User_Account!Last_Access_Date = CStr(Now())
3) RecordSet_User_Account.Update

 

[PHV]

Seems that the connection you use don't give sufficient credentials for updating the User_Account table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maupiti]

Hi PHV (MIS)

How do I go about to give sufficient credentials for updating the User_Account table. Would it be setting read and write permission in SQL 2000 Server ?

//////////////////////////////////////////////

Does this look right to you ?

Set cnThisConnect = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection

RecordSet_User_Account.Open Str_SQL, cnThisConnect, _
adOpenKeyset, adLockOptimistic, adCmdText

 

[PHV]

Create a linked table to User_Account with the same connection string and see if the linked table is updatable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maupiti]

Hi PHV (MIS). The table User_Account had been linked to an SQL Server using ODBC from the very start of this thread.

 

[SuryaF]

I'm just shooting in the dark here but try removing the adLockOptimistic and see if that works.