VB连接ACCESS数据库表时排序后无法更新到源表，求大牛，看不出问题出在哪里！

Private Sub Command1_Click()
Dim conn
Dim rs As ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=\xxgl1\xxgl.mdb"
SQL = "select 学号,姓名,数学,名次1,语文,名次2,英语,名次3,政治,名次6,历史,名次7,地理,名次8,生物,名次9 into c1lsb1 from c1lsb order by 学号"
Set rs = conn.Execute(SQL)
[b]SQL1 = "select a.学号,a.姓名,a.数学,(select count(1) from c1lsb1 where 数学>a.数学)+1 as 名次1 from c1lsb1 as a"
Set rs1 = conn.Execute(SQL1)[/b]
conn.Close
End Sub
以上语名能够运行，就是在C1lsb1中无法看到每个科目的名次
但是在ACCESS中运行SQL1中的语句就可以看到名次的结果，是不是记录表无法更新，恳请大牛出山！

用这个试一试，带入SQL语句即可