[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11394
  • Last Modified:

Run an update query in VB access

I want to run an update query after a command is clicked on a form.  This update query will update all dates to match another date field.  What code would I put in to run an update query after the command button is clicked?  Thank you.
0
keefek01
Asked:
keefek01
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi keefek01,

Something like this (uses ADO)

Private Sub YourCommandButton_Click()

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

Dim sSQL as string

sSQL = "UPDATE YourTableName SET SomeFieldName = AnotherDateFieldName"

cn.Execute sSQL, cn

End Sub

Hope this helps.
-Jim
0
 
peter57rCommented:
Hi keefek01,

something like:

Dim strSQL
Dim mydatevariable as date

mydatevariable = ???????whatever

strsql = "Update mytablename set mydatefield = #" & mydatevariable & #"
currentdb.execute strsql, dbfailonerror


Pete
0
 
keefek01Author Commented:
All i want to do is make sure a query runs through when i click the command.  The query name is "qryupdateclosedcancelled".  Aren't there any steps of code are more simplex.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'query runs through'.

If all you want to do is run the query, then you have a few options...

DoCmd.RunSQL "qryupdateclosedcancelled"

or

either mine or Peter's comments that execute a query off of the command object.

Most programmers will use the latter, as it gives you a lot more options, but if all you want is a quick-and-dirty, you can use DoCmd...

0
 
keefek01Author Commented:
When i run
DoCmd.RunSQL "qryupdateclosedcancelled"  it says "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

What can i do with this?

0
 
msrobertsCommented:
try
docmd.OpenQuery "qryupdateclosedcancelled"

run sql is for executing an sql string
openQuery is for executing a query from the database.
0
 
Simon BallCommented:
where is the new date field that you want the update query to use as the new value?

if its on the form you can use a query, and force the update value to be [forms]![yourformname]![the field with the new date in].value...

i.e.[forms]![form1]![txtdate].value in the update query...
0
 
keefek01Author Commented:
here is the code that i am trying to put into VB in order to run my update query.    DoCmd.OpenQuery qryclosedcancelled, acViewNormal, acEdit

I get an error that says "the action or method requires a query name argument"   How do i get this code to run correctly?
0
 
msrobertsCommented:
DoCmd.OpenQuery "qryclosedcancelled", acViewNormal, acEdit

querynames are strings.  if there aren't quotes then it thinks it's a variable.
0
 
keefek01Author Commented:
When i run this in the form it lets me update it but the warning signs appear stating that i am about to run an update query that will modify data in my table.  Then the next one says i am about to update "" number of rows.  Is there any way i can go through this without the warning signs appearing?  I want it to automatically update with out having to hit yes everytime it runs through.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> Is there any way i can go through this without the warning signs appearing?  

In Access VBA you use...

DoCmd.SetWarnings False
'All of your code goes here
DoCmd.SetWarnings True
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now