Create new table sorted

[vtj]

I am using DAO coding to create new records in a table that already exists.
I am presently creating the records in the order they exist in another table
by reading the second table sequentially. This creates the new table in
random order the same as the second table. The new table is emptied before
the process. If I were to do this process in SQL I could use an ‘oder by’
clause that would put the new records in a particular order. Is there a
procedure for accomplishing the same thing in the DAO coding? I have listed
the code that I am using below:

Dim db1 As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db1 = CurrentDb()
Set rst1 = db1.OpenRecordset("SecondTable", dbOpenDynaset)
Set rst2 = db1.OpenRecordset("NewTable", dbOpenDynaset)

rst1.MoveFirst
op1:
If rst1.EOF Then GoTo opclose

rst2.AddNew
various code statements defining strings
rst2.Update
rst1.MoveNext
GoTo op1

opclose:
rst1.Close
rst2.Close
db1.Close

Thank you for any assistance.

 

[Allen Browne]

Simplest solution is to use a SQL statement for the source:

Dim strSql As String
strSql = "SELECT SecondTable.* FROM SecondTable " & _
"ORDER BY [SomeField];"
Set rst1 = db1.OpenRecordset(strSql, dbOpenDynaset)

BTW, you need to end the code with:
Set db1 = Nothing
not
dt1.Close
You set db1 to the current database, which - by definition - is already
open. Therefore you don't close it. Merely deassign your variable.

 

[Alex Dybenko]

Hi,
records are stored in a table by order of primary key, if I remember
correctly, but I have no idea why do you need this, you can easy sort table
by any field later, when you get data

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com