Jump to content

Ms-access To Mysql


btrfld
 Share

Recommended Posts

I have a potential client who is unhappy with their hosting solution, and would like to go with us. They are currently using a MS Access 2000 database.

 

First, is it possible for them to simply put their DB up on TCH servers and access it via MyODBC? If so, does anybody out there have experience and know how to do it?

 

If not (as I expect), how hard or easy is it to convert to a MySQL DB, and then to upload changes? The best of all worlds would be for the person currently managing the data not to have to learn a whole new interface.

 

Thanks in advance for any help you can give me.

Link to comment
Share on other sites

Migrating data from Access to MySQL is fairly simple. I have exported several db's from Access as CSV data, then used PHPMyAdmin (accessible from cpanel) to import the data to a MySQL db.

 

Is the database populated with static data, or will the person managing the data be making changes on a regular basis? You could write a custom PHP interface for data modification & entry, but it wouldn't be the same as using Access.

 

On machines at work (not TCH servers) I have migrated several db's from Access to MySQL, and then written web interfaces in PHP for them. If you end up going that route, I'm willing to help any way I can. I'm certainly not an expert in PHP or MySQL, but I have a good working knowledge of both.

 

Good Luck -

 

Allan Doute

www.doute.net

Link to comment
Share on other sites

Allan - thanks for the tips on migrating from Access to MySQL. I have written a couple of Web-based interfaces, so that shouldn't be a problem if I decide to go that route. I do appreciate your offer of help, and will try not to abuse it.

 

Jim - thank you for the link to phphelp. Seems that it would be possible to let the client use Access as the interface to MySQL.

 

The big question is Does TCH allow remote access to databases in this manner? Is there a GURU listening who has an answer to this?

Link to comment
Share on other sites

  • 5 weeks later...

Trying to do something like this now. I've got an Access *.mde beta that I'm trying to migrate to MySQL and then plan to create PHP pages for it. Through some research, I've been able to create a dump file and ftp'd it to my site but have been unable to locate any documentation on how to extract it onto TCH's servers.

 

I tho't about going from *.mde to *.mdb and trying to convert that way. However, I can only get some of the data structure to convert. I realize that I'll lose some of the code in the conversion, but I can work around that. I could do each of the tables by hand, but it would take quite a bit of time. I KNOW there has to be an easier way out there somewhere! But if that's the way it has to be, so be it. (I got the time to do it!)

 

Any suggestions/help from the "guru's" would be appreciated. TIA.

Link to comment
Share on other sites

Steve,

I haven't done exactly what you're asking, but it would seem that an easy solution would be to create a CSV from the Access db and then use phpMyadmin to upload the files.

 

But if you have a dump file.... if it's in SQL, I'd first try pasting the code into the query field in phpMyAdmin.

 

That's how I transfered my 95 databases from my old host to TCH. But I was going from MySQL to MySQL... so there might be another twist in there that I'm not aware of.

Link to comment
Share on other sites

Don't try to create new db through phpMyAdmin, instead us the Manage MySQL link in cpanel to create the db, create the user and assign the user to the db. Then all other functions can be done within phpMyAdmin.

 

Hope that helps.

Link to comment
Share on other sites

Try this thread that I started today in the Scripting forum

 

http://www.totalchoicehosting.com/forums/i...=ST&f=59&t=2399

 

Not the end all and be all of db info... but it should get you going in the right direction. Follow the suggestions to other resources and it should be pretty easy.

 

If you still need more help after trying the suggestions in that post then let us know where you get stuck.

Link to comment
Share on other sites

Jack:

 

Thanks for the "Into to PHP" thread. It nicely puts the basics into a language everyone can easily understand. It will also nicely augment my growing stack of PHP documentation.

 

Still having a problem trying to move my dumpfile into an actual database. I've got the database created (thanks lstover!). But I'm assuming (I know..I know!) that I can't use the mysqldump from the query field in phpMyAdmin to create the sql statement to dump the tables from the dump file into the blank database. Only select statements are allowed there, correct?

 

I'm sure it's buried in the mysql documentation somewhere, but I'm on the road for my job and I'm limited as to how long I can stay dialed-up without being charged. I seem to remember having a difficult time first learning Oracle, but as time progressed, it started to come easily enough. I'm sure this is the same way...just gotta get over that initial stumbling block!

 

Thanks for all the help so far.

Link to comment
Share on other sites

Yes. You can use the dumpfile by pasting it right into the SQL field in phpMyAdmin.

 

It will create all the tables and populate it with data, assuming the dumpfile is written correctly.

 

I've had to restore a database before and that's exactly what you do. Now the dumpfile I was using was made by phpMyAdmin... but there's no reason I can think of why this wouldn't work for you.

Link to comment
Share on other sites

Ok...I'm officially stumped. Head Bash I've read the MySQL manual and tried to enter the syntax in to the SQL field in phpMyAdmin. However, all I got was an error message.

 

Syntax used: mysqldump databasename < dumpfile.sql

 

Somehow I'm just not getting it! Any and all help appreciated.

Link to comment
Share on other sites

Tip: Open your dumpfile.sql for editing through the cPanel FileManager. Highlight all contents of the file and CTRL+C to copy. Then open phpMyAdmin and click SQL. Paste in the contents of your copy via CTRL+V.

 

You may need to edit the content to exclude any comments, etc. Make sure that the SQL statement then ENDS in a semi-colon (:).

 

Go

 

Good luck

Link to comment
Share on other sites

I am happy to report that I've finally figured what I've been doing wrong. It took some digging, trial and error, but I now got it.

 

Many thanks to lstover and surfire for the guidance and links. BTW...msa2mys works just fine with some slight modifications. Saves a BUNCH of typing!

 

This makes me happy I'm here with TCH!

 

Rock Sign

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...