Can't Update Database

[Anthony Ching]

I am using Access 2007. I have the following coding for updating the password
field of the user table:

Dim rst As New ADODB.Recordset, Sql As String
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Sql = "UPDATE [User Identification] SET [Password] = '" &
TempVars!NewPassword & "', [Date Last Change] = '" & Now() & "' WHERE [User
ID]= '" & TempVars!CurrentUser & "';"

rst.Open Sql, cn
Set rst = Nothing

The data has been split and reside on a remote server. I have generated the
run-time codes using Access Developer and have Access Run-time on the
machines. However, while this works fine on my development machine (which has
Office 2007), it doesn't work on other machines (with Office 2003) who are
also connected to the server. The error number is "-2147467259" and the error
description is "Operation must be an updateable query". Can someone help.

 

[Tom van Stiphout]

On Thu, 7 Jan 2010 18:33:01 -0800, Anthony Ching

There are a few unusual things about this update statement, but I'm
not sure they can explain that behavior.
I am assuming [User Identification] is a table, not a query?
Does it have a Primary Key?
Now() should be wrapped in #-signs rather than single-quotes.
Do you really have an alphanumeric UserID?
Also when the error occurs inspect the values to see if they are what
you expected.

-Tom.
Microsoft Access MVP

 

[Anthony Ching]

[User Idntification] is a table with primary key [User ID] which is
alphanumeric.
--
Anthony

On Thu, 7 Jan 2010 18:33:01 -0800, Anthony Ching

There are a few unusual things about this update statement, but I'm
not sure they can explain that behavior.
I am assuming [User Identification] is a table, not a query?
Does it have a Primary Key?
Now() should be wrapped in #-signs rather than single-quotes.
Do you really have an alphanumeric UserID?
Also when the error occurs inspect the values to see if they are what
you expected.

-Tom.
Microsoft Access MVP

I am using Access 2007. I have the following coding for updating the password
field of the user table:

Dim rst As New ADODB.Recordset, Sql As String
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Sql = "UPDATE [User Identification] SET [Password] = '" &
TempVars!NewPassword & "', [Date Last Change] = '" & Now() & "' WHERE [User
ID]= '" & TempVars!CurrentUser & "';"

rst.Open Sql, cn
Set rst = Nothing

The data has been split and reside on a remote server. I have generated the
run-time codes using Access Developer and have Access Run-time on the
machines. However, while this works fine on my development machine (which has
Office 2007), it doesn't work on other machines (with Office 2003) who are
also connected to the server. The error number is "-2147467259" and the error
description is "Operation must be an updateable query". Can someone help.

.

 

[Anthony Ching]

I changed the statement to
Sql = "UPDATE [User Identification] SET [Password] = '" & ([New Password]) &
"', [Date Last Change] = #" & Now() & "# WHERE [User ID]= '" &
TempVars!CurrentUser & "';"

I made a runtime version using Access Developer Extension and have included
Access Runtime with it. It is working fine on my own machine. However, when
this is set up on another machine the following error came up:

-2147467259 Operation must be an updateable query

The data of this program has been split and reside on a server.


--
Anthony

On Thu, 7 Jan 2010 18:33:01 -0800, Anthony Ching

There are a few unusual things about this update statement, but I'm
not sure they can explain that behavior.
I am assuming [User Identification] is a table, not a query?
Does it have a Primary Key?
Now() should be wrapped in #-signs rather than single-quotes.
Do you really have an alphanumeric UserID?
Also when the error occurs inspect the values to see if they are what
you expected.

-Tom.
Microsoft Access MVP

I am using Access 2007. I have the following coding for updating the password
field of the user table:

Dim rst As New ADODB.Recordset, Sql As String
Dim cn As ADODB.Connection

Set cn = CurrentProject.Connection
Sql = "UPDATE [User Identification] SET [Password] = '" &
TempVars!NewPassword & "', [Date Last Change] = '" & Now() & "' WHERE [User
ID]= '" & TempVars!CurrentUser & "';"

rst.Open Sql, cn
Set rst = Nothing

The data has been split and reside on a remote server. I have generated the
run-time codes using Access Developer and have Access Run-time on the
machines. However, while this works fine on my development machine (which has
Office 2007), it doesn't work on other machines (with Office 2003) who are
also connected to the server. The error number is "-2147467259" and the error
description is "Operation must be an updateable query". Can someone help.

.

 

[Anthony Ching]

I changed the coding to the following:

Dim strSql As String
Dim cn As ADODB.Connection

On Error GoTo Change_Err

Set cn = CurrentProject.Connection
strSql = "UPDATE [User Identification] SET [Password] = '" & ([New
Password]) & "', [Date Last Change] = #" & Now() & "# WHERE [User ID]= '" &
TempVars!CurrentUser & "';"

cn.Execute strSql

However, the same message came out.

--
Anthony

Did my suggestion work?

Your're openning a recordset with the command, you should use Execute.

I changed the statement to
Sql = "UPDATE [User Identification] SET [Password] = '" & ([New Password]) &
"', [Date Last Change] = #" & Now() & "# WHERE [User ID]= '" &
TempVars!CurrentUser & "';"

I made a runtime version using Access Developer Extension and have included
Access Runtime with it. It is working fine on my own machine. However, when
this is set up on another machine the following error came up:

-2147467259 Operation must be an updateable query

The data of this program has been split and reside on a server.

--
Please Rate the posting if helps you.



.