VBA Loading: SQL-Loader vs. VBA
Hi Everyone,
I have an application where we need to load several hundred thousand rows. We're using SQL-Loader and that all works perfectly and is very fast.
However, I need to hand over this functionality to users and give them a visual basic (in MS-Access) screen to import the data.
Of course I could import/load the data in one go (using ms-access append queries and attached oracle tables) but it takes an eternity and there are locking problems because the load is only committed once it is all read/written.
At the moment I am testing a version where VBA code reads the attached source file and writes line by line to the Oracle table. This is good but the writing is understandably extremely slow.
What I really wanted to do was to use an MS-Access query and write just a few hundred rows at a time. But I was unable to do this because it seems to be very difficult to obtain a clear/unique rownum for the (VBA) records as they are read.
What would be idea would be, if I could write INSERT statements that insert several dozen rows each time: That could potentially really increase the speed of writing.
Does anyone out there have any experience of this? Is there a quick way to write large volumes of data from VBA recordsets to Oracle?
Any tips would be a great help.
Regards,
Alan Searle
PS: My suspicion is that I will either end up having to teach the users how to use SQL-Loader or I will need to call up SQL-Loader from VBA which will work but is an extra component and complication when we roll out the application.