I’m trying to recreate an entire table stored in MS Access to a Notes View. Sounds easy right? (sure!) I’m using an ODBCResultSet class with ‘NextRow’ to loop until ‘IsEndOfData’ to get all the rows. I am also using a nested For…Next Loop to get the ‘NumColumns’ in each row and then using a ‘ReplaceItemValue’. to write those column values of each row to the document before looping to the ‘NextRow’.
So what happens is, I know there are appox 10,000 rows in the MS Access Table. The script however, only creates 2570. Now here is the good part: when I use different methods to loop through the rows, I get different results. For Example, when I use a “Do…Loop Until result.IsEndOfData”, I get 2570 every time. But if I use something like “While Not result.IsEndOfData…Wend”, I’ll get like 3400. Even better, if do something like Do…Loop Until resilt.NextRow = False, it will seem to loop forever. Any Ideas anybody!?!
Set qry.Connection = con
Set result.Query = qry
If con.ConnectTo("ODBCsource", "USER", "pass") Then
qry.SQL = "SELECT * FROM THETABLE"
result.Execute
Else
Messagebox "Connection to the OBDC Data Source Failed!" ,1,"Error"
Exit Sub
End If
counter=0
' Build the view by adding all the documents
Do
Set doc = db.CreateDocument
doc.Form = "MyForm"
result.NextRow
’ This loop adds fields named n1,n2,n3, etc. for each column until no more columns
For i = 1 To result.NumColumns
var = result.GetValue(i)
Set item = doc.ReplaceItemValue("n"&i, var)
Next
Call doc.Save(True,True)
counter= counter+1
Print counter & " documents created."
Loop Until result.IsEndOfData
result.Close(DB_CLOSE)
con.Disconnect
Subject: ODBC Results…what the heck is going on?! (IsEndOFData and NextRow)
Wish I knew the answer. I resorted to using multiple passes through the data, first selecting records where the first column starts with A, then with B and so on. Seldom if ever in talking to Access was I able to get all the data in one pass.
Subject: RE: ODBC Results…what the heck is going on?! (IsEndOFData and NextRow)
I had a similar problem importing from Visual FoxPro. I set these parameters in order to get past row 10022:
Set qry.Connection = con
' The following 2 lines to attempt to get more than 10022 rows from dbf file
'set the record cache limit to unlimited
result.CacheLimit = DB_NONE
'set the record limit to unlimited
result.MaxRows = 0
Set result.Query = qry
qry.SQL = "SELECT * FROM webcontributor"
result.Execute
If result.IsResultSetAvailable Then
result.NextRow....
Subject: ODBC Results…what the heck is going on?! (IsEndOFData and NextRow)
Well, this has been answered before in the R4/R5-forum. The resultset isn’t able to get the complete result before the code continues and therefore these strange things can happen.
The solution is to move the cursor to the last position and then back again before looping through the resultset:
Subject: RE: ODBC Results…what the heck is going on?! (IsEndOFData and NextRow)
I don’t believe “MoveLast” or “MoveFirst” are LS methods, so I used result.LastRow and result.FirstRow as you suggested, and had it prompt me with some column values for the “LastRow”. The prompt shows that it is not moving to the last row, but to around the 2500th row. When I look at the table in MS Access there are 9009 rows.
result.Execute
result.LastRow
var = result.GetValue("WO_NUM") & " " & result.GetValue (7)
box = Messagebox ( var, 1,"Result Last Row")
If box = 2 Then
Exit Sub
End If
result.FirstRow
' Rebuild the view by adding all the documents again
Do
Set doc = db.CreateDocument
doc.Form = "TWO"
For i = 1 To result.NumColumns
var = result.GetValue(i)
Set item = doc.ReplaceItemValue("n"&i, var)
Next
Call doc.Save(True,True)
result.NextRow
ctr= ctr+1
Print ctr & " documents created."
Loop Until result.IsEndOfData