Transfertext Errors 3709 and 3001

[Jeff]

Hi all,

I'm trying to import a Tab separated values file in to a table. Funny thing
is it worked for a while then just stopped giving the following errors. I
have regenerated the text/data files and still no joy. I did copy the code
to a new data base and recreated the form and table and import spec and it
worked there for a little while and now is giving the same errors... I have
also tried deleting and recreating the import specs and still same error.
Any ideas??

Heres the code up to the transfer text statement...

'=========================
' Initialize Variables
'=========================
Dim MyDB As Database
Dim MyTdef As TableDef
Dim dbsCurrent As Database, dbsContacts As Database
Dim MyDataTbl As Recordset
Dim MyRndTbl As Recordset
Dim MyQry As Recordset
Dim NewRec As Recordset

Dim MyNdxFld As Field

Dim NumRows, MyRndNum As Long
Dim AryRndNum(51) As Long
Dim i, j, T1, T2 As Long

Dim MyStr As String

Dim MyPath As Variant

Dim MyFD As FileDialog

'====================================
'initialize text box value to empty.
'====================================
Form!Text3.SetFocus
Form!Text3.Text = " "
Form!Text8.SetFocus
Form!Text8.Text = " "

'==============================
' Create File Dialog Object
' Get File Path and File name
'==============================

MyPath = Empty 'clear all values from the variable.

Set MyFD = Application.FileDialog(msoFileDialogFilePicker)


If MyFD.Show = -1 Then
MyPath = MyFD.SelectedItems(1)
'MsgBox ("Path is : " & MyPath)
Else
MsgBox ("No Path Selected")
GoTo done:
End If

'Clear Object Variable
Set MyFD = Nothing


'========================================================================
' Delete Members Table to make room for import
'========================================================================
Form.SetFocus
Form!Label3.Caption = "Importing Data."
Form.Refresh

On Error Resume Next
DoCmd.DeleteObject acTable, "Members"
On Error GoTo 0
'====================================
'Import data to a new Members table
'====================================

DoCmd.TransferText acImportDelim, "Member Import Spec", "Members", MyPath,
True

Thanks!!

Jeff

 

[John Nurick]

Hi Jeff,

Have you tried compacting-and-repairing the database? This is necessary
in order to recover the space occupied by all the tables you keep
creating and deleting.

In general, it's a bad idea to create and delete tables as part of
everyday operations, not least because of this "bloating". If you need
to work with a succession of text files, you may be able to link to them
rather than import them temporarily. Or you can create a temporary mdb
file for your temporary tables.