help starting mySQL

[suzyjazz]

I need to convert my MS Access DB to mySQL. There are virtually no web hosts that support Access.

Can anyone give me tips on how to get started? I have a month before my current domain expires and I'd really like to go with someone else, but I'd have to convert my Access DB to mySQL by that time.

All I need to do is design my database locally (not very many tables), load my new data into it (hope there is some sort of insert into table from Excel feature), tweak my ASP code to access the new DB, and then... not sure what is involved on the web server (my host) side. I guess I have to recreate these tables on the server side.

What do I need to buy? Is My SQL Workbench good enough for this? Is there something free?

[Donner]

I don't know much about mysql, just what i've done for a couple of clients who have needed it, so i may not be of much help.

I've either used the ssh access via my mac terminal, or the web access to the mysql database. One thing i've found is that if i save my excel file as a .csv i can upload it directly to a table in my database. As long as the fields all match then it will upload without and trouble.

i don't know if this makes any sense, but it is an easy way of uploading lots of data all at once, you just have to make sure the excel file has the same field titles and follows the rules for each field (no. of characters, date, etc) otherwise it will give you an error message and can be very frustrating.

[suzyjazz]

Originally Posted by Donner

I've either used the ssh access via my mac terminal, or the web access to the mysql database.

This part didn't make any sense, but I am sure I will get there. :)

Originally Posted by Donner

One thing i've found is that if i save my excel file as a .csv i can upload it directly to a table in my database. As long as the fields all match then it will upload without and trouble.

Thank you for this. There has got to be a feature in MS Access that allows me to save to CSV. I'll cross my fingers.

Thanks for replying! I found a free download and I just ran the config tool. I'll see what happens.

suzyjazz added 12 Minutes and 53 Seconds later...Double Post Merged Below

I feel like an idiot. I can't even log in.

Instructions say to do this to connect:

shell> mysql -h host -u user -p
Enter password: ********

Well, this is local, so I assume I can skip the host part. However, I don't have a user. I haven't created one. I did the config wizard where I selected a password, but I don't recall any sort of user ever being mentioned.

[Donner]

again, no expert, but i think there has to be a user and password because when your website queries the database it has to login somehow. Most people will have a login user and password that allows limited access and then an admin. user and password, so chances are you have at least one, if not both.

i'd probably start with a google search and see if anyone else has had the same issues you are having. I'd offer more but my experience is very very limited and i really only know how to deal with the shell access via terminal (which is what those commands you included are for) or via the mysql admin web access page.

[kayumangi]

Hi Suzy,

Before you consider migrating to MySQL, what sort of web hosting were you looking at?

I'm assuming that you are also using ASP rather than PHP?

Have you considered www.brinkster,com and www.1and1.com?

Anyhow, if you really want to move databases from MS Access to MySQL, checkout this program

http://www.bullzip.com/products/a2m/info.php

The easiest way to move to a hosting provider is to dump all you data into one file that you can run on a browser-based admin tool like phpMyAdmin.

Hope that helps.

kayumangi added 3 Minutes and 33 Seconds later...Double Post Merged Below

Sorry I just scanned the posts =)

SSH console is very cumbersome specially if you are not used to typing all the commands, that is why installing phpMyAdmin (if you web host don't have one) is the way to go. Its not GUI based like Access but at least you've got some menu action.

Not sure where you have installed your MySQL database but if its a fresh install the default username is root and the password is blank (as in blank, not the work blank lol).

[Brains]

If you get frustrated and stuck, I'd be happy to convert it for you, and send you back a raw SQL dump you can easily load into mysql (either from the command line, or with phpMyAdmin).

[suzyjazz]

Originally Posted by kayumangi

Before you consider migrating to MySQL, what sort of web hosting were you looking at?

I'm assuming that you are also using ASP rather than PHP?

Have you considered www.brinkster,com and www.1and1.com?

Anyhow, if you really want to move databases from MS Access to MySQL, checkout this program

http://www.bullzip.com/products/a2m/info.php

The easiest way to move to a hosting provider is to dump all you data into one file that you can run on a browser-based admin tool like phpMyAdmin.

I need a new host because mine sucks. There is NO support whatsoever. I'm using ASP. I know php has more support but there is no way I am re-writing my web site in the next month. Thank you for the suggestion of brinkster. I will check around to see if they are any good. They support ASP!

I'm not sure what my problem with the console was, but closing and re-opening it fixed it. I logged in, created my database, etc.

I've just finished doing all the annoying data manipulation garbage. I probably wasted my time, but it's done now, and I'm saving these text files forever. If I have to do it again, I will look into a tool! Thanks for the link.

I think I am using the user "root". It just opens and asks for a password. I'm sure I will have another adventure trying to set this thing up on whatever host I get. Right now my goal is to load the data (done) and tweak my local web site to read it.

suzyjazz added 1 Minutes and 43 Seconds later...Double Post Merged Below

Originally Posted by Brains

If you get frustrated and stuck, I'd be happy to convert it for you, and send you back a raw SQL dump you can easily load into mysql (either from the command line, or with phpMyAdmin).

Thank you, that is very kind. I got the upload to work from command line, though. :) I'm saving all my commands in a text file. I'm so glad the edit > paste feature is there.

Maybe once I make more progress I will look into the sqladmin deal.

[kwhite]

Originally Posted by suzyjazz

I need to convert my MS Access DB to mySQL. There are virtually no web hosts that support Access.

Can anyone give me tips on how to get started? I have a month before my current domain expires and I'd really like to go with someone else, but I'd have to convert my Access DB to mySQL by that time.

All I need to do is design my database locally (not very many tables), load my new data into it (hope there is some sort of insert into table from Excel feature), tweak my ASP code to access the new DB, and then... not sure what is involved on the web server (my host) side. I guess I have to recreate these tables on the server side.

What do I need to buy? Is My SQL Workbench good enough for this? Is there something free?

I'm not a mySQL expert but I do lots of import, export and manipulation of data using Access and SQLServer. Assuming that MySql supports ODBC, by far the easiest way to transfer your data is to create an ODBC connection to your MySql database and then attach your MySql tables to your Access database. You can then use update queries to move the data directly into your new database. In simple cases you may be able to just copy and paste the data from your old tables to your new tables. Piece of Baklava.

[suzyjazz]

Hey, Brinkster supports Access too! Wow. I hope these people are good.

Originally Posted by kwhite

I'm not a mySQL expert but I do lots of import, export and manipulation of data using Access and SQLServer. Assuming that MySql supports ODBC, by far the easiest way to transfer your data is to create an ODBC connection to your MySql database and then attach your MySql tables to your Access database. You can then use update queries to move the data directly into your new database. In simple cases you may be able to just copy and paste the data from your old tables to your new tables. Piece of Baklava.

That sounds cool but I don't know how to do that. Fortunately, I only have 2 tables to move.

[kwhite]

Originally Posted by suzyjazz

Hey, Brinkster supports Access too! Wow. I hope these people are good.

That sounds cool but I don't know how to do that. Fortunately, I only have 2 tables to move.

It's actually extremely simple. You can probably do the whole thing from within Access: For Access 2003 you would use File->Get External Data-> Link Tables. Select file type ODBC Databases.

Here is a link that will shows you everthing you need to know about setting up ODBC. http://www.ucl.ac.uk/is/mysql/odbc/

[stuckupsigns]

Most webhosts offer phpMyAdmin in the control panel. phpMyAdmin fully supports uploads from .csv (Excel) and text. What I would recommend you doing (as a SQL noob) would be to manually create a table in phpMyAdmin using just a small bit of information from your site, then export it as a .csv or .sql file from there. That will allow you to see the structure that MySQL needs. Open your dump file in Excel, move everything around to suit, then import it again using phpMyAdmin. As far as connecting to your db using .asp, I can't help. All I know is .php. You should be able to find a tutorial online that will show you how to connect though.

[zeroendless]

If i were me and for two tables, i won't do conversion but create new tables and import data using phpmyadmin likes others have suggested. With Access, CSV is probably ya best bet, and you shouldn't have any issue at all getting asp to access mysql, once the connection is set , there's much on sql command, they are more or less the same. I've done asp sites on My SQL database before and don't recall having cross language issues, unless you are fancy for storage procedure and temp tables (then you should check which My sql version on the web server).

The important part is making sure IUSR users permission is set accordingly for table fields and auto-increment field is marked where it should after imported.

Good luck.

[suzyjazz]

Not sure why phpmyAdmin is being mentioned -- I'm using ASP/VBScript not php. Did you all mean mySQLAdmin? I haven't installed that yet. Anyway, my 2 tables are loaded and now I am trying to connect my ASP page to the database, but it's not working. I'm searching for an answer.

If it helps, I'm a mySQL noob but definitely not a SQL noob.

has anyone tried to connect web site on their C drive (I'm using c:\inetpub\wwwroot\) to mySQL database? I don't know what else to do since I already tried the code I found everywhere (sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=photos; UID=[x]; PASSWORD=[y]; OPTION=3")

[stuckupsigns]

we are mentioning phpMyAdmin because it is the application of choice with most web hosts. Your local machine is a different story. There is a local machine connection tutorial on the W3C site for .asp and SQL.

[suzyjazz]

OK. Thanks for the further details. That should help once I make it to the other side.