MS Access Export to txt file - runtime error 3310

I have the following code, which runs great.  Until it hits around 11,000 records exported then it stops.  I get a runtime error 3310.

Anyway to work around this?  I'm currently just removing the newly exported records from the table and restarting the procedure.  But I have hundreds of thousands of records . . . would rather not baby sit.

---------
    Dim rs As DAO.Recordset, db As DAO.Database
    Dim strCsv As String, qd As DAO.QueryDef, sSql As String
   
    Set db = CurrentDb
    Set qd = db.QueryDefs("qryAcctList")

    Set rs = CurrentDb.OpenRecordset("tblAcctList")

    Do Until rs.EOF
    strCsv = rs(0) & ".txt"
    sSql = "select c1,c2,c3,c4,c5,c6,c7,c8 from tblAcctList where AcctNumber='" & rs(0) & "'"

    qd.SQL = sSql

     DoCmd.TransferText acExportDelim, AcctNumber, "qryAcctlist", "c:\CSVfolder\" & strCsv, False
   
    rs.MoveNext
    Loop
    rs.Close
    qd.Close
    db.Close
--------
ShawnGrayAsked:
Who is Participating?
 
hpdvs2Commented:
From what you described, "around 11,000" I'm guessing it varies a bit.  However, I'm willing to bet it takes the same amount of time prior to failing.

Your probably timing out.  

I'm not sure (don't have VB up in front of me) but I believe db, rs or possibly qd, might have a time out value you can change.

If not also check your server configuration, and default timeouts for that as well.  Since its a bulk transfer, you may want to also look into bulk exports.  

Either that or use a DataAdapter to fill a DataSet, so you don't need both connections open the whole time.
0
 
Rey Obrero (Capricorn1)Commented:
this line

DoCmd.TransferText acExportDelim, AcctNumber, "qryAcctlist", "c:\CSVfolder\" & strCsv, False


must be


DoCmd.TransferText acExportDelim, "AcctNumber", "qryAcctlist", "c:\CSVfolder\" & strCsv, False


wonderin why you are ot getting an error
0
 
ShawnGrayAuthor Commented:
Hey Cap -

It really works great; I'll take processing 11k records at a time any day.
The 'time out' issue suggested by hpdvs2 is possible but I may finish the project by the time I figure out how to get around it. :)

Thanks again.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Rey Obrero (Capricorn1)Commented:
run this codes



 Dim rs As DAO.Recordset, db As DAO.Database
    Dim strCsv As String, qd As DAO.QueryDef, sSql As String
   
    Set db = CurrentDb
    Set qd = db.QueryDefs("qryAcctList")

    Set rs = CurrentDb.OpenRecordset("tblAcctList")
    rs.movelast

    msgbox "No of records "  & rs.recordcount

   exit sub


post how many records shown in the message box
0
 
ShawnGrayAuthor Commented:
Originally I had 143,000  but with the timeout issue I only load and process 10,000 at a time.
0
 
hpdvs2Commented:
If I recall correctly, you can change your code to this:
    ...
    Set rs = CurrentDb.OpenRecordset("tblAcctList")
    dim count as int
    count = rs.RecordCount
    Do Until rs.EOF
    ...

Open in new window


I recall back when I used RecordSets with VB6, Move next literally makes the next request to the server and waits for the response, then works with it.  If you ask for the count (via RS, not Count in SQL) it grabs every record right away.  (though it eats more ram)
0
 
ShawnGrayAuthor Commented:
I've requested that this question be deleted for the following reason:

No solution
0
 
Rey Obrero (Capricorn1)Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.