Hi, thank you for reading this. I have a database that is not at all structured correctly but is the only way we can receive the data. The table has over a million rows and consists of the following columns:
dataID - just a unique ID; not used by us
system_id - the system the question is valid for
siteName - name of the site
siteAddress - address of site
siteType - type of site
QLevel - level of question
fieldQuestion - question label
fieldAnswer - answer
questionID - question number
dataID | system_id | siteName | siteAddress | siteType | QLevel | fieldQuestion | fieldAnswer | questionID
1234 | 12649 | ABC Location | 123 Main St | Building | 1 | This is a question | This is their answer to77 | 77
1234 | 12649 | ABC Location | 123 Main St | Building | 2 | This is a child question1 | This is their answer to78 | 78
1234 | 12649 | ABC Location | 123 Main St | Building | 3 | This is a child question2 | This is their answer to79 | 79
Each question has been grouped together, so for the above example 77,78,79 would go into a specific table but 80-99 may be in another table and so on. I did this to clean-up the tables and also because I didn't want to put hundreds of questions into a single table. So what I would like when all is said and done are tables that look like this:
system_id | question_77 | question_78 | question_79
12649 | This is their answer to77 | This is their answer to78 | This is their answer to79
(for reference; system_id is a number, and all question fields are memo. Added pipes just to show separation of data)
The other tables would look similar but with their own set of questions.
What I first did was to create all of the new tables with their corresponding "question_#" fields. Each questionID corresponds to a specific table and not all are sequential.
Now I need to move the data from the original table to the new recipient tables. Below is the VBA. However, when I run it I tend to get different issues at different times. I have never successfully gotten all the way through to the end. Sometimes I can get 100K+ through and then it errors out other times only a couple thousand and it errors out. The error is typically 'Run-time error '3049': Cannot open database ". It may be a database that your application recognizes, or the file may be corrupt.' (again almost at random). Below is the VBA that I wrote to move everything to its correct table:
Private Sub Command75_Click()
Dim dbs As DAO.Database
Dim myOriginalTable As Recordset
Dim myRecipientTable As Recordset
Dim newQuestionNum As String
'
Set myOriginalTable = CurrentDb.OpenRecordset("originaltable", dbOpenDynaset)
'
Do Until myOriginalTable.EOF = True
'
If myOriginalTable![questionID] = 77 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableA", dbOpenDynaset)
If myOriginalTable![questionID] = 78 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableA", dbOpenDynaset)
If myOriginalTable![questionID] = 79 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableA", dbOpenDynaset)
If myOriginalTable![questionID] = 80 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableB", dbOpenDynaset)
If myOriginalTable![questionID] = 85 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableC", dbOpenDynaset)
If myOriginalTable![questionID] = 87 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableB", dbOpenDynaset)
If myOriginalTable![questionID] = 92 Then Set myRecipientTable = CurrentDb.OpenRecordset("tableD", dbOpenDynaset)
'
' The above continues for every single question # so that each one will be placed
' in the correct table; there are a couple hundred and not all sequential
'
' Convert number to a field name
'
newQuestionNum = "question_" & myOriginalTable![questionID]
'
' Determine whether this systemID exists in new table
'
myRecipientTable.FindFirst ("[system_id] = " & myOriginalTable![system_id])
'
If myRecipientTable.NoMatch Then
'
' systemID does not exist in new table, create it
'
If Len(myOriginalTable![fieldAnswer]) > 1 Then
myRecipientTable.AddNew
myRecipientTable![system_id] = myOriginalTable![system_id]
myRecipientTable(newQuestionNum).Value = myOriginalTable![fieldAnswer]
myRecipientTable.Update
End If
Else
'
' systemID exists in new table, update it
'
If Len(myOriginalTable![fieldAnswer]) > 1 Then
myRecipientTable.Edit
myRecipientTable(newQuestionNum) = myOriginalTable![fieldAnswer]
myRecipientTable.Update
End If
End If
'
myOriginalTable.MoveNext
End If
Loop
MsgBox "Complete!"
GoTo Cleanup
Cleanup:
myOriginalTable.Close
myRecipientTable.Close
Set myRecipientTable = Nothing
Set dbs = Nothing
End Sub
I added a status bar update but removed it in the above code. It is useful because it tells me whether the process is still running (but does sometimes freeze although it appears to still be running UGH).
The above basically checks to see if the systemID exists; if it does it overwrites the answer for that question with the new data. If it does not it simply adds the new systemID with the corresponding answer in the appropriate question column.
This is also a slow process. I'm hoping someone could tell me whether I'm doing this right or perhaps there is a simpler and quicker way to do this. Since the data is all over the place I figured the above would be the best way to do it. I'm also getting this large file on a weekly basis so I want to have a consistent method to process it and hopefully a reliable and fast way to process it.
PLEASE LET ME KNOW YOUR THOUGHTS!!! I really like to learn from examples and other people's experiences. If I'm doing it wrong or maybe it just needs a tweek, I appreciate any and all feedback.
Jon