dcsimg
www.webdeveloper.com
Results 1 to 4 of 4

Thread: From Access Database database to the web. Best approach?

  1. #1
    Join Date
    Apr 2006
    Posts
    370

    From Access Database database to the web. Best approach?

    I'm working with a admin company that uses a PC-based Access database to manage membership records. The membership information - at least certain parts of it - are needed for the website (mySQL on the web server).

    At some point it may be possible to install something on their PC to "throw the data" automatically. But for now, I need to assume that I won't be able to tamper with their machine in any way.

    Two pieces of "good news": (a) as far as I know, there will be no need to synchronise data: it will be a one-way street Access ==>> mySQL; (b) the data transfer is likely to be infrequent: once a day at the very most.

    so....
    • Would appreciate your thoughts on how best to get data from their Access database to the database on the webserver.
    • Would also appreciate your thoughts on whether to transfer all of the data (+/- 1000 records), or just changed data.


    Thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,617
    I don't believe Access has a built in change-tracking mechanism, so if you wanted to transfer only changed records, you would need to create some mechanism to track what was added, updated, and/or deleted since the last transfer. Therefore I'm guessing that just uploading the entire table might end up being the more effective solution (but that's based on a lot of assumptions, so don't hold me to it).

    As far as getting the data from Access to MySQL, my first thought would be to use Access's export function to dump the table data into a SQL file. Then you could create a PHP file on the web host with a file upload form which then takes that SQL file and runs it against the database.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,617
    Here's a (perhaps slightly dated) article I found on migration from Access to MySQL that might give you more food for thought: http://www.kitebird.com/articles/access-migrate.html
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  4. #4
    Join Date
    Apr 2006
    Posts
    370
    NogDog - Thanks you very much for your posts.

    You've confirm some of my thoughts (which is always nice). Thing all the reords will have to move. I'd wondered about export to XML... but I hadn't though about SQL dump, which would be easier.

    Thanks you very much for the link. It threw up one idea that might be very useful: seems like its possible to link to one or more of the SQL tables from Access. Need to have a play; if it works, it would mean that the some of the SQL tables (things like categories) could become "the master data"... meaning that fewer tables would need to be uploaded.

    Thanks again for posting - very useful.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles