you are working with an sql database. There is no need to copy 1 record at a time, 1 field at a time.
use queries.
number the records
then make a query to pull 1 record:
qsOutput = select * from input where recNo = forms!myForm!txtCount
it stores the X on the form and the query reads it to export only that 1 record.
then run thru the list exporting the query.
Code:
iCount = Dcount("*","input")
For x = 1 To iCount
forms!myForm!txtCount = X
sFile = "c:\temp\MyFile" & format(x,"00") & ".xls"
docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12, "qsOutput", sFile, true
next