关于使用VBA连接SQL数据库获取数据时创建临时表的问题

Yaojiaqing

使用VBA连接SQL数据库获取数据时，在SQL语句中涉及创建及查询临时表的过程，结果均为：
运行时错误'3704'：
对象关闭时，不允许操作。
请问该如何解决？万分感谢！！

代码如下
[code=vb]Private Sub Yao()
    Dim i%, strCn$, strSQL$, serIP$, uid$, pwd$, dbName$, mydate, sht As Worksheet
    Dim cn As Object   
    Dim rs As Object   
    Dim stime As Date, etime As Date
    stime = Timer
    serIP = "10.1.1.1"
    uid = "sa"
    pwd = "123"
    dbName = "sa"
   
    Set cn = CreateObject("ADODB.Connection")  
    Set rs = CreateObject("ADODB.RecordSet")
    strCn = "Provider=sqloledb;Server=" & serIP & ";Database=" & dbName & ";Uid=" & uid & ";Pwd=" & pwd & "; "   
    mydate = Date
    strSQL = "if object_id('tempdb..#shops') is not null drop table #shops; select b.sno,b.areaid,b.areaname,a.shopid,a.sname into #shops from areashops a,areadept b where"
    strSQL = strSQL + "a.arealevel=b.arealevel select * from #shops"
    cn.Open strCn
    cn.CommandTimeout = 720
    rs.Open strSQL, cn
   
    Set sht = ThisWorkbook.Sheets("Sheet1")
    sht.[a2:i50000].ClearContents
    sht.[a2:i50000].NumberFormatLocal = "@"
    sht.[a2].CopyFromRecordset cn.Execute(strSQL)  


    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
[/code]

amazeyeli

if object_id('tempdb..#shops') is not null drop table #shops;
go
select b.sno,b.areaid,b.areaname,a.shopid,a.sname into #shops from areashops a,areadept b where a.arealevel=b.arealevel;
go
select * from #shops;
加个go试试

Yaojiaqing

amazeyeli 发表于 2013-1-23 18:26
if object_id('tempdb..#shops') is not null drop table #shops;
go
select b.sno,b.areaid,b.areaname ...

    strSQL = "if object_id('tempdb..#shops') is not null drop table #shops;"
    strSQL = strSQL + " go "
    strSQL = strSQL + "select b.sno,b.areaid,b.areaname,a.shopid,a.sname into #shops from areashops a,areadept b where a.arealevel=b.arealevel;"
    strSQL = strSQL + " go "
    strSQL = strSQL + "select * from #shops;"


是这样子吗？   这样的话语法错误

amazeyeli

vba不懂,不过你可以试下一条一条累加下去执行,看哪条出错了

Yaojiaqing

amazeyeli 发表于 2013-1-24 09:28
vba不懂,不过你可以试下一条一条累加下去执行,看哪条出错了

是运行到
sht.[a2].CopyFromRecordset cn.Execute(strSQL)  时返回如下错误：

amazeyeli

VBA不懂,期待高手解答

Yaojiaqing

自己顶一下。期待高手解答！

鄙人阿ben

顶下,然后坐等高手出现……

Yaojiaqing

再顶一下~继续等待。。。

Yaojiaqing

求大神赐教！！！