Access VBA Programming UTC format

[SierraJuliet]

Any suggestions on VBA code for formatting Access dates and time to UTC (Coordinated Universal Time) format instead of the standard Access format of =Date() or =Now()?

The idea is to transfer the database backend to MySQL, which natively stores by default all dates and times in UTC format and returns to original user format when retrieved.

The issue is =Date() and =Now() uses the database frontend computer date and time settings when creating and modifying records.



I anticipate this being a problem when multiple time zones are involved (i.e. madness when auditing records); therefore, I would like to standardize the date and time setting at the database frontend.

Thanks in advance for any insight!

[ranman256]

you would not change the 'format' of the table field for date. it stays as date, but you not use =Date(),or Now.
you would have another field TimeZone.
and a tTimeZone table to lookup the difference.
the date stored is calculated from the tTimeZone.UTC offset.

[orange]

I have not used MYSQL as a backend to Access for years. I would review the MySQL manual regarding MS Access as a front end and using the ODBC connector. If MySQL is using UTC as a default storage, I would think the ODBC connector might handle the "translation", or you might try a pass through procedure. A little testing will confirm or not.

You may find info here.
Good luck.

[SierraJuliet]

Thanks for the input. I will evaluate everything a bit more and then post the solution I use herein.