-2

I have an application built on ASP Classic & Microsoft Access (for the Database) which I have been maintining for a long time, but now I want to upgrade it to PHP for the betterment.

Maintainence Procedure (3/4 times a day)

  1. I download the live Access file from the server.

  2. I run the UPDATE queries that update records into an Oracle database (my company's local database).

  3. Then I import data from the Oracle database.

  4. Upload it back to the website

The Problem:

I can't find the alternate to this procedure in MySQL. On the other hand, Access has very easy ways to run that procedure by linking to Oracle tables: I just need to click them!

MySQL has the Import/Export option but I don't know how to do it, like I did in ms-access.

If there's a simple way to download the Live Database and the Export in ORACLE and them import it back to the live database then I can surely upgrade my website to PHP :)

FrustratedWithFormsDesigner
  • 46,105
  • 7
  • 126
  • 176
yaqoob
  • 99
  • 3
  • 2
    This question doesn't make much sense to me. What are you trying to move from where to where ? You want to know how to move data from MS Access to MySQL, is that correct ? – Machado Apr 28 '17 at 14:47
  • 1
    you mean downgrading – Ewan Apr 28 '17 at 15:47
  • @Ewan no upgrading. alot of things i cant do with asp classic so i need to move it to php – yaqoob Apr 28 '17 at 17:16
  • @Machado leave it. – yaqoob Apr 28 '17 at 17:17
  • like what? why not upgrade to the current version of asp? – Ewan Apr 28 '17 at 17:17
  • Current version is ASP.NET right? i don't want to involve in that, PHP is much better alot of support, documentation and free of cost. i never ran into a problem that cant be solved in PHP rather asp. its my own choice – yaqoob Apr 28 '17 at 17:20

1 Answers1

2

So you "physically" pull the database [file] off the web server machine, open it, run an "Update" that pushes data from Access to Oracle (presumably through Linked Tables), then pull data from Oracle back into Access, then push the database [file] back onto the web server machine.

Let's ignore the potential for data loss caused by just splatting the database file back onto the web server, where it might have been updated since you last copied it ...

You could write a new program to automate the process of querying MySQL and updating data into Oracle, then reading Oracle and updating data into MySQL. Many would advocate this and, most of the time, I probably would as well. However, you've got something that works, albeit involving Access, so here's an alternative ...

You could continue to leverage Access' Linked Tables feature.

You currently have "actual" tables in the Access database that you read and then push data [via Linked Tables] to Oracle. Then you pull data [via Linked Tables] from Oracle into those "actual" tables.

What's to stop you replacing those "actual" tables with more Linked Tables, pointing at the new, post-migration, "real" tables, held in MySQL? (Remember, Linked Tables like these might be a simple way of doing the actual migration to MySQL!)

You never copy a database file ever again. Just open the Access database and run your Queries.

Even better, write a Procedure in a Module in Access to run all those queries for you, completely automating the process so you literally just "push one button" and everything happens. And, in Access, you really can have that one Button. On a Form.

Better still, you could even write a small program (outside Access) to automate that automating Procedure and that program could be run by your company's Scheduling application - that way, you don't actually have to run this process at all any more!

Phill W.
  • 11,891
  • 4
  • 21
  • 36
  • or connect directly to the oracle server – Ewan Apr 28 '17 at 15:49
  • @Phill first of all many thanks to you for taking time to read my question and providing solutions. I thought of that using access to link oracle and mysql to export and import data. i must try it. and data loss is no issue because at the time of updating there is no activity in the database. i will definitely try your suggestions thanks alot. – yaqoob Apr 28 '17 at 17:13
  • @Ewan can't do that, we don't have resource to put oracle server online – yaqoob Apr 28 '17 at 17:15
  • 1
    I like this practical answer even though i hate the insane rube golberg machine connection of software and databases you end up with – Ewan Apr 28 '17 at 17:23