Update Database too slow!!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Update Database too slow!!!

  1. #1
    Kika Guest

    Update Database too slow!!!


    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

  2. #2
    Jeff Guest

    Re: Update Database too slow!!!


    "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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center