How run update query on database startup

I have an update query that I want to run on database startup but I don't want the update message to appear.  I've tried creating an AutoExec macro with the query in it but can't avoid the message.

I may be better with VBA code running on startup so the setwarnings can be false but I don't know how to write the code.

The update query SQL is:

UPDATE tblCustomers SET tblCustomers.SubscriptionExpired = True, tblCustomers.CustomerStatus = 2
WHERE (((tblCustomers.CurrentExpirationDate)<Now()));
SteveL13Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steve,

  You can set warnings false in a macro as well (DoCmd), but either way, that's not a hot idea because it also masks any errors you might encounter.

 Best way is to run it in code and do this:

Dim strSQL as string

strSQL = "UPDATE tblCustomers SET tblCustomers.SubscriptionExpired = True, tblCustomers.CustomerStatus = 2
WHERE tblCustomers.CurrentExpirationDate)<Now()"

CurrentDB().execute strSQL, dbFailOnError

and then have error trapping in the procedure to catch it.

That way no update messages, but you do get an error trap if something goes wrong.

Jim.
0
 
SteveL13Author Commented:
How would I use this code in an AutoExec macro?  Or would I just have it in the onopen event of my starting form?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<How would I use this code in an AutoExec macro?  Or would I just have it in the onopen event of my starting form? >>

You can do it a couple different ways:

1. Use the RunCode Action in the Autoexec macro (procedure must be a function, not a sub and it doesn't matter if it returns anything).

2. Use a form and as you guessed, place the call to the code (or the code itself) in the OnOpen event.

Jim.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, you might want to take 5 minutes and look at this:

http://www.experts-exchange.com/videos/537/MS-Access-Different-Ways-to-Start-Up-a-Database.html

Not quite what your asking about, but does pertain to it.

Jim.
0
 
SteveL13Author Commented:
Jim,

Very well done video.  I'll hang onto that .

So I have created a public function and here is the code:

Public Function UpdateRecords()

    Dim strSQL As String
        strSQL = "UPDATE tblCustomers SET tblCustomers.SubscriptionExpired = True, tblCustomers.CustomerStatus = 2 WHERE tblCustomers.CurrentExpirationDate)<Now()"
    CurrentDb().Execute strSQL, dbFailOnError
    
End Function

Open in new window


But when the database opens I get a syntax error on the line:

CurrentDb().Execute strSQL, dbFailOnError

I have also attached a screen shot of the AutoExec in case I have that messed up.

Screen shot of autoexec
0
 
SteveL13Author Commented:
And here is the error message...

error message
0
 
SteveL13Author Commented:
I found it.  It was the extra ) in the line.  Thank you very much.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Great.  Glad to hear it's working.  Now just add some error handling to that so it catches any errors (On Error Goto ...)

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.