求教各位老大！在EXCEL中操作覆盖Access台帐的代码

=if

我想在EXCEL中操作覆盖Access台帐
我在EXCEL中将Access中证书编号相同的找出来，再依照ID号的大小，保留一个最大的，删除小的。

zhangjimfu

1. Select * From sheet1 Where ID in(Select List From(
   
2. SELECT a.证书编号,Last(ID) as List
   
3. FROM sheet1 AS a
   
4. WHERE (Select Count(*) From sheet1 as b Where b.证书编号=a.证书编号)>1
   
5. GROUP BY a.证书编号));

复制代码

SQL代码供参考

zhangjimfu

1. Sub quarySQL()
   
2. 
   
3.     Dim rst, cnn, strSql$
   
4.     strSql = "Select * From sheet1 Where ID in(Select List From(SELECT a.证书编号,Last(ID) as List FROM sheet1 AS a WHERE (Select Count(*) From sheet1 as b Where b.证书编号=a.证书编号)>1 GROUP BY a.证书编号))"
   
5.     Set rst = CreateObject("ADODB.Recordset")
   
6.     Set cnn = CreateObject("ADODB.Connection")
   
7.    
   
8.     cnn.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & ThisWorkbook.Path & "\db2.mdb"
   
9.     Set rst = cnn.Execute(strSql)
   
10.     Range("a5").CopyFromRecordset rst
    
11.    
    
12.     cnn.Close
    
13.     Set cnn = Nothing
    
14.     Set rst = Nothing
    
15.    
    
16. End Sub

复制代码

ExcelVBA代码供参考！