[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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
--------
0
ShawnGray
Asked:
ShawnGray
  • 3
  • 3
  • 2
1 Solution
 
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now