SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    moving data from mySQL to Access

    I need to move my data from mySQL to MS Access. I was able to export 2 of my tables to csv files, then import them into access tables.

    Access will not read my other table because it contains text fields with new lines.

    What is the easiest way to import this information, rather than having to input each record by hand...I have almost 1000 records that I'd rather not have to re-enter.

    Thanks.

  2. #2
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing into Access

    I have only done this once and the following worked for me (although there is probably a lot of better ways).

    Dump your mysql db with the following

    mysqldump -u[your username] -p [name of db] > [path to where you want to store you backup]

    Hit return and you will be prompted for your password.

    If all is well a backup will be made to the location you specified.

    The backup is a textfile that contains a huge (if you db is huge) SQL create and insert statement.

    Now you should be able (in theory) to copy this statement into Access and run the query to insert the data. However, if you have any fields that contain ' (i.e Micheal's web page) the statement will not work as Mysql delimits all ' with \ (ie. \') and Access won't like it.

    So to remedy this I did a search and replace on all \ and replaced with ' so the data would look like this: Micheal''s web page.

    This then worked for me.

    Also remember that you must create the dd in Access NOT the tables the statement will do this for you, but it won't create the initiall db.

    Good luck!!
    circlemaker

  3. #3
    SitePoint Enthusiast raynebair's Avatar
    Join Date
    Dec 2000
    Location
    Alabama
    Posts
    91
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is the script that I get from mysqldump different than the one I would get from using the dump feature in phpMyAdmin?

    I ran the one in phpMy Admin and when I try to run it to first create the table then insert the records it gives my syntax error on create table statement.

    Also it doesn't seem to support adding more than one insert statement at a time in the query.

    Another thing is my host doesn't support odbc so I can't link or import into access that way.

    thanks


Bookmarks

Posting Permissions

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