How to update table based on parameters using SQL

[Ed Dror]

Hi there

This code is from Microsoft Library

Dim dbs As Database
Dim qdf As QueryDef

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 " _
& "WHERE ReportsTo = 2;"

dbs.Close

Now I have

1 Form with 1 Text box and 1 botton

Everything is fine but what if this is a btnUpdate_Click() event on a form
And the "SetReportTo = 5" is a txtBox instead

Ho do you insert into sql string parameter from text box in a Form?

Thanks,
Ed Dror

 

[Marshall Barton]

This code is from Microsoft Library

Dim dbs As Database
Dim qdf As QueryDef

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")

dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = 5 " _
& "WHERE ReportsTo = 2;"

dbs.Close

Now I have

1 Form with 1 Text box and 1 botton

Everything is fine but what if this is a btnUpdate_Click() event on a form
And the "SetReportTo = 5" is a txtBox instead

If the field in the table is a numeric type:

dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = " & Me.txtNewValue _
& " WHERE ReportsTo = " & Me.txtOldValue

If it is type Text:

dbs.Execute "UPDATE Employees " _
& "SET ReportsTo = """ & Me.txtNewValue & """ " _
& " WHERE ReportsTo = """ & Me.txtOldValue & """ "

 

[Klatuu]

& "SET ReportsTo = " & Me.MyTextBoxName _

 

[Ed Dror]

Marsh,

I'm getting runtime error 3734
The database has been placed in a state by user Admin on machine
"my computer name" that prevent it from being opened or locked

Private Sub btnUpdate_Click()
Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase("C:\Documents and Settings\Ed
Dror\Desktop\WeeklySalary_GRN_PGR.mdb")

dbs.Execute "UPDATE PaychexData " _
& "SET SQFT = """ & Me.txtSQFT & """ " _
& " WHERE DIV = """ & Me.txtDIV & """ "
dbs.Close
End Sub

Is there any way to set the database as current database and prevent this
from happening?

Like :

Dim strSQL As string

strSQL = "UPDATE PaychexData " _
& "SET SQFT = """ & Me.txtSQFT & """ " _
& " WHERE DIV = """ & Me.txtDIV & """ "
DoCmd.RunSQL (strSQL)

But I'm getting 0 update!

Thanks,
Ed Dror

 

[Marshall Barton]

I'm getting runtime error 3734
The database has been placed in a state by user Admin on machine
"my computer name" that prevent it from being opened or locked

Private Sub btnUpdate_Click()
Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase("C:\Documents and Settings\Ed
Dror\Desktop\WeeklySalary_GRN_PGR.mdb")

dbs.Execute "UPDATE PaychexData " _
& "SET SQFT = """ & Me.txtSQFT & """ " _
& " WHERE DIV = """ & Me.txtDIV & """ "
dbs.Close
End Sub

Is there any way to set the database as current database and prevent this
from happening?

Like :

Dim strSQL As string

strSQL = "UPDATE PaychexData " _
& "SET SQFT = """ & Me.txtSQFT & """ " _
& " WHERE DIV = """ & Me.txtDIV & """ "
DoCmd.RunSQL (strSQL)

UsingRunSQL will not get close to doing what you want.

Sorry, I have no idea what that means, probably the database
is already open from somewhere else (in your program???)

 

[Peter Yang[MSFT]]

Hello Ed,

If you try to use the code to open a new database, can you reproduce the
problem? If the issue only occurs with specific database, you may want to
copy the database to a different folder and open it from there to test.

Please let's know if you have any update. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ed Dror]

Peter,

It works, i'm using Access 2007 and I needed to permit Macro

Thanks,
Ed Dror