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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.