Can't Update Database

  • Thread starter Thread starter Anthony Ching
  • Start date Start date
A

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.
 
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
 
[User Idntification] is a table with primary key [User ID] which is
alphanumeric.
--
Anthony


Tom van Stiphout said:
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.
Click to expand...
.
Click to expand...
 
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


Tom van Stiphout said:
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.
Click to expand...
.
Click to expand...
 
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


AccessVandal via AccessMonster.com said:
Did my suggestion work?

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

Anthony said:
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.
Click to expand...

--
Please Rate the posting if helps you.



.
Click to expand...
 
Back
Top