相同的SQL语句| 有时可以运行|有时报错|VBA或使用“现有连接”方法都出现过

autumnalRain

我在EXCLE中直接用SQL语句,运行没有问题

1. Select 序号,姓名,性别,null as 学历,年龄 from [部门A$] UNION ALL
   
2. Select 序号,姓名,性别,null as 学历,年龄 from [部门B$] UNION ALL
   
3. Select 序号,姓名,性别,学历,年龄 from [部门C$]

复制代码

但当我把它粘贴至VBA SQL中时却常报错，提示“未定义变量”……什么也没有修改……过了半小时重新打开——竟然可以运行！

1. Sub INNERJOIN()
   
2. Dim ADORST As Object
   
3. Set AdoConn = CreateObject("ADODB.Connection")
   
4. conn = "provider=microsoft.ACE.OLEDB.12.0;extended properties='Excel 12.0;hdr=yes';data source=" & ThisWorkbook.FullName
   
5. Sql = "Select 序号,姓名,性别,null as 学历,年龄 from [部门A$] UNION ALL Select 序号,姓名,性别,null as 学历,年龄 from [部门B$] UNION ALL Select 序号,姓名,性别,学历,年龄 from [部门C$]"
   
6. AdoConn.Open conn
   
7. Set ADORST = AdoConn.Execute(Sql)
   
8. For k = 0 To ADORST.Fields.Count - 1
   
9.   Sheets("全部合并").Cells(14, k + 1) = ADORST.Fields(k).Name
   
10. Next k
    
11. Sheets("全部合并").Range("A15").CopyFromRecordset ADORST
    
12. ADORST.Close
    
13. AdoConn.Close
    
14. Set ADORST = Nothing
    
15. Set AdoConn = Nothing
    
16. End Sub

复制代码

各位老师指导，这是什么原因？

autumnalRain

可惜报错的界面忘了截图，总之感觉很蹊跷。
还有EXCEL中用“数据--现有连接”方法时也经常报错，但是重开机或是过段时间就正常了。
上面的代码，一直认为是“NULL AS 学历”出错，所以删了，运行成功。只是未达到结果；等把这句又加上去，运行成功，也是想要的结果。
这就出现这种情况：一旦运行成功，再换以前报错的SQL语句也没有问题。
相同的语句，不做任何修改，有时可以运行，有时却报错，实在想不通…………………………………………………………

lzqmsy

Sub INNERJOIN()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ActiveWorkbook.FullName

Range("A2:G50").ClearContents

Sql = "Select 序号,姓名,性别,学历,年龄 from " & _
      "(Select 序号,姓名,性别,'' as 学历,年龄 from [部门A$] UNION ALL " & _
      "Select 序号,姓名,性别,'',年龄 from [部门B$] UNION ALL " & _
      "Select 序号,姓名,性别,学历,年龄 from [部门C$])"

[A2].CopyFromRecordset conn.Execute(Sql)

conn.Close
Set conn = Nothing
Application.ScreenUpdating = True
End Sub

autumnalRain

lzqmsy 发表于 2015-10-28 16:31
Sub INNERJOIN()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")

感谢老师。因为这里仅仅就是将三个表合并在一起，所以我没有用子查询。疑问还是：我的写法为什么刚开始运行不了，后来却可以运行了呢？我根本没有改动过的！请您指导！

lzqmsy

Sub INNERJOIN()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")
Set rst = CreateObject("Adodb.Recordset")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ActiveWorkbook.FullName

Range("A2:G50").ClearContents

Sql = "Select 序号,姓名,性别,学历,年龄 from " & _
      "(Select 序号,姓名,性别,'' as 学历,年龄 from [部门A$] UNION ALL " & _
      "Select 序号,姓名,性别,'',年龄 from [部门B$] UNION ALL " & _
      "Select 序号,姓名,性别,学历,年龄 from [部门C$])"
rst.Open Sql, conn, 1, 1
[A2].CopyFromRecordset rst

For i = 0 To rst.Fields.Count - 1
    Cells(1, 1 + i) = rst.Fields.Item(i).Name
Next

rst.Close: conn.Close
Set rst = Nothing: Set conn = Nothing
Application.ScreenUpdating = True
End Sub

lzqmsy

你还可以试试这个

autumnalRain

关于EXCEL中用现有连接方法运行SQL语句，显示“数据库引擎无法打开或无法写入数据他已经被其他用户以独占方式打开，或者您没有查看或写入其数据的权限”这种情况就是数据库被打开了。它目前是锁定状态，你不能对数据库做任何操作。
下面给出了办法，但不一定完全有效。================================================================================================================================
第一：在SQL中是否增加了路径试试？如果可以正确就保留路径。
第二：在“我的文档”-----“我的数据源”中会出现对应的数据库文件，可以把原来的全部删掉，再做一次会产生全新的对应的数据库文件在那个地方。
第三：请把做好的表格保存好后再打开，若刷新时再出现原来的错误信息，就找到对应的数据库文件，选中后进行多次刷新，然后再去刷新原表（不需要关闭原表）。
第四：可重复以上操作。。。。。。
===============================================================================
我按照这些办法还是无法消除错误时，惯常的做法就是：先不管它，等过段时间再重新打开。就象我的提问中VBA中用SQL的情况一样，也是先不管它，等过段时间再运行代码，就不会报错。


个中原因，实在不明白啊……

autumnalRain

有段时间我都有点想放弃使用SQL-现有链接方法，因为不断地报错，忙的时候，实在让人崩溃啊。
我现在都使用MICROSOFT QUERY连接，无非多操作两步。但至现在还没有报过错！
也算是找到了一种替代方法

autumnalRain

转字专家的例子
http://club.excelhome.net/thread-1061487-1-1.html

autumnalRain

lzqmsy 发表于 2015-10-28 17:21
Sub INNERJOIN()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")

感谢老师！现在的问题是我的SQL语句有时可以运行，有时报错。说明语句没有问题。但为什么会出现这种情况呢？