Migrate Access database to Oracle

Toad Data Point & Toad Intelligence Central
1

Has anyone tried using TDA to migrate Access database (mdb) to Oracle db? I just tried to export data from Access to csv files and got errors when I attempted to export multiple files. It works fine with exporting a single file. Here are the errors:

Automation Logging:11/3/2010 4:22:18 PM: Connection successful.
Automation Logging:11/3/2010 4:22:18 PM: String cannot be of zero length.
Parameter name: oldValue
Automation Logging:11/3/2010 4:22:18 PM: at System.String.Replace(String oldValue, String newValue)
at Quest.Toad.ImportExport.ValidateConnection.ReplaceConnection(String xmlTemplate, String connTrl)
at Quest.Toad.Workflow.Activities.Database.ExportWizardActivity.Execute(ActivityExecutionContext executionContext)
Automation Logging:11/3/2010 4:22:18 PM: Continuing after error
Automation Logging:11/3/2010 4:22:18 PM: Completed with errors

Any ideas?

2

The error below has to do with the Automation script says to use a different connection than what is originally defined in the export template. In TDA 2.7 you can use the export wizard without parent connection. Try creating a new automation script with new export wizard activity.

Also, how is your export defined? Are you selecting multiple tables to go to csv file?

I can also suggest doing this a different way. In 2.7 you can just use Import to query from Access and import into Oracle all in one action. Use the Import Wizard Add Query type. Point to the Access connection and enter select * from yourtable. When you do it this way the data types of the new table it creates will be more accurate. You can also define the new table to any column types you want.

Let me know how that goes.

Debbie

3

Debbie,

Thank you so much for pointing to 2.7. I was able to export multiple tables to multiple files in one step. However, I haven’t found a way to query multiple files from Access database and import them to different tables in Oracle. Your example works for a single table. For now, I have to export all tables from Access to csv files then import them to Oracle tables, so it’s a 2-step process. It would be slick if there’s a one-step process that I can query all Access tables and migrate data from Access to Oracle.

Once again, thanks so much for your help.

4

I entered CR 79,589 for this request. When we impelment would depend on how many other users would also like this feature.

Anyone else want to comment if they would need an import whole access database fetaure?

Debbie

5

Debbie,

Importing a whole Access database may be too much but I think it would be useful to be able to import more than one table.

Thanks

6

Okay. I updated the request selecting multiple Access tables.

Thanks,

Debbie