Hello,
I have a Access VBA program that extracts data from a variety of tables, does calculations on that data and then inserts them to a table I have here locally in an MS Access database.
Originally, my Access/VBA program was accessing an oracle database (Oracle 8i). I tried using ADO and passthrough queries but due to the amount of processing required across the network I decided not to do this way. The program I created loops through and queries 100s and 1000s of times, performs manipulation on the data for every loop (e.g aggregates / sum values), and performing 100s of thousands of inserts to the Access tables using the values from each query.
I decided instead to import the data I needed to my local machine from Oracle into Access. From there, I use the program I created in Access to perform the queries on the imported tables, the code performs the logic/processing locally on my desktop, and then inserts all of the records into the Access table. I'm sure this is probably not the best way to do it but it accomplishes the goal.
Some of my local access tables that were loaded are out of date now (because I haven't finished developing the program that keeps my local Access tables updated with the data from Oracle DB- this other program will run periodically on a scheduled task), so I need to import the data from 36 or so Oracle tables that are located across the network (In Florida - I'm here in CA) and then do the same extract/load again.
The combined # of rows of those 36 or so tables is approximately 1,300,000.
When I selected all the tables to import, it takes an extremely long time. It's already been 4 hours or more and hard to tell whether the import is evening happening or if Access is locked up.
Access is taking up 49616K of memory and the cpu utilization is low (15 - 20 % up and down).
Network Utilization doesn't appear to go above .12% I'd prefer not to shut access down in the middle but
I don't know how long it's going to run.....
Unfortunately, I don't have access to the Oracle database server that I'm importing from and can't store/run my .mdb (Access form/VBA) program on it.
Would anyone have some ideas or thoughts for how to do this better in the situation described? Instead trying to import all 36 tables ... maybe I should import a few at a time? Would that make much of a difference?
The other thing I was thinking was to possibly obtain a free version or Oracle or SQL Server and replicate the tables from the Oracle database in Florida to a local database/tables here where I can run the extract off of instead of trying to import everything into Access... After everything is imported, the database size is almost 1.5 GB.... and I think the limit is 2 GB. Eventually this project may need to be handed over to our IT but I would at least like to educate myself on the possible alternatives and make some good recommendations before we do that.
If you can share thoughts, suggestions, etc... it would be much appreciated.
Thanks!
Start Free Trial
