Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Unanswered: Looping an Update of a Table

    I have a large database of about 300k entries. I have a text file that has been created with each line having individual, unique names and if they are true/false.

    There are about 60k lines in the text file and I got code that... works, but takes forever to run. I am really new to VBA with Access so trying to figure out a better way.

    The text file lines look like this:

    C:\Blah\Blah2\More\Stuff\12345678910.txt True
    C:\Blah\Blah2\More\Stuff\12345678911.txt False

    So I set up a loop to go through each line, pick the name and true/false out of the line (name and true/false are always in same place in line).

    If the line says 'True' then I update a field named Contacted in a table called Contacts inside my database to be 'Yes'. I find the entry to be updated by saying where id is say '12345678910' and update that. So it does this one at a time and takes forever.

    I think I could use recordsets instead and it would be much faster, but I am not sure if this would update the table itself? I appreciate any help you all can give on this.

    Here is the code I am currently using.

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    'Open the text file for reading
    Set objFile = objFSO.OpenTextFile("C:\TextFile.txt")

    'Loop through the lines in the file

    Dim StrLine As String
    Dim Check As String
    Dim T As String
    Dim F As String
    Dim mySQL As String
    Dim Contactname as String

    Do While objFile.atendofstream = False

    StrLine = objFile.Readline
    Check = Mid(StrLine, 95, 4)
    Contactname = Mid(StrLine, 61, 5) & Mid(StrLine, 69, 3)
    F = "Fals"
    T = "True"

    If Check = T Then
    Contactname = Mid(StrLine, 61, 5) & Mid(StrLine, 69, 3)
    mySQL = "UPDATE CONTACTS"
    mySQL = mySQL & " SET CONTACTS.Contacted = Yes"
    mySQL = mySQL & " WHERE (Instr([ACC_ID]," & Contactname & ")>0)"

    DoCmd.SetWarnings False
    DoCmd.RunSQL mySQL
    DoCmd.SetWarnings True

    ElseIf Check = F Then
    End If

    Debug.Print StrLine

    Loop

  2. #2
    Join Date
    Aug 2008
    Posts
    58
    Why not just import the ContactID and True/False values into a table? Then run an update query to change the value of 'Contacted' in tblContacts to the True/False value in the new table. Considering that you're dealing with a massive number of records, running one large update query will be much more efficient than looping through a recordset.

    J

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •