Toad World® Forums

Connecting to Local Storage

We currently export data from local storage into Access Databases to which are linked excel spreadsheets - which produce 1000s of graphs each month using some vba.

Is there any way to bypass the microsoft access part and link the spreadsheets directly into local storage?

If possible this would save us one man day per month waiting for all the exports

Thanks in anticipation

This would really make our lives much easier - a response would be useful?

The embedded database is not capable of linking files. It is not an access database. How do these files get generated? We do support exporting SQL results directly to local storage in automation. See the execute SQL activity.

Hi Debbie - thanks for the reply.

Our workflow is as follows:

Automation runs SQL against several external databases (Oracle, SQL Server, MySQL) collecting monthly data and appending the data to Local Storage tables (about 100 million rows of data so far!)

Further automation then extracts the last 12 months of data from the Local Storage tables and exports this data to MS Access databases - from which we link multiple spreadsheets which contain many graphs.

I’m sure you can see that this is quite a garganuan task and TDP makes life a whole lot easier with it’s automation features.

But . . . .

We are running into the limitations of MS Access database sizes (and the amount of time TDP takes to export the data each month).

If we could simply plug our speadsheets via ODBC into our Local Storage tables then it would bypass about a days worth of exporting and circumvent the need for all the problematic Access databases.

It may be that there is a better way to produce our thousands of graphs each month - I’m open to suggestions (but they would need to be quicker and less effort to make changing our process worthwhile).

I’m interested in your opinion?

To get around this limitation, we use IMPORT WIZARD to move data from Local Storage to MySQL. Then use ODBC to connect to MySQL.

TDP_OWL - that sounds a good idea. Can you be more explicit how you went about this?

Create the tables in MySQL that will populate from LocalStorage or you can let Import Wizard do this for you. I prefer to let the import wizard create the tables with 10-15 records, then edit the fields and field types. Lastly, truncate the the table.

  1. Run your process to load LocalStorage.

  2. Connect to MySQL

  3. In Object Explorer > Select the MySQL database > Click the TABLES tab > Right-Click Select Import Wizard:

a.png

  1. In the IMPORT WIZARD dialog, click ADD QUERY

  2. In the connection drop down, select LOCAL STORAGE

  1. Open the SQL query you want to run against LOCAL STORAGE or paste the query.

  2. Click NEXT to continue, and follow through the next steps.

On your first try, limit the results to a small sample until you get the dataset you want.

very good idea ! I do the same with an Oracle instance…

all you need is a database with ODBC connector to link to Excel.

I’ve implemented a MySQl database on our reporting server (thanks to TDP_OWL for the assistance above). I’m running into one problem though! As I run the import wizzard the mysql.exe is growing (as if it is storing the results in memory). On Friday it actually brought down TDP because of lack of memory! Any ideas on how to fix this?

So - solution isn’t perfect but we have inserted command line instructions to shut down and restart the MySQL server in our automation. This works and our full automation runs - which is brilliant - if a little ham-fisted. Talk about a sledgehammer to crack a nut!