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.

.

 

[AccessVandal via AccessMonster.com]

SQL is a reserved word in Access under DAO. Might not be an issue with ADO
but I'm not sure about 2007 against 2003.

You might want to rename it to something like "strSQL" instead.

How about using Execute instead?

cn.Execute strSQL,RA

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.

 

[AccessVandal via AccessMonster.com]

PS. If [Date Last Change] is really a date, you need to replace the single
quote to something like...

[Date Last Change] = #" & Now() & "# WHERE [User .....

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

rst.Open Sql, cn
Set rst = Nothing

 

[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.

.

 

[AccessVandal via AccessMonster.com]

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.

 

[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.



.

 

[AccessVandal via AccessMonster.com]

Where is "TempVars!CurrentUser" declared? I don't see the RecordSet
"TempVars".

Include a line just before the strSQL.

strSql = "UPDATE [User Identification] SET [Password] = '" & ([New

Password]) & "', [Date Last Change] = #" & Now() & "# WHERE [User ID]= '" &
TempVars!CurrentUser & "';"

Debug.Print strSQL 'add a line here to check the output
cn.Execute strSql
cn.close
set cn = Nothing

And see the result of your string output from the immediate window (ctl G)
and copy and paste it into your browser for us to see.

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.

 

[AccessVandal via AccessMonster.com]

Typos remove the ">". and watch for word wrap in your browser.

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

Debug.Print strSQL 'add a line here to check the output
cn.Execute strSql
cn.close
set cn = Nothing

 

[AccessVandal via AccessMonster.com]

Note: I did not see the your parenthesis. Remove it and what is "[New
Password]", is this a control on a form? If it is, change it to ....Me![New
Password]

Anthony Ching wrote:

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