I am doing a project. I must read from a text file and update it into the database. The text file is large. The average contain about 30,000 lines. The size is about 3Mb. Once I read the contents of the text file, I add it into the listbox to display it to the user. Then when the user clicks the Add button, the adding into the database process starts.
The problem is, it takes about 20-30 mins to finish the whole process. It will be even worse if there are lots of records in the database. I know my code is not efficient. I need your help to save time in the adding process. These are the codes for adding process:
'Break up each line, check them to see if they are unique and then save them into the database.
If lstBox.ListCount > 0 Then
For intCount = 0 To lstBox.ListCount - 1
Record_Serial_Number = Mid(lstBox.List(intCount), 1, 5) 'this is the unique number.
Shore_Station_Identification = Mid(lstBox.List(intCount), 6, 3)
Start_Date = Mid(lstBox.List(intCount), 9, 6)
Call_Direction_Code = Mid(lstBox.List(intCount), 33, 1)
Satellite_Link_Holding_Time = Mid(lstBox.List(intCount), 87, 5)
Chargeable_Duration = Mid(lstBox.List(intCount), 92, 5)
Assigned_Type_Of_Service = Mid(lstBox.List(intCount), 107, 2)
If rst.RecordCount > 0 Then
rst.Find "Record_Serial_Number=" & Record_Serial_Number 'To see if the new serial no. exists in the database.
intAbsolutePosition = rst.AbsolutePosition 'If the serial no. is found in the database, then intAbsolutePosition will be positive.
If intAbsolutePosition < 0 Then 'if the serial number is unique.
.Fields("Record_Serial_Number") = Record_Serial_Number
.Fields("Shore_Station_Identification") = Shore_Station_Identification
.Fields("Start_Date") = Start_Date
.Fields("Call_Direction_Code") = Call_Direction_Code
.Fields("Satellite_Link_Holding_Time") = Satellite_Link_Holding_Time
.Fields("Chargeable_Duration") = Chargeable_Duration
.Fields("Assigned_Type_Of_Service") = Assigned_Type_Of_Service
If lstDuplicateIds.ListCount = 0 Then
lstDuplicateIds.AddItem "The following records were not added because they already exist."
'As for my database connection, I use the recordset. It's Cursorlocation is adUseClient,
CursorType is adOpenDynamic and LockType is adLockOptimistic.
May I know what the problem is?
Page 1 of 1
Updating from a text file
2 Replies - 1990 Views - Last Post: 30 January 2005 - 08:09 AM
Replies To: Long Updating
Re: Long Updating
Posted 30 January 2005 - 04:44 AM
it's really irritating to read the code as the code in each line is extended onto 3 or 4 Lines....Try uploading the form that does all the transfer from the Text File to the Database....Dont send the TEXT File or the whole project...I just want to read the code and see if yur code can be improved.
Re: Long Updating
Posted 30 January 2005 - 08:09 AM
If you copy and paste the code into your IDE of choice (or file editor), the line breaks should disapear. He may not be able to upload the text file, I haven't seen NuLLiFy around for a while (see post date).
Page 1 of 1