How to obtain server's datetime through ODBC/MySQL?

[Lierduh]

I need to retrieve the MySQL's current time through MyODBC. I am not sure how to achieve this in VBA.

I can do this by creating a pass-through query as:

SELECT CURRENT_TIMESTAMP;

The problem is I need to store the DSN information with the query which may not be practical.

Is there a special kind of table in MySQL I can create for returning current date/time and Access can link to this table? I was thinking to create a HEAP type table and after inserting a row, retrieve the timestamp field. This would be too complicated to just get the server time I think.

Please help.

 

[Nouba]

maybe you could create a PT query on the fly with DAO's CreateQueryDef function, set the connection string, make it a PT query, assign the sql text, open a recordset on it, read out the date/time information, close the recordset, destroy the query.

I'm not sure, if the PT query also works with an unnamed (temporary) query.

 

[Lierduh]

That means I would need to hard code the user name or password in the code or store the password etc in a local table. Each user uses a different set of ODBC user/password based on permission.

It is easy to update the linked tables using the Linked Table Manager and not easy to maintain DSN in other part of the frontend, be it in the code, query etc. May be I need to create a unique user with no permission to any DB and with no password for this purpose?

 

[Tim K.]

If you know the server name you can use Net Time to help.

If you want to set the current machine time to the server time, type this in command promt:

Net Time \\computer_name /set /yes

Learn more about the parameters, type Net Time /? in the command promt.

 

[Lierduh]

Thanks Tim for the info. It will be handy to know this.

I have a routine to check user's PC's time setting against the servers as well as the date format when a user is logging into the Access front end. However if a user accidentally changes his/her time setting after login, then the database will end up with wrong data entry. It is too convenient to use Now() and Date in Access, but it may also cause big problem if the client's time/date is wrong. For a certain part of the entry, time drift may become critical as well around midnight. (Not that anyone is using the DB at that time, but I still try to write something which has this in consideration.)