Need help on update query in Access

[Christine R via AccessMonster.com]

Hi,
I am trying to update a table in access when a user makes changes to the list
of names and press a button.
However, I would like the event in the button to grab the value from the
textbox and run the query below.

DoCmd.RunSql "Update dbo_job_test.wellname where Wellname = " " &

So after this I don't know what to write. If someone has a clue, please help
me. Please provide me with a sample if possible.
Thanks a bunch.
Christine

 

[Van T. Dinh]

Christine

1. Your SQL String doesn't make sense. Normally you need the SET clause to
update values in the Table. The UPDATE SQL should be something like:

UPDATE [YourTable]
SET [Field1] = <somevalue>, [Field2] = <someOthervalue> ...
WHERE <somecriteria>

You need to check the full syntax in the JET Reference section of Access
Help for details.

2. Are you sure this is an Access local Table? This looks like a linked
Table (possibly from an MS-SQL Server database) to me. If this is the case,
it may be more efficient to do the update directly on the server rather than
RunSQL.

3. An example if you want to use RunSQL: If the code is executed in the
context of [YourForm] that has TextBoxes [OldWellName] and [NewWellName] and
you want to update the WellName, you can use something like:

****Untested****
Dim strSQL As String

strSQL = "Update dbo_job_test " & _
" SET WellName = '" & Me.NewWellName & "' " & _
" WHERE Wellname = '" & Me.OldWellName & "'"

Debug.Print strSQL

DoCmd.RunSql strSQL
****Code ends****

The Debug statement prints the result of the String concatenation for you to
check. Comment it out when everything is fine.

HTH
Van T. Dinh
MVP (Access)

 

[Christine R via AccessMonster.com]

Van, many thanks for the answer. As you had noticed that I am fairly new to
this. Yes it is a SQL Server database, however I had a button when clicked
will first update the temporary access table prompting user for a change and
if he/she presses yes then it will go ahead and update the sql server.
If you have any suggestions, please do advise.
Chrstine

Christine

1. Your SQL String doesn't make sense. Normally you need the SET clause to
update values in the Table. The UPDATE SQL should be something like:

UPDATE [YourTable]
SET [Field1] = <somevalue>, [Field2] = <someOthervalue> ...
WHERE <somecriteria>

You need to check the full syntax in the JET Reference section of Access
Help for details.

2. Are you sure this is an Access local Table? This looks like a linked
Table (possibly from an MS-SQL Server database) to me. If this is the case,
it may be more efficient to do the update directly on the server rather than
RunSQL.

3. An example if you want to use RunSQL: If the code is executed in the
context of [YourForm] that has TextBoxes [OldWellName] and [NewWellName] and
you want to update the WellName, you can use something like:

****Untested****
Dim strSQL As String

strSQL = "Update dbo_job_test " & _
" SET WellName = '" & Me.NewWellName & "' " & _
" WHERE Wellname = '" & Me.OldWellName & "'"

Debug.Print strSQL

DoCmd.RunSql strSQL
****Code ends****

The Debug statement prints the result of the String concatenation for you to
check. Comment it out when everything is fine.

HTH
Van T. Dinh
MVP (Access)

Hi,
I am trying to update a table in access when a user makes changes to the list

[quoted text clipped - 8 lines]

Thanks a bunch.
Christine

 

[Christine R via AccessMonster.com]

Also Van,
If I do the updates directly to the sql server, an you provide me a sample
code for that.
Many thanks again.

Christine

1. Your SQL String doesn't make sense. Normally you need the SET clause to
update values in the Table. The UPDATE SQL should be something like:

UPDATE [YourTable]
SET [Field1] = <somevalue>, [Field2] = <someOthervalue> ...
WHERE <somecriteria>

You need to check the full syntax in the JET Reference section of Access
Help for details.

2. Are you sure this is an Access local Table? This looks like a linked
Table (possibly from an MS-SQL Server database) to me. If this is the case,
it may be more efficient to do the update directly on the server rather than
RunSQL.

3. An example if you want to use RunSQL: If the code is executed in the
context of [YourForm] that has TextBoxes [OldWellName] and [NewWellName] and
you want to update the WellName, you can use something like:

****Untested****
Dim strSQL As String

strSQL = "Update dbo_job_test " & _
" SET WellName = '" & Me.NewWellName & "' " & _
" WHERE Wellname = '" & Me.OldWellName & "'"

Debug.Print strSQL

DoCmd.RunSql strSQL
****Code ends****

The Debug statement prints the result of the String concatenation for you to
check. Comment it out when everything is fine.

HTH
Van T. Dinh
MVP (Access)

Hi,
I am trying to update a table in access when a user makes changes to the list

[quoted text clipped - 8 lines]

Thanks a bunch.
Christine