Need some MySQL help

Discussion in 'Archived Threads 2001-2004' started by Jeff Blair, Mar 15, 2002.

  1. Jeff Blair

    Jeff Blair Second Unit

    Joined:
    Apr 30, 2000
    Messages:
    335
    Likes Received:
    0
    ok, this might be a "little" over the heads of some of the people here. I have an Access 2000 database. I would like to know the best way to get the data into a MySQL database. Now, the kicker is, I will keep updateing the database in Access. I have tryed to out put it in text, but that didn't work. Any ideas? Thanks.
     
  2. MichaelG

    MichaelG Second Unit

    Joined:
    Jul 10, 2000
    Messages:
    322
    Likes Received:
    0
    Jeff,

    In a worst case senario you could write a program that reads the data from each Access table and sends creates to the equivilent MySQL table. How much data are we talking about importing? If it's a lot of data then you could just have the program create the import script for MySQL so that it only commits after an entire table, or set of related tables have been updated.

    After you get the MySQL database synched with the Access database could you just make dual calls to both databases?

    Maybe I don't understand the entire situation. Why do you need to continue to write to the Access database? What is the MySQL database going to be used for?
     
  3. Jeff Blair

    Jeff Blair Second Unit

    Joined:
    Apr 30, 2000
    Messages:
    335
    Likes Received:
    0
    I am going to use Crystal Reports to make some reports from the MySQL database. I can't get it to see the Access Database. So, I figured move it over to MySQL, and do it there. That is when the headache started.
     
  4. Mike St.Louis

    Mike St.Louis Supporting Actor

    Joined:
    Sep 22, 1999
    Messages:
    518
    Likes Received:
    0
    I have done this.
    You need to get the "My ODBC" API from www.mysql.com and install it.
    From Access you select Export from the file menu.
    In the dialog box there is a drop-down box for the type of file. There will be an ODBC option. Select it.
    From there you will get another dialog box with data types. There should be an option for MySQL. Select it.
    Finally you get a screen where you specify the database location, name and other options. You also enter your username and password.
    Whew!
    I haven't played with this too much so I'm not sure how you could append new data to an existing table.
    BTW, the ODBC API is quite useful for a number of things. With it I can grab data from my MySQL database from within Excel.
     
  5. Jeff Blair

    Jeff Blair Second Unit

    Joined:
    Apr 30, 2000
    Messages:
    335
    Likes Received:
    0
    Mike,

    I tryed that before. I just got the new version of MyODBC, and it worked. Well, kind of. I have 2 fields. One is a Date field, and the other is a time field. In the date field I get this 2002-1-02 00:00:00 and in the Time field I get this 1899-12-30 07:00:00

    Is there an easy way to take out the 00:00:00 and the 1899-12-30 from the fields?
     
  6. Alan Benson

    Alan Benson Stunt Coordinator

    Joined:
    May 15, 2001
    Messages:
    106
    Likes Received:
    0
     
  7. Jeff Blair

    Jeff Blair Second Unit

    Joined:
    Apr 30, 2000
    Messages:
    335
    Likes Received:
    0
    for some reason Crystal will not see some of my tables. I have tryed to set up a ODBC source, and to connect to it directly.

    As for the importing into MySQL. I do have the 2 fields set up as yyyy-dd-mm and hh:nn:ss
     
  8. Mike St.Louis

    Mike St.Louis Supporting Actor

    Joined:
    Sep 22, 1999
    Messages:
    518
    Likes Received:
    0
    Jeff,

    What you'll want to do is convert the fields in MySQL from DATETIME to DATE. This will drop the time portion at the end. You will have to do this from the command line on your MySQL computer. You will need to use the ALTER command. Here's an example from the MySQL site (you should always check here).

     
  9. Rob Speicher

    Rob Speicher Supporting Actor

    Joined:
    Nov 24, 2000
    Messages:
    935
    Likes Received:
    0
     
  10. Mike St.Louis

    Mike St.Louis Supporting Actor

    Joined:
    Sep 22, 1999
    Messages:
    518
    Likes Received:
    0
    I stand corrected. Dumb mistake on my part.
     

Share This Page