The third question was related to the one about the quantity exceeding the number of digits. That would make the string longer than the template example. Code behind Excel is an option but consider:
Code:
Sub ExportCSV()
Dim rs As ADODB.Recordset
Dim i As Integer
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM Table1 ORDER BY Model, FGNum, BegSerial;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
While Not rs.EOF
CurrentDb.Execute "DELETE FROM Table2"
For i = 0 To rs!QtyOrdered - 1
CurrentDb.Execute "INSERT INTO Table2(CSVString) VALUES('" & rs!FGNum & "~" & Left(rs!BegSerial, 1) & Format(Mid(rs!BegSerial, 2) + i, "000000") & "')"
Next
DoCmd.TransferText acExportDelim, , "Table2", "C:\" & rs!Model & ".csv"
rs.MoveNext
Wend
rs.Close
End Sub
This version appears to run faster:
Code:
Sub ExportCSV()
Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
Dim i As Integer
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ORDER BY Model, FGNum, BegSerial;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
While Not rs1.EOF
CurrentDb.Execute "DELETE FROM Table2"
Set rs2 = New ADODB.Recordset
rs2.Open "SELECT * FROM Table2;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
For i = 0 To rs1!QtyOrdered - 1
rs2.AddNew
rs2!CSVString = rs1!FGNum & "~" & Left(rs1!BegSerial, 1) & Format(Mid(rs1!BegSerial, 2) + i, "000000")
Next
rs2.Update
rs2.Close
DoCmd.TransferText acExportDelim, , "Table2", "C:\" & rs1!Model & ".csv"
rs1.MoveNext
Wend
rs1.Close
End Sub