Update Database too slow!!!

[Kika]

Hi,

I am using txt files to update my database. First, I delete the contents
within the tables in the database and fill them thru control data bounded
to the tables. The function that I use to delete the database is very fast.
Here is the function:

' Delete a table contains in the database
Public Sub deleteTable(tableName As String)
dbCP2000.Execute "DELETE * FROM " & tableName, _
dbFailOnError
End Sub

But to fill the tables in the database, I did not find a similar function
with SQL to read in txt files and fill them. So I wrote the following function
to do this job thru the bounded data control:

' Sub That fill a table from a txt file
Public Sub fill_Table(ctrlData As Control, _
tableName As String)
Dim fld As Field
Dim strRow As String ' Line in the file
Dim strField As String
Dim intPos As Integer
Dim fNum As Integer ' Hold the file channel
Dim i As Integer

On Error Resume Next

' Create a new recordset
Set ctrlData.Recordset = dbCP2000.OpenRecordset _
(tableName, dbOpenTable)

' Assign a channel number and ...
fNum = FreeFile

' Open the file
Open App.Path & "\" & tableName & ".txt" _
For Input As fNum

Do Until EOF(1)
Line Input #fNum, strRow
Debug.Print strRow
With ctrlData.Recordset
.AddNew

'For Each fld In .Fields
For i = 0 To (.Fields.Count - 1)
' If a tab delimiter is found, field text is
' to the left of the delimiter
If InStr(strRow, Chr(9)) <> 0 Then
' Move position to the delimiter
intPos = InStr(strRow, Chr(9))
' Assign field text to strField variable
strField = Left(strRow, intPos - 1)
Else
' If the tab delimiter isn't found, field text is the
' last field in the row
strField = strRow
End If

' Assign the a value to a field
'fld.Value = strField
Debug.Print strField
.Fields(i).Value = strField

' Strip off field value text from text row.
strRow = Right(strRow, Len(strRow) - intPos)
intPos = 0
Next i

.Update
End With
Loop

' Close the chanel
Close fNum
End Sub

The problem is this function is too slow. So it take a very long time to
process. The database has 8 tables with one very huge ( about 40,000 items
). I will like to have to have another way to do it in order to make it more
faster. It is very important and any help or suggestion will be very helpfull.

Thank You in advance
Kika

[Jeff]

"Kika" <cdiallo@hotmail.com> wrote:
>
>Hi,
>
>I am using txt files to update my database. First, I delete the contents
>within the tables in the database and fill them thru control data bounded
>to the tables. The function that I use to delete the database is very fast.
> Here is the function:
>
>' Delete a table contains in the database
>Public Sub deleteTable(tableName As String)
> dbCP2000.Execute "DELETE * FROM " & tableName, _
> dbFailOnError
>End Sub
>
>But to fill the tables in the database, I did not find a similar function
>with SQL to read in txt files and fill them. So I wrote the following function
>to do this job thru the bounded data control:
>
>' Sub That fill a table from a txt file
>Public Sub fill_Table(ctrlData As Control, _
> tableName As String)
>Dim fld As Field
>Dim strRow As String ' Line in the file
>Dim strField As String
>Dim intPos As Integer
>Dim fNum As Integer ' Hold the file channel
>Dim i As Integer
>
> On Error Resume Next
>
> ' Create a new recordset
> Set ctrlData.Recordset = dbCP2000.OpenRecordset _
> (tableName, dbOpenTable)
>
> ' Assign a channel number and ...
> fNum = FreeFile
>
> ' Open the file
> Open App.Path & "\" & tableName & ".txt" _
> For Input As fNum
>
> Do Until EOF(1)
> Line Input #fNum, strRow
> Debug.Print strRow
> With ctrlData.Recordset
> .AddNew
>
> 'For Each fld In .Fields
> For i = 0 To (.Fields.Count - 1)
> ' If a tab delimiter is found, field text is
> ' to the left of the delimiter
> If InStr(strRow, Chr(9)) <> 0 Then
> ' Move position to the delimiter
> intPos = InStr(strRow, Chr(9))
> ' Assign field text to strField variable
> strField = Left(strRow, intPos - 1)
> Else
> ' If the tab delimiter isn't found, field text is
the
> ' last field in the row
> strField = strRow
> End If
>
> ' Assign the a value to a field
> 'fld.Value = strField
> Debug.Print strField
> .Fields(i).Value = strField
>
> ' Strip off field value text from text row.
> strRow = Right(strRow, Len(strRow) - intPos)
> intPos = 0
> Next i
>
> .Update
> End With
> Loop
>
> ' Close the chanel
> Close fNum
>End Sub
>
>The problem is this function is too slow. So it take a very long time to
>process. The database has 8 tables with one very huge ( about 40,000 items
>). I will like to have to have another way to do it in order to make it
more
>faster. It is very important and any help or suggestion will be very helpfull.
>
>Thank You in advance
>Kika


Try the Insert Into statement. It puts a complete record in one statement.