Forum Moderators: open
Situation
A client currently uses an MS Access db for their website. They make changes in-house and upload the new db automatically every night:
Office Access db ->UPLOAD-> to Server.
We're doing a redesign for them that includes moving to a mySQL db on the server:
Office Access db ->EXPORT/IMPORT -> mySQL -> UPLOAD-> to Server.
Question
Is there a good/easy/proper etc. way to accomplish what they were doing before but including getting the data into mySQL?
The site will be on a LAMP setup
they export csv from access
you have an upload script that reads and inserts into db
Is that what you mean?
Seems like a good way to me
I guess so. Things is, I have no idea how to do it. When I googled I ran into software solutions, from what you are telling me I can cook up a script to do this? Maybe I should go and hit the mySQL functions over at php.net...
I assume manual export from access which leaves you with a csv file
Your Script
start with a standard upload style form
they click browse, select file, click upload
You upload and move to a dir you can work with
You can use the Basics of extracting data from CSV files [webmasterworld.com]
For Step4 you would reorganize the fields, if needed, into the order you like for your db
Step5 - instead of writing to file you would build and execute your mysql INSERT query
Step7 - close connection instead of file pointers
fairly straight forward when you boil it down
fairly straight forward when you boil it down
Yes, well thank you for that! Sometimes I just don't know what to cook with...
In this case, 'Basics of extracting data from CSV files' was a missing ingredient
Use a VBA script that will output SQL and upload that. I use the one at
[cynergi.net...]
It is free and unsupported. There are some things to wathc out for.
* carefully read the notice about Microsoft DAO 3.6
* You can adjust the max size for a blob/memo field. It's supposed to go up to 64KB, but I get buffer overflows with blob/memo fields beyond about 40kb.
* If you have any system columns (as when you use replication) in your table, you will want to create a Make Table query in Access to get rid of these before exporting.
Tom
I had actually spotted another post where you mentioned this. I tried it and looked out for the erros/problems but it choked somewhere. I'll give it another shot and let you know how it goes.
(how'd you come across this old post?}
It is not free, but for $39 it is not bad at all.
Of course, I have no affiliation with them, I just use the program and am happy with it.
I tried it and looked out for the erros/problems but it choked
How so? How big is the Access DB you're talking about (in MB I mean?). Mine is only 17MB and it takes about 5 minutes for the script to run, so maybe it just needed more time?
(how'd you come across this old post?}
Continuing ed. At least once a week I try to read through a forum that concerns a topic I know nothing about - surprised to be able to contribute here.
Tom
Right now I'm also looking into myODBC [mysql.com] which, from past experience with odbc, should do what you are after in terms of simply updating new data.
