Results 1 to 12 of 12
  1. #1
    ABW Ambassador kse's Avatar
    Join Date
    November 29th, 2005
    Posts
    2,511
    Need Help with moving from MS Access to MYSQL
    Ok, I Know and use MS Access and ASP Classic. I want/need to either move to MS SQL or MySQL . So I have no idea where to start and wither to go with MS SQL or MySQL to start with. So to start getting a idea on how to get started I will ask the below questions:



    1) I currently create my Access file on my workstation including the tables & queuies. Can I do this with SQL ??



    2) I currently test my Access files on my Windows XP workstation. Can I do this with SQL??



    3) I then copy the Access database to my host when I go live. Can I do this with SQL



    4) If I decide I want to add a new field to my database after using it for some time (say middle name), I copy the access file back to my workstation then add the field and then test and then copy it back to my Host. Can I do the with SQL??



    I have a lot more question but I will just start with the above for now.

    Thank you

  2. #2
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    Hi KSE,

    Question 1) I currently create my Access file on my workstation including the tables & queuies. Can I do this with SQL ??

    You probably could but I wouldn't. Once you create your db tables (through the web admin provided by your db host) you will then embed the SQL queries into your pages and run them server-side (before the page loads).

    Question 2) I currently test my Access files on my Windows XP workstation. Can I do this with SQL??

    What do you mean by test? If you mean preview the data before you publish it to the world - you could always have a field named "active" or "live" and if the record is not active or live - you just don't display it to the world (but let you see it in your administration page)

    Question 3) I then copy the Access database to my host when I go live. Can I do this with SQL

    There should be no need to constantly download and upload your database. It is a file that you set up on your server and then input and retrieve data to/from.

    4) If I decide I want to add a new field to my database after using it for some time (say middle name), I copy the access file back to my workstation then add the field and then test and then copy it back to my Host. Can I do the with SQL??

    The host that hosts the db will have a web interface admin tool that will allow you to add/delete and configure you tables and feilds.

    It might seem like a pain but it is really powerful once you get the hang of it.

    Dave

  3. #3
    ABW Ambassador kse's Avatar
    Join Date
    November 29th, 2005
    Posts
    2,511
    Thanks Dave for the feed back, I will have to look at my current host to see what they offer that allows me to work with SQL, I only noticed a option that allow me to connect to an existing database.

    What about MS SQL or MY SQL?? Is their a difference??? I find most host offer MY SQL more.

  4. #4
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    MySQL is a lite version of MS SQL. I expect the licensing fees are much lower for MySQL (therefor the abundance of MySQL).

    It should be fine for your purposes. Your queries will (mostly) work if you decide to change to MS SQL at a later date.

    Dave

  5. #5
    ABW Ambassador kse's Avatar
    Join Date
    November 29th, 2005
    Posts
    2,511
    Thanks again Dave, I did have a look at one of my host and they do have a web interface admin tool so I played around with a old Blog I added some time ago and I was able to have a look and modify records & tables so that is a great start.

    Thanks again

  6. #6
    ABW Ambassador kse's Avatar
    Join Date
    November 29th, 2005
    Posts
    2,511
    Hi Dave can I ask another question? I have a directory script that can use either MS Access or SQL (with ASP) and it contains the following connection lines:

    strMyConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False; Data Source=" &Server.MapPath("database/database.mdb")&""

    'strMyConn ="PROVIDER=SQLOLEDB;DATA SOURCE=your-server;DATABASE=mydirectory;USER ID=mydirectory;PASSWORD=mydirectory;"


    So I know the top line is for MS Access so I guessing the second line is for SQL ??? Also what would be my "your-server" in the section SOURCE=your-server;

    So if this is a SQL connection string then this is all that needs to changes in my code inorder to switch to SQL?? I know I would have to convert my database. So I would not need to change my code to read, write, modify & delete record???

    I also found a tool at bullzip.com that looks like it will convert my ms access database to either MS SQL or My SQL

    Thanks again

  7. #7
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    I have sent you a PM...

  8. #8
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Quote Originally Posted by CanadianDave
    MySQL is a lite version of MS SQL. I expect the licensing fees are much lower for MySQL (therefor the abundance of MySQL).
    MSSql and MySql are two entirely different products. MSSql is a microsoft product. MySql is owned by Sun Microsystems and is distributed free of charge with a GNU GPL licence.

    My approach to this problem would be to set up an ODBC link directly from your access data to a server running MySql. You would have to download the windows version of MySql ODBC. Setting up the link is explained here . With this method any input in you Access database is automatically updated on the server

    Regards
    Bob

  9. #9
    Full Member iolaire's Avatar
    Join Date
    October 3rd, 2006
    Location
    Arlington, VA
    Posts
    229
    Quote Originally Posted by CanadianDave
    MySQL is a lite version of MS SQL. I expect the licensing fees are much lower for MySQL (therefor the abundance of MySQL).
    MySQL is not related to MS SQL. MS SQL is microsoft's MYSQL server language. MySQL is a opensource/paid for commercial support plans product that now is owned by Sun.

    Well you are learning you can install MySQL on your computer and work with it via your web application language, assuming you have that working on your computer. It is very possible to use mysqldump to then move your final tables and data online.

    Initially I recommend working locally and then learn how to work on you data on you host once you have a working system.

  10. #10
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    Thanks for setting me straight on the difference between the two...

  11. #11
    ABW Ambassador kse's Avatar
    Join Date
    November 29th, 2005
    Posts
    2,511
    Thanks again all for you help I have created a .sql file from one of my larger MS Access databases and will upload it to my host maybe tonight (once I figure out how to set it up on my host) and then play around connnecting to My SQL database.

    I have decided to go with My SQL for now since 3 of my 4 host support it and if I ever want to switch to PHP it would help with the conversion to PHP

  12. #12
    ABW Ambassador Georgie Peri's Avatar
    Join Date
    January 18th, 2005
    Location
    Norwalk, CT
    Posts
    846
    KSE here is what I do...

    Install mysql

    Get a program called
    Navicat Mysql ( use this to upload your Access DB into MYSQL )

    To make pages really easy in classic asp connecting to mysql .. try using
    http://www.hkvstore.com/aspmaker/


    ~~~~
    I still use ACCESS for alot of the entries / updates .. I use a custom interface I created a while ago for my merchant listings

    I do all my changes / updates in access , and just upload the DB into mysql when im done with navicat mysql ..
    OpA! Giasou Ti kanies!

  13. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. MYSQL LOAD DATA - access denied
    By vivekar in forum Programming / Datafeeds / Tools
    Replies: 6
    Last Post: October 4th, 2004, 04:58 PM
  2. MySQL Help!
    By Pete in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: April 29th, 2002, 02:44 AM
  3. Access to MySQL
    By eaglefire in forum Programming / Datafeeds / Tools
    Replies: 1
    Last Post: April 18th, 2002, 10:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •