SQL automatically sorting

[Beginner]

Hi!

Does anyone know, why SQL automatically sorts when it is used to get data
from a table to another? Sorting is from 1 to z, and the GWh table is empty
when I run the sub.

Dim strSql As String
strSql = "INSERT INTO GWh(Id, Region, P_s) SELECT Id, Region, P_s FROM
Sopimustaulu;"
DoCmd.RunSQL (strSql)

The same thing happens when using two recordsets:

Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
rs.Open "Sopimustaulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs2.Open "GWh", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Do While rs.EOF = False
rs2.AddNew
rs2![Id] = rs![Id]
rs2![Region] = rs![Region]
rs2![P_s] = rs![P_s]
rs2.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing

Is there any way to turn the automatic sorting off? I'd like to use SQL for
this, because it is several seconds faster than using recordsets.

 

[Tom Wussernark [MSFT]]

no you can't turn sorting off.

it's called.. uh.. a clustered index
and you've got no control over it if you're a wimp and you use MDB

if you've got balls then use SQL Server

 

[Beginner]

Thanks for the wonderful answer. I'm afraid I don't have balls, so I cannot
use SQL Server.

no you can't turn sorting off.

it's called.. uh.. a clustered index
and you've got no control over it if you're a wimp and you use MDB

if you've got balls then use SQL Server

Hi!

Does anyone know, why SQL automatically sorts when it is used to get data
from a table to another? Sorting is from 1 to z, and the GWh table is
empty
when I run the sub.

Dim strSql As String
strSql = "INSERT INTO GWh(Id, Region, P_s) SELECT Id, Region, P_s FROM
Sopimustaulu;"
DoCmd.RunSQL (strSql)

The same thing happens when using two recordsets:

Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
rs.Open "Sopimustaulu", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs2.Open "GWh", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Do While rs.EOF = False
rs2.AddNew
rs2![Id] = rs![Id]
rs2![Region] = rs![Region]
rs2![P_s] = rs![P_s]
rs2.Update
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing

Is there any way to turn the automatic sorting off? I'd like to use SQL
for
this, because it is several seconds faster than using recordsets.

 

[John Spencer]

Probably the data is "sorted" that way because you have made the ID field
the primary key. Access usually opens the records in primary key order
unless you specify a differenct order.

I am guessing that the records are retrieved using the Primary key. If ID
is not the primary key then the records were probably written in that order
to the disk and are most efficiently retreived in the order they were
stored.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Douglas J. Steele]

You should never make any assumptions about the order of data in tables. In
a relational database, tables are "sacks of data", where the data is
physically placed where the DBMS wants to. This will be impacted by what
indexes have been created on the table, but little else.

If the order of the data is important to you, use a query with the
appropriate ORDER BY clause.

 

[Beginner]

Thank you both for the answers! I think that the best thing for me to do
is to make a query. That way I'll know that everything is where it should be..

Thanks!
-Beginner

 

[Tony Toews [MVP]]

no you can't turn sorting off.

it's called.. uh.. a clustered index
and you've got no control over it if you're a wimp and you use MDB

if you've got balls then use SQL Server

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/