|
Ms Access, mysql and my boss
As i just mentioned in response to another thread, I recently ported an internal Access application to web-based PHP/MySql. (you may remember my "Death March" thread a couple months ago).
Anyway, now that it's ported, my boss, and boss only, still wants to use Access for her ad hoc reporting and querying off the Mysql db. Ok, so i can set up an Access->Mysql ODBC connection for her, but she wants an .mdb on the local network, and I don't want her messing with the live copy of data. So I'm exporting from mysql to access. blech.
so here's just some of the problems:
1. Until I can write a script/tool, I'm having to do the export/import manually every afternoon. Aaaaah! Anybody have any automated solutions?
2. When I import (using Get External Data->ODBC) into Access, mysql datetime fields (where the time portion is "00:00:00") are not being converted correctly, so that in the corresponding Access Date/Time field i have some records like "9/23/03" and some like "3/26/04 12:03:32". This has created a problem in querying, as a parameter of "3/26/04" doesn't match the second type.
I feel like this is a lame post, but anyone have any ideas?
josheli
Thursday, April 8, 2004
This is total flamebait, but here goes:
Maybe you could try creating a PHP script that would extract the data from MySQL, convert the dates/times etc. to the proper Access format, and then insert/update the data in the MDB. This PHP script could then be added to a scheduled task (or cron job on Unix) that would be hit when the "timer" went off.
I haven't tried this yet, but a friend and I have discussed this as a possible (albeit crusty) workaround for the lack of stored procedures in MySQL.
Please be gentle if thou considerest me mental.
Clay Whipkey
Thursday, April 8, 2004
I have not used mySQL so I don't know if you can do this - can you set-up a DTS activity to do the export - I have done that in SQL Server - you can export from database to Access tables or Excel files. You can actually put it into Access tables and translate each column. That might solve your date problem. Check it out
KS
Thursday, April 8, 2004
OK, more random advice. You could use something like Pear DB abstraction layer (http://pear.php.net) and do everything twice and do it in "real time". One pear instance pointing to mysql, the other to access. It should not require any additional code, once you get the PEAR stuff set up, just two method calls. This could be done "automatically" depending on how you link your data objects together.
I personally feel your pain on this one. I can't imagine trying to maintain two separate DBs (separate DB platforms no less). The PEAR framework would take away the guess work on this... now you have a datetime problem, but who is to say what else you will find. I would let the abstraction layer do all this work for you.
Good Luck.
Seeker
Thursday, April 8, 2004
Thanks.
Clay, I actually did just that when going from Access to Mysql. set up a php script to extract, munge, reformat and put a bow on top of the data.
i don't know what a DTS is, but i'll look into it.
on mysql.com there is an old access macro that supposedly imports from mysql, but i couldn't get it to work.
looks like i'll have to write _another_ script/tool.
josheli
Thursday, April 8, 2004
Doesn't Access import delimeted files? Why not have a PHP script spit out a pipe delimited file, and then have her import that?
Andrew Burton
Thursday, April 8, 2004
I was understanding the problem to be more with the process being *automated*, not with the process itself. Reall, I think that script would easy as pie to write, its just making it kick off on its own without having to manually initiate the data transfer that exposes the weakness of MySQL. (actually I love mysql and hate Access, but that's irrelevant).
The core issue here is why the hell does your boss insist on using Access? Maybe its a comfort level using MS Office kind of thing.
Clay Whipkey
Thursday, April 8, 2004
AFAIK, DTS is only available on SQL 7.0/2000.
I did something similar once w/ Perl (PHP would work too). I had the database spit out a text file for each table (via Perl script). My script then used a DSN to connected to Access. I dropped and recreated each table and inserted the data from the text files.
Sounds a little strange, but only took a couple of hours to do and worked perfectly.
Lee
Thursday, April 8, 2004
Andrew, that wouldn't fix the problem with the dates, and it just adds an unnecessary step - Access can import data directly from MySql, after all.
I've never worked with MySQL, but what I've done with SQL Server and Access is link the table(s) in question via ODBC, then run an append query to put the new data from the linked table into a native Access table. The append query can use functions to scrub the data (via Format() or CDate() or whatever). It's not terribly fast, but it seems to work ok for the data chunks I get (on the order of hundreds, rather than thousands, of rows per append).
Martha
Thursday, April 8, 2004
As for the automation issue, you could write some code to run the append query whenever the Access db is opened. If your boss refuses to eat the dog food she had you cook for everyone else, she can wait that extra few seconds while the query runs.
Martha
Thursday, April 8, 2004
DTS, as I am familiar with it, is a set of tools bundled with MS SQL server. I would imagine the other big DBMSs have the equivelant, but not MySQL or Access. If you do have access to MS SQL server, DTS would probably do the job handily--you can make connections to diverse data stores, munge data, push it into other diverse data stores, and schedule it all in DTS. Any conditional logic you require can be applied through VBScript. That said, I assume you do not have MS SQL, which gave rise to the use of Access to begin with. I feel for you, as I too have a handful of legacy Access applications under my care that suffer from, well, being Access.
MacSqueeb
Thursday, April 8, 2004
DTS would be overkill. I agree with Martha.
Write a proc in Access to link to the tables and run append queries.
DJ
Thursday, April 8, 2004
Technical solutions to social problems are non-solutions.
Retrain your boss to use the new tool. Explain the best business case why it is not best to be needlessly jiggling the data back and forth because she refuses to learn the new tool.
At the same time, it sounds like she is using Access in a way that the new tool doesn't function. Learn what she is really doing, and duplicate that functionality in the new tool. Make her a part of that development process, and she will have something invested in using the new tool.
Wayne Earl
Thursday, April 8, 2004
This is very simple....
Lookup "Access Link tables"
Here is one such discussion link
http://www.bitmechanic.com/mail-archives/mysql/current/1225.html
Code Monkey
Thursday, April 8, 2004
OP: "I don't want her messing with the live copy of data"
Infinite Monkeys
Thursday, April 8, 2004
i don't want my boss linking into the live data. i'm trying to get my department to set up mysql replication, and she can then link to the slave. but i work for the gubment, so things are slow.
implementing what she likes about Access huh? doesn't that sort of imply reinventing Access for the web, complete with checkbox queries, expression builder, form designer, etc.? but really you're right, and i'm working on the core 5 or so things she does 90% of the time.
don't y'all just love management's "pet projects". it's funny that my boss designed and coded the original Access applicaiton as her personal pet project. it lasted about 9 months before they hired me to port it to her new pet project (web app).
thanks for all the ideas.
josheli
Thursday, April 8, 2004
"Andrew, that wouldn't fix the problem with the dates, and it just adds an unnecessary step - Access can import data directly from MySql, after all."
That would fix the problem with the dates. If the PHP script displayed dates and stuck them in a text file, the dates would then be Text field and not wrongly a imported Date field.
As for automating it, a Perl script and Windows Scheduler would fix that. Use thge Perl module Win32::ODBC to grab the data, delete the original Access table, create a new table in the file, and then INSERT via an SQL command. This solves the date problem. Schedule it to run either at midnight (if the boss leaves the computer on) or at start up (if it gets turned on every morning).
This is basically what I do to syncronize an AS/400 (db2?) database and a MySQL database. Also, it's not adding a redundant step if the direct way doesn't handle dates properly.
Andrew Burton
Thursday, April 8, 2004
I second Martha's suggestion. Give your boss an Access app with an Autoexec macro that links the tables, imports the data, then deletes the linked tables. Bonus points for giving her a secured database so she doesn't have rights to linked tables she creates (but then you'll have to do the connection in code). (=
Sam Livingston-Gray
Thursday, April 8, 2004
Does Access support unix timestamps? If so, you could try replacing the date fields with timestamps and do the formatting in the code.
Eric Debois
Friday, April 9, 2004
"i don't want my boss linking into the live data. "
Sorry, don't have a computer with Access available at the moment, but it strikes me that there may be a property you can set on a linked ODBC table within Access to make it read-only. Wouldn't that solve all the problems?
Herbert Sitz
Friday, April 9, 2004
...or create the connection with a user that only has read privs.
Sam Livingston-Gray
Saturday, April 10, 2004
It is probably easier to copy mysql data to another mysql database than to MSaccess.
You might try having 2 mysql databases, a primary and a mirror, and letting your boss link to the mirror via ODBC.
Jim Crockett
Saturday, April 10, 2004
This is exactly what happens when you deploy a new system without changing the workflow of how you do stuff within an organization.
I would try to replace her reports with new and better ones, so that you give her an incentive to stop this stupidity.
Talk to her, and have her explain exactly what it is she needs in her current reports, and find a way to match those requirements using your current platform.
Patrik
Sunday, April 11, 2004
Okay...I know I'm posting this several months after the fact but I can't resist. Why not just link to the MySQL tables with ODBC and use the Access security to make sure the user can only touch the data in a read only manner? Then create queries against the attached tables that format the dates the way you want and the end user can write queries and reports against the query instead of against the original table.
vonkarl
Wednesday, August 11, 2004
 Recent Topics
Fog Creek Home
| 
