Results 1 to 6 of 6
  1. #1
    kennyritch
    Guest

    Exporting from one DB to another (ODBC) (2000)

    Hi Folks

    I've got an external mySQL DB on our server and use a local 'Access' front-end form to help me view the data in a nice user-friendly way (connecting through ODBC).

    Data in the DB is often changed and therefore I overwrite previous info with new info. I would like a button on the form that allows me to store the historical data in another table in my external mySQL DB (or maybe a simple text file?) so that I don't lose it completely.

    Does anyone know how I can do this or have any ideas as to where I could find out more about this technique. Presumably it can be done?

    Cheers,
    Kenny.

  2. #2
    magenta
    Guest

    Re: Exporting from one DB to another (ODBC) (2000)

    Why are you overwriting your data? If you want to keep a historical record I would have set up the DB so that records are only added.

    Perhaps if you gave a bit more detail about your DB situation?

    cheers <img src=/S/thinks.gif border=0 alt=thinks width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting from one DB to another (ODBC) (2000)

    Kenny,
    I suspect magenta's 'adding data' solution seems to be a good solution from my limited knowledge - especially as mySQL is supposed to very designed to work well and fast with large tables. I can't really answer your query but I have one of my own: was it very hard to convert your Access frontend/backend
    setup to Access front end/mySQL remote back end.
    Also, do you regret it in any way apart from the problem of keeping historical snapshots you have alluded to?

  4. #4
    kennyritch
    Guest

    Re: Exporting from one DB to another (ODBC) (2000)

    I never started with a pure Access frontend/backend so there was no conversion involved. I always intended to use an Acess frontend/mySQL remote backend and I don't regret it one bit.

    Basically the process is quite straight forward. Create a mySQL DB on the server and then create an ODBC link on the home PC. Then link to this DB using the Link Tables option in Access. Build your form and you're done!

    Since I posted my question on this board I've developed a solution to the historical data problem I was having. Basically I created a separate table to hold historical data and linked this into my Access form using a subform. Easy.

    Cheers,
    Kenny.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting from one DB to another (ODBC) (2000)

    Thanks Kenny, it sounds easy but I need to do some work. My problem is taking the tables with their fairly involved relationships and transferring them to mySQL without re-inventing the wheel.
    By the way, do you really look like that bird?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting from one DB to another (ODBC) (2000)

    Kenny, I haven't succeeded yet in setting up an Access front end/mySQL back end (varous error messages abound) but am still trying. But I read the other day that mySQL does not yet support sub-selects, stored procedures or triggers. I use sub-selects in my Access front end quite often. This leads me to ask: does the mySQL DRIVER myODBC automatically handle this situation? Does it convert for example, a query in access based on another query into an equivalent SQL statement that mySQL understands?
    thanks for any insights.

Posting Permissions

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