Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Unanswered: Table Corruption Problem?

    Greetings again all,

    I am trying to combine the results of three combo boxes into a single joint table in Access 10. The code looks like this:

    DoCmd.SetWarnings False

    ' delete old records

    DoCmd****nSQL "delete from EVObyClass"

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb

    Dim aName As String
    Dim aClass As Integer
    Dim aCount As Integer
    Dim aSQL As String
    Dim aCamp As String
    Dim aSQL2 As String



    ' query 1st list
    aCount = 1
    aCamp = "CampA"

    For aCount = 1 To 74 Step 1
    aSQL = "SELECT id, " & aCamp & " from evo_main where " & aCamp & " = " & aCount
    Set rs1 = db.OpenRecordset(aSQL)

    If rs1.BOF And rs1.EOF Then
    Else
    rs1.MoveFirst
    Do While Not rs1.EOF
    aSQL2 = "INSERT INTO EVObyClass (aStudent, aClass) VALUES (" & rs1("id") & ", " & rs1("CampA") & " )"
    DoCmd****nSQL aSQL2

    rs1.MoveNext
    Loop
    rs1.Close
    End If
    Next aCount

    ' query second list

    aCount = 1
    aCamp = "CampB"

    For aCount = 1 To 74 Step 1
    aSQL = "SELECT id, " & aCamp & " from evo_main where " & aCamp & " = " & aCount
    Set rs2 = db.OpenRecordset(aSQL)

    If rs2.BOF And rs2.EOF Then
    Else
    rs2.MoveFirst
    Do While Not rs2.EOF
    aSQL2 = "INSERT INTO EVObyClass (aStudent, aClass) VALUES (" & rs2("id") & ", " & rs2("CampB") & " )"
    DoCmd****nSQL aSQL2

    rs2.MoveNext
    Loop
    rs2.Close
    End If
    Next aCount

    ....

    Code originally was working fine (yes, the basic design is messy; its a long story), even when I updated the original 109 records. Then the code started stopping at student 109 (by ID number); we had about 140 in all, all of which had been added after importing the first 109 from Excel. I went to the table and determined that record id number 110 was missing, and that the record sets created by the select statement stopped at 109. However, if I just do a simple select statement query, all 140 records will appear.

    I assumed that somehow the missing 110 was an indication of a corrupt table, and tried recreating the table. No success. Part of the problem, I thought, might be that the joiner table, which had relationships with a student ID ( in a student table) and a class ID (in an EVO class table) in order to generate a report. These particular relationships prevent deletions from the main student table, and I thought perhaps some one had created a deletion, screwing up the main student file. I tried repairing the entire database, etc. I am at a total loss now.

    Ideas

    John Smith
    Aylmer, Quebec

  2. #2
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Table Corruption

    The mystery seems to be solved, but I am not quite sure why. I recreated the joiner table, and voila the count was correct. It appears that either the joiner table was corruption, or perhaps some undeleted relationship had come into play [I cleaned up the data base a bit too, and I had a (bad)tendency to save old tables (eg "EVO_main_may16") and thus old relationships].The initial "select" it turns out was fine, and the subsequent "inserts" were fine EXCEPT anything over 109 was not being inserted into the joiner table.

    As an aside, one thing that truly pisses me off in these forms is people who coming looking for help, find a solution and and don't bother posting the solution. You take people's time, you owe.

    Regards

    John S.
    Aylmer, Quebec

Posting Permissions

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