DoCmd.RunSQL Insert give weird results

[cyrusv]

Hi all,

i am trying to loop an array that i had populated with recordset.getrows() method and do an insert into a new table if certain records met a criteria, the code is working but i realised in the access table that the insert statement isnt inserting new records from the bottom, sometimes i would continue inserting records in the middle of the table, sometimes the bottom, this has messed up the sequence for the new records.

Please help! thanks!


Code:

Private Sub cmd_SortByArray_Click() ' Sort By Using Array Method

Dim db As Database
Dim rs As DAO.Recordset
' a variant is a dynamic data type
Dim data As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("tempHoldingTable", dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

'get the all the rows in record count
data = rs.GetRows(rs.RecordCount)


'Sno
Dim OriginStr1 As String
'Wire Spec
Dim OriginStr2 As String
'Origin
Dim OriginStr3 As String
'Destination
Dim OriginStr4 As String
'counter for original file
Dim OrigIntCounter As Integer

'Sno
Dim DestStr1 As String
'Wire Spec
Dim DestStr2 As String
'Origin
Dim DestStr3 As String
'Destination
Dim DestStr4 As String
'counter for original file
Dim DestIntCounter As Integer
Dim testBoolean As Boolean

For OrigIntCounter = 0 To rs.RecordCount - 1
OriginStr1 = data(0, OrigIntCounter)
OriginStr2 = data(1, OrigIntCounter)
OriginStr3 = data(2, OrigIntCounter)
OriginStr4 = data(3, OrigIntCounter)
'Disable warning
DoCmd.SetWarnings False

** I AM INSERTING THE RECORD THAT I AM COMPARING**

'insert true results
DoCmd.RunSQL "INSERT INTO [ReformattedTable] SELECT tempHoldingTable.* FROM [tempHoldingTable] WHERE Sno = " & OriginStr1 & ";"

'enable warning again
DoCmd.SetWarnings True

For DestIntCounter = 0 To rs.RecordCount - 1

DestStr1 = data(0, DestIntCounter)
DestStr2 = data(1, DestIntCounter)
DestStr3 = data(2, DestIntCounter)
DestStr4 = data(3, DestIntCounter)

** IF THERE IS IS A RECORD THAT MATCHES FROM THE REMAINDING RECS***
If OriginStr4 = DestStr3 Then

testBoolean = True


'Disable warning
DoCmd.SetWarnings False

****INSERT IT AFTER THE ORGIN RECORDS ***

'insert true results
DoCmd.RunSQL "INSERT INTO ReformattedTable SELECT tempHoldingTable.* FROM tempHoldingTable WHERE Sno = " & DestStr1 & ";"

'enable warning again
DoCmd.SetWarnings True

End If


Next DestIntCounter
'MsgBox CStr(OriginStr1) + " " + CStr(OriginStr2) + " " + CStr(OriginStr3) + " " + CStr(OriginStr4) + " ", vbOKCancel
Next OrigIntCounter
MsgBox ("Done!")
Call cmd_refreshResultsTable_Click
End Sub

 

[Galaxiom]

Tables are unordered lists. It is impossible to control where the records are inserted.

You must rely on ordering in queries, forms and reports to display them in a desired order.

 

[cyrusv]

My Result are
as you can see,

record 1's destination is record 124's origin thats why i place it beneath records one..

but out of the blue record 64 got brought up to the top...


is it because DoCmd.RunSQL got some sort of bug or technical loop hole?

Sno Wire Spec Origin Destination
1 LFF 0.75 K \BT-1 \IP-19
124 LFF 0.75K \IP-19 8D-MB-1
2 LFF 0.75 K \BT-3 \IP-20
125 LFF 0.75K \IP-20 8D-MB-2
64UL 18 Y 21E-+19 AC-P1
65 UL 18 Y 21E--10 BA-E1
194UL 18 Y 10BA-E1 \XJ-8
66 UL 18 Y 21E-1- 17C-P1

 

[cyrusv]

Tables are unordered lists. It is impossible to control where the records are inserted.

You must rely on ordering in queries, forms and reports to display them in a desired order.

Hi thanks for the prompt reply, i thought access tables when being inserted it will always start from the bottom?? as in like first come first serve

eg..
Record 1 to Record 100,

as i loop through 1 to 100,

Insert 1
Insert 2
Insert 3
...
...
...

till


Insert 100?

 

[Galaxiom]

Despite appearances tables are not spreadsheets. The data is held in "pages" of records and Access puts the new records wherever it finds convenient.

Records in a table have no intrinsic order or numbering. You must include a field for the purpose if you want them ordered.

Generally if you compact the database they will appear at the bottom until you delete some. But don't count on it. One day you will compact it and they will be completely shuffled.

Tables only store records. Absolutely everything about display is done elswhere. Even when you apply an order to the datasheet view of the table they appear in order because you are actually looking at a built in query rather than the table.

 

[cyrusv]

i am trying to sort a table that has 3 columns.

Sno Origin Destination

if record 1 destination is QQ
All records that Origins from QQ would be inserted below Record 1 in a new table
And if Record 5 starts from QQ and ends at TT and has records that orginate from TT would be places below Record 5

 

[cyrusv]

Despite appearances tables are not spreadsheets. The data is held in "pages" of records and Access puts the new records wherever it finds convenient.

Records in a table have no intrinsic order or numbering. You must include a field for the purpose if you want them ordered.

Generally if you compact the database they will appear at the bottom until you delete some. But don't count on it. One day you will compact it and they will be completely shuffled.

Tables only store records. Absolutely everything about display is done elswhere. Even when you apply an order to the datasheet view of the table they appear in order because you are actually looking at a built in query rather than the table.

Would you have any suggestion in where i could place the data that has a follow a FIFO approach to something similar to a insert statement? perhaps a new record set? or a direct write to a excel file?

 

[Galaxiom]

All you need to do is Order By Destination in the RecordSource query when you want to display them in a form or report.

There is no such thing as Record1 unless you assign that to a field in the record.

 

[Galaxiom]

Maybe you need to sequentially number the records or include a DateTime field. Then you can order them as required however they are stored.

They can be appended to a recordset and will stay in the order they are added. But this does not mean any table the recordset is derived from will maintain that order. As soon as that recordset is closed that ordering evaporates.

You can write to a recordset then export that recordset to a spreadsheet and the order will be maintained. However rather than trying to juggle the appends to get the order right, the recordset can be efficiently ordered after it is populated. Just needs that ordering sequence or DateTime field.

 

[Galaxiom]

Looking back at post #3, now it has line breaks I can see it was a representation of your table. You just need to order by SNo whenever you want records in order.

 

[cyrusv]

Maybe you need to sequentially number the records or include a DateTime field. Then you can order them as required however they are stored.

They can be appended to a recordset and will stay in the order they are added. But this does not mean any table the recordset is derived from will maintain that order. As soon as that recordset is closed that ordering evaporates.

You can write to a recordset then export that recordset to a spreadsheet and the order will be maintained. However rather than trying to juggle the appends to get the order right, the recordset can be efficiently ordered after it is populated. Just needs that ordering sequence or DateTime field.

same feelings, i think a date time field would suffice, but still there should be a more efficient way, because i need to remove the date time field when i export it out to the excel file. the excel file will be fed to a industrial program for use..

could you explain more on the using the recordset? when you mention order efficiently, its by AbsolutePosition? or ?

 

[cyrusv]

Looking back at post #3, now it has line breaks I can see it was a representation of your table. You just need to order by SNo whenever you want records in order.

i cannot order by Sno, it will render my previous effort to sort the rows in a specific manner to either the default ascending or descending

 

[Galaxiom]

I see. Your record order is being created in your procedure.

You could add a field to the table to store an incrementing variable in the append so that the records could be ordered when you export from the table. The Order By field does not have to be included in the output. Just untick the Show box in the query designer when you create the export query.

Alternatively, instead of appending to the table, add the records to another recordset. The recordset could be based on an empty table. Simply replace your append query with an AddNew to the recordset.

With rs
.AddNew
!somefield = whatever
!anotherfield = something
.Update
End With

These records will maintain the appended order but only in the recordset. Then export this recordset directly to Excel without involving the table and then empty the table.

Personally I would use a disconnected ADO recordset. Basically you create an blank ADO recordset that has no connection object, append the fields, then add the records just as with the DAO recordset.

This is faster because it doesn't involve a table hence avoiding disk activity. It also avoids the bloating that happens with temporary tables.

 

[cyrusv]

Thank You Very Much, Would try it out! keep you posted! =)

 

[cyrusv]

i tried doing the recordset method, but the recordset isnt in the correct order when i used a array to check it's data, specifically using the recordset.getrow(recordset.recordcount) method, could you supplement me with codes that can write the recordset to a excel file too?

 

[gemma-the-husky]

cyrus

in databases you just CANNOT rely on an order position in a table.

You muct have some details in a table that uniquely define a record. Tell us what columns you have in your table, and an example of what some rows of data look like.

Now, in terms of copying data to another table, yes, you can do this, but you probably shouldn't have to. You just use a select query which picks out the rows you need in the main table, and use that.

Are you new to databases. The hard thing is to stop thiniking of a database, as a spreadsheet.

 

[cyrusv]

Hi Husky,

Thanks for your reply, i understand that access table is just a means of storage and the order that is represented on display is mostly achieve through the order by clause in the select query.

i have attached a copy of my test data please take a look :

what i am trying to achieve is to sort the records based on the following criteria;

Sno Origin Destination
1 A1 B1
2 A2 B2
3 B1 C1
4 B2 C2
5 C1 D1
6 C2 D2


INTO the sequence where if there is any record's origin is equal to the destination of the current record, it would be moved to below the current record's position


Sno Origin Destination
1 A1 B1
2 B1 C1
3 C1 D1
4 A2 B2
5 B2 C2
6 C2 D2

 

[Galaxiom]

i tried doing the recordset method, but the recordset isnt in the correct order when i used a array to check it's data,

Maybe I have been lucky. When I have appended to empty recordsets the records always stayed in order. I use disconnected ADO recordsets but I would have expected the DAO to be the same.

Morover what would be the point of being able to Order a recordset if it could not be relied on when you tried to extract the records?

Did you definitely take data directly from the recordset you had been adding records to? Remember you can't open a new recordset against the table because it won't be ordered.

I use the CopyFromRecordset method in Excel.

 

[cyrusv]

I used the following code to sort and got the attached results, look at record 256 and 257, there was a break in sequential order yes?
it resumed at 281 and 282..


CODE:


Private Sub cmd_Sort_Click() ' uses recordset as a way to sort the data and insert into ReformattedTable

Dim rs As DAO.Recordset
Dim db As Database
Dim intCount As Integer
''strings used to store value
Dim previousStr As String
Dim currentStr As String

On Error GoTo cmdAddSmall_Err

''sets current databse to an object
Set db = CurrentDb()
''initiate the record set
Set rs = db.OpenRecordset("tempHoldingTable", dbOpenDynaset) 'dbOpenDynaset
'check if bookmarkable to true
Dim boo As Boolean
boo = rs.Bookmarkable


'initial record count
'intCount = rs.RecordCount

Dim currentCounter As Integer
currentCounter = 0
''populates recordset
'move to last record
rs.MoveLast
'Move to first record
rs.MoveFirst

If rs.RecordCount <> 0 Then

'check if records isnt last line
'Do While rs.EOF = False
Do Until rs.EOF

'set the bookmark record
varbookmark = rs.Bookmark

'current record, should be the first record
previousStr = rs(3)
If rs.EOF = False Then

'Disable warning
DoCmd.SetWarnings False
'insert statement
DoCmd.RunSQL "INSERT INTO [ReformattedTable] SELECT * FROM [tempHoldingTable] WHERE Sno = " & rs(0) & ";"
'enable warning again
DoCmd.SetWarnings True

Else
'exit Do statement
Exit Do

End If
'loops through remainding records
' Do While rs.EOF = False
Do Until rs.EOF
rs.movenext

If rs.EOF = False Then

If previousStr = rs(2) Then

'Dim Sno As Integer
'Sno = rs(0)
'Disable warning
DoCmd.SetWarnings False
''Deletes the Record
'rs.Delete

'insert true results
DoCmd.RunSQL "INSERT INTO [ReformattedTable] SELECT * FROM [tempHoldingTable] WHERE Sno = " & rs(0) & ";"

'enable warning again
DoCmd.SetWarnings True

Else


End If

Else

'exit Do statement
Exit Do

End If

'adds a counter
'currentCounter = currentCounter + 1

Loop

'move to first record so that program can go to bookmark but still stop at end of file
rs.MoveFirst
If rs.EOF = False Then

rs.Bookmark = varbookmark
'Start from next line
rs.movenext

Else
'exit Do statement
Exit Do

End If

Loop
End If

MsgBox "Table is sorted, Continue With Exporting It!"

cmdAddSmall_End:
On Error GoTo 0

Exit Sub
cmdAddSmall_Err:
Beep
MsgBox Err.Description
Resume cmdAddSmall_End
rs.Close

End Sub

 

[cyrusv]

Maybe I have been lucky. When I have appended to empty recordsets the records always stayed in order. I use disconnected ADO recordsets but I would have expected the DAO to be the same.

Morover what would be the point of being able to Order a recordset if it could not be relied on when you tried to extract the records?

Did you definitely take data directly from the recordset you had been adding records to? Remember you can't open a new recordset against the table because it won't be ordered.

I use the CopyFromRecordset method in Excel.

hi!,

i created a empty table called FinalTable and opened a dao recordset by doing this:

''sets current databse to an object
Set db = CurrentDb()

''initiate the record set
Set rs = db.OpenRecordset("tempHoldingTable", dbOpenDynaset) 'dbOpenDynaset

''rsNew is the empty recordset.
Set rsNew = db.OpenRecordset("FinalTable")


i used the following to add to the empty recordset rsNew:

With rsNew

.AddNew
rsNew!Sno = rs(0)
rsNew![Wire Spec] = rs(1)
rsNew!Origin = rs(2)
rsNew!Destination = rs(3)
rsNew!F5 = rs(4)
rsNew!F6 = rs(5)
rsNew!F7 = rs(6)
rsNew!F8 = rs(7)
rsNew!F9 = rs(8)
rsNew!F10 = rs(9)
rsNew!F11 = rs(10)
rsNew!F12 = rs(11)
rsNew!F13 = rs(12)
rsNew!F14 = rs(13)
rsNew!F15 = rs(14)
rsNew!F16 = rs(15)
rsNew!F17 = rs(16)
rsNew!F18 = rs(17)
rsNew!F19 = rs(18)
rsNew!F20 = rs(19)
'rsNew(20) = rs(20)

.Update

End With

and i used the array to see the values:

dim newArr as variant
rsNew.MoveFirst
newArr = rsNew.GetRows(rsNew.RecordCount)