too many transferText operations kills export functionality

[beckerttech]

We have an application that grabs data from a master table and then
exports text files containing just the data for each client - we
produce 50 text files every 30 minutes continuously.

We eventually hit a point where we get an error on the TransferText
line - error "This property is not supported for external data sources
or for databases created with a previous version of Microsoft Jet."

If you go to the Database window, right click on a table and choose
EXPORT - nothing happens No dialog boxes, no error messages, just
nothing like it's ignoring you.

Only way to resolve the issue is to restart Access - then everything
works find for a while until we must hit some large number of exports.

I can code around TransferText but is there a simpler method? A hot
fix for Jet?

Thanks.

 

[Ken Snell \(MVP\)]

I've never seen this type of error before. Can you post the code that you're
using?

 

[wphx]

I'm wondering whether something recursive is happening and the depths of the
recusion is blowing something out.

either that or whether some kind of timing pause is necessary between
exports if any flushing has to be done etc.

could antivirus have anything to do with this also?

 

[phillips001]

Within the loop, are you closing your recordset that you're using the
transfertext on (rst.close)? Weird messages to appear when memory or disk
limits are approached.

 

[beckerttech]

The code is below - I know there are different ways to do it but this
is a small (but troublesome) piece of the bigger picture.

FYI - this has to run ALOT before it trips the error - I don't have an
exact number but would estimate there are probably 20,000 transferText
executions (~4 days worth) before transferText fails and you can even
execute Export from the file menu anymore. (this set of code exports
individual data files for about 30 clients and does so about every 10
minutes 24 hours a day)

We'll try adding a rstRecOutput.Close in front of the rstRecOutput =
Nothing

--------------------------------------------

Set pvRec = CurrentDb.OpenRecordset("SELECT
tblDestinations_Plant.[LBL_WERKS], tblDestinations_Plant.[Name 1] FROM
tblDestinations_Plant;", dbOpenSnapshot)
Do While Not pvRec.EOF
'update query
Set pvQdf =
CurrentDb.QueryDefs("qryExport_Plants")
pvQdf.SQL = "Select * from tblLabels Where
LBL_WERKS = '" & pvRec("LBL_WERKS") & "'"
pvQdf.Close

'check if records exist
Set pvRecOutput = pvQdf.OpenRecordset()
If pvRecOutput.RecordCount > 0 Then
'create text file
Call UpdateStatus("Creating file for " &
Format(pvRec("LBL_WERKS"), "@@@@@@@@@@") & " " & pvRec("Name 1"))
pvFileName = fvOutputDirectory &
pvRec("LBL_WERKS") & "\labels.txt"
If Dir$(pvFileName) <> "" Then Kill pvFileName
'if directory doesn't exist then create it
If Not FileOrDirExists(fvOutputDirectory &
pvRec("LBL_WERKS") & "\") Then
MkDir (fvOutputDirectory &
pvRec("LBL_WERKS"))
End If
DoCmd.TransferText acExportDelim, ,
"qryExport_Plants", pvFileName, True
'These files are created when the export
happens, delete them
If Len(Dir(fvOutputDirectory &
pvRec("LBL_WERKS") & "\Schema.ini")) > 0 Then
Kill fvOutputDirectory &
pvRec("LBL_WERKS") & "\Schema.ini"
End If
If Len(Dir(fvOutputDirectory &
pvRec("LBL_WERKS") & "\Export.ini")) > 0 Then
Kill fvOutputDirectory &
pvRec("LBL_WERKS") & "\Export.ini"
End If
If Len(Dir$(fvAutoUpdateFile)) > 0 Then
FileCopy fvAutoUpdateFile,
fvOutputDirectory & pvRec("LBL_WERKS") & "\autoLabelUpdate.exe"
End If
Else
Call UpdateStatus("Creating file for " &
Format(pvRec("LBL_WERKS"), "@@@@@@@@@@") & " " & pvRec("Name 1") & "
- No Records")
End If
Set pvRecOutput = Nothing
pvRec.MoveNext
Loop

 

[Ken Snell \(MVP\)]

You need to explicitlly close the pvRecOutput object before you set it to
Nothing. Add this line just before you set it to Nothing:

pvRecOutput.Close
Set pvRecOutput = Nothing


--

Ken Snell
<MS ACCESS MVP>



The code is below - I know there are different ways to do it but this
is a small (but troublesome) piece of the bigger picture.

FYI - this has to run ALOT before it trips the error - I don't have an
exact number but would estimate there are probably 20,000 transferText
executions (~4 days worth) before transferText fails and you can even
execute Export from the file menu anymore. (this set of code exports
individual data files for about 30 clients and does so about every 10
minutes 24 hours a day)

We'll try adding a rstRecOutput.Close in front of the rstRecOutput =
Nothing

--------------------------------------------

Set pvRec = CurrentDb.OpenRecordset("SELECT
tblDestinations_Plant.[LBL_WERKS], tblDestinations_Plant.[Name 1] FROM
tblDestinations_Plant;", dbOpenSnapshot)
Do While Not pvRec.EOF
'update query
Set pvQdf =
CurrentDb.QueryDefs("qryExport_Plants")
pvQdf.SQL = "Select * from tblLabels Where
LBL_WERKS = '" & pvRec("LBL_WERKS") & "'"
pvQdf.Close

'check if records exist
Set pvRecOutput = pvQdf.OpenRecordset()
If pvRecOutput.RecordCount > 0 Then
'create text file
Call UpdateStatus("Creating file for " &
Format(pvRec("LBL_WERKS"), "@@@@@@@@@@") & " " & pvRec("Name 1"))
pvFileName = fvOutputDirectory &
pvRec("LBL_WERKS") & "\labels.txt"
If Dir$(pvFileName) <> "" Then Kill pvFileName
'if directory doesn't exist then create it
If Not FileOrDirExists(fvOutputDirectory &
pvRec("LBL_WERKS") & "\") Then
MkDir (fvOutputDirectory &
pvRec("LBL_WERKS"))
End If
DoCmd.TransferText acExportDelim, ,
"qryExport_Plants", pvFileName, True
'These files are created when the export
happens, delete them
If Len(Dir(fvOutputDirectory &
pvRec("LBL_WERKS") & "\Schema.ini")) > 0 Then
Kill fvOutputDirectory &
pvRec("LBL_WERKS") & "\Schema.ini"
End If
If Len(Dir(fvOutputDirectory &
pvRec("LBL_WERKS") & "\Export.ini")) > 0 Then
Kill fvOutputDirectory &
pvRec("LBL_WERKS") & "\Export.ini"
End If
If Len(Dir$(fvAutoUpdateFile)) > 0 Then
FileCopy fvAutoUpdateFile,
fvOutputDirectory & pvRec("LBL_WERKS") & "\autoLabelUpdate.exe"
End If
Else
Call UpdateStatus("Creating file for " &
Format(pvRec("LBL_WERKS"), "@@@@@@@@@@") & " " & pvRec("Name 1") & "
- No Records")
End If
Set pvRecOutput = Nothing
pvRec.MoveNext
Loop

 

[beckerttech]

Thanks - we'll give that a try.

 

[beckerttech]

Thanks again for the reply but it didn't fix it.

There is definitely a problem (albiet a small one) that affects
transferText after a large number of uses.

This code will illustrate the error as it doesn't use any recordsets.
You only need a table called Contacts - use the table wizard to create
it - to test. Not sure if there is any reason to pursue further -
I've worked around the issue by not using TransferText. Thanks again
for your replies.

Sub exportTest()

For x = 1 To 20000
DoCmd.Echo True, Format(x, "#,###,##0")

'this is the export
'transferText will usually cause error around 10,000
executions
'you will notice after the error that you can not manually
export anything from the database window
'until you restart Access
DoCmd.TransferText acExportDelim, , "Contacts", "C:\testfile"
& Format(x, "0000000") & ".txt", True

'I've run following command as many as 20,000 times with out
error
CurrentDb.Execute "SELECT * INTO
[Text;FMT=Delimited;HDR=Yes;Database=C:\].[testfile" & Format(x,
"0000000") & ".txt] FROM Contacts"


'this section just deletes the created files as we go so we
don't have to delete them at the end
'or run in to a DOS directory file limit issue
'it leaves the last file created in the directory so you can
check it's number
If Dir$("C:\testfile" & Format(x, "0000000") & ".txt") <> ""
Then
If Dir$("C:\testfile" & Format(x - 1, "0000000") & ".txt")
<> "" Then
Kill ("C:\testfile" & Format(x - 1, "0000000") &
".txt")
Else
End If
Else
Exit Sub
End If


Next x
End Sub