Display MySQL time type field values in MS Access?

Joseph_Sliker

Hi;

This is actually not php related, but I was just hoping maybe someone here would have some ideas on this one... (I posted this on the MySQL forum but have no idea how active that is...

Hi;

My Situation:

I have a MySQL (v3.23.45) database with several tables. I have defined some of the field types as TIME. I use MyODBC (v3.51.06) to link to these tables from within MS Access. I don't run the server and the powers that be cannot be persuaded to install a newer version of MySQL, but I could update MyODBC on my machine if that would help.

My specific problem:

The problem I have is that when I view values in the the TIME fields using MS Access, these only display values of 0:00 or (blank/null) even though I know the tables contain the correct values (i.e. when I view queries via .php pages, phpMyAdmin, etc.). I can update the fields via access properly by entering correct values into the cells (i.e. "15:30:00" if I need to update the time to 3:30 PM).

I have tried to modify the Format property to everything available within the Access table design page, but this only seems to change the 0:00 values to 12:00PM or to date formated strings.

How can I see accurate values from MySQL TIME fields when I use MS Access?

Thanks for your help.

AIS4U

Joseph Sliker:

Well, I'm not a programmer and I don't play one at work.

I am also not a MySQL, PHP, or Access expert either.

That being said, one way that might possibly work would be to write a simple little PHP program that would write the information in your MySQL database to a comma separated value (CSV) flat file.

Then I think you could use Access to retrieve the information from the CSV flat file and get it into Access.

This is kind of the long way around, but it might work.

You have probably already tried it, but if not I would do a Google search on something like "MySql to Access" and see what came up. I am sure you could find some freebee conversion software out there some where or at lease something that would give you a 30 day trial.

This probably wasn't of great value, but at least I gave it a shot.

Good Luck

Joseph_Sliker

Thank you very much for your suggestion AIS4U, but I'd really like to just correct whatever configuration variables (of which I must be ignorant) in Access or MyODBC that prevents the correct display of the values. You see, I have to interact with/edit the values in these tables a lot and the extra step you described is logical, but fussy and time consuming.

I've got other options for viewing/editing the contents of my online tables (ie. phpMyAmin, MySQL-Front, etc.) that DO show the time values correctly but I've been working in Access so long, I just find working in that interface to be much more efficient.