Normalising Excel data

[tractorvix]

I've managed to normalise most of my tables, but there is on that is causing me problems!

It kind of contains 2 set of data and looks like:
01/01/08 01/02/08 etc
base 5 5.5
Product
A 100 150
B 10 20

I can successfully take the base numbers out and normalise them into a table, but I'm struggling with the product data as my code works on BOF and EOF. Is there a way that I can state the starting row, rather than using BOF?

Private Function NormaliseScenarioData(InputTable As String, OutputTable As String, FieldName As String, StartPoint As Integer)
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String
On Error GoTo errorhandler

y = CurrentDb.TableDefs(InputTable).Fields.Count
Set rs = CurrentDb.OpenRecordset(InputTable)

If rs.EOF And rs.BOF Then
Exit Function
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE " & OutputTable & ".*" & _
"FROM " & OutputTable & ";"

Do Until rs.EOF
RunCode:
For x = StartPoint + 2 To y - 1
sql = "INSERT INTO " & OutputTable & "(ProdXrefID, Scenario, dateval, " & FieldName & ")" & _
" VALUES(" & rs(StartPoint) & "," & rs(StartPoint + 1) & ",'" & rs(x).Name & _
"'," & rs(x) & ")"

DoCmd.RunSQL sql
Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
DoCmd.SetWarnings True
Exit Function

errorhandler:
Resume RunCode

End Function

Thanks
Vicky

 

[Golom]

If you are positionally dependent (which you appear to be), I wouldn't just open the table. Relational databases do not have any guaranteed ordering of rows if you don't have an Order By clause. Write an SQL statement to ensure that your rows are being returned in the order that your code expects.

You can position to a specific row in several ways.

rs.Move n
If you know that you want to start at the 3^rd row (for example) then rs.Move 2 will get you there if you are positioned on the first row when you issue that command. Move is relative to your current position.

rs.AbsolutePositioon
Use rs.AbsolutePosition = 2 to get to row 3 no matter where you are. AbsolutePosition is zero based (i.e. Record 1 is AbsolutePosition 0). You cannot use this when you open a table-type recordset and Microsoft notes that it is not recommended that you use it to position in a recordset because it can change when modifications are made to the recordset.

rs.FindFirst
If you know some unique values for the field(s) in the record that you want to start on then
rs.FindFirst "myField = " & SomeValue
will position you on the first matching record.