Export Oracle table (data) to 50000 records files (VBA conversion)
From: David M Higgs <david.higgs_at_aldasys.demon.co.uk>
Date: 13 Jan 2003 10:09:36 -0800
Message-ID: <d1e250c4.0301131009.22ac7635_at_posting.google.com>
Date: 13 Jan 2003 10:09:36 -0800
Message-ID: <d1e250c4.0301131009.22ac7635_at_posting.google.com>
Dear All
I have a table of employees (~320,000 records) that I need to export
to text files in multiples of 50,000 records. (The reason: the
destination system crashes when you try and load too much data in one
go!!)
The routine below (VBA) works fine via Access and ODBC but I would
love to have it as an SQL script.
Any takers? I've been trying for hours now and can't get it to work.
Thanks very much in advance for all help.
-- Regards, David Sub ExportTable()Received on Mon Jan 13 2003 - 19:09:36 CET
'declare variables
Dim db As Database Dim rst As Recordset Dim sSQL As String Dim lngRecord As Long Dim iFileIndex As Integer Dim sFilename As String Set db = CurrentDb
'declare recordset
sSQL = "SELECT * FROM EMPLOYEES;" Set rst = db.OpenRecordset(sSQL) rst.MoveFirst iFileIndex = 1 On Error GoTo ExportSpodMart_Err While Not rst.EOF 'loop through all records sFilename = "I:\spod\spodmartdata\file-" & Format(iFileIndex, "00") & ".txt" Open sFilename For Output As #1 For lngRecord = 1 To 50000 Print #1, rst!FIRSTNAME & "|" & rst!LASTNAME & "|" & rst!TELEPHONE & "|" & rst!EMAIL rst.MoveNext Next lngRecord Close #1 'close file iFileIndex = iFileIndex + 1 'increment index Wend
'close recordset and release objects
rst.Close Set rst = Nothing Set db = Nothing Exit Sub ExportSpodMart_Err: 'crashed out - run out of records! Close #1
'close recordset and release objects
rst.Close Set rst = Nothing Set db = Nothing End Sub