[已解决]代码报错，请高手解决一下

chengzhiting · 发表于 2013-11-3 20:19

本帖最后由 chengzhiting 于 2013-11-3 21:14 编辑

点击按钮3后就报错，不知道是什么原因，麻烦请解决下，谢谢

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-11-3 20:46

Sub delete()
Dim cnn As ADODB.Connection
Dim rs As Recordset
Dim strsql As String
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
'ws.Cells.clear
Set cnn = New ADODB.Connection
Set rs = New Recordset
cnn = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" & ThisWorkbook.Path & Application.PathSeparator & "AccessData.accdb"
cnn.Open
strsql = "delete * from table1 where table1.month=" & ActiveSheet.[yyyymm]
Set rs = cnn.Execute(strsql)
End Sub

复制代码

跳转到最佳答案楼层

1091126096 · 发表于 2013-11-3 20:24

本帖最后由 1091126096 于 2013-11-3 20:35 编辑

看看 cnn = "Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\Users\Administrator\Desktop\AccessData.accdb"
好像是这句代码有问题。

chengzhiting · 发表于 2013-11-3 20:40

1091126096 发表于 2013-11-3 20:24
看看 cnn = "Provider=Microsoft.ACE.OLEDB.12.0; data source=C:\Users\Administrator\Desktop\AccessD ...

什么问题啊？

hwc2ycy · 发表于 2013-11-3 20:46

这个最佳答案由 hwc2ycy 给出，感谢 hwc2ycy 的回答。

单击隐藏图章

Sub delete()
Dim cnn As ADODB.Connection
Dim rs As Recordset
Dim strsql As String
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
'ws.Cells.clear
Set cnn = New ADODB.Connection
Set rs = New Recordset
cnn = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" & ThisWorkbook.Path & Application.PathSeparator & "AccessData.accdb"
cnn.Open
strsql = "delete * from table1 where table1.month=" & ActiveSheet.[yyyymm]
Set rs = cnn.Execute(strsql)
End Sub

复制代码

hwc2ycy · 发表于 2013-11-3 20:47

引用文件的时候，最好不要使用决定路径，一切以代码所在的工作簿为基准。
不然你文件挪个位置又要改代码。

hwc2ycy · 发表于 2013-11-3 20:51

查询语句忘了是日期类型了。
strsql = "delete * from table1 where table1.month=#" & ActiveSheet.[yyyymm] & "#"

hwc2ycy · 发表于 2013-11-3 20:53

Sub conn()
Dim cnn As ADODB.Connection
Dim rs As Recordset
Dim strsql As String
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Set ws = ThisWorkbook.ActiveSheet
'ws.Cells.clear
Set cnn = New ADODB.Connection
Set rs = New Recordset
cnn = "Provider=Microsoft.ACE.OLEDB.12.0; data source=" & ThisWorkbook.Path & Application.PathSeparator & "AccessData.accdb"
cnn.Open
strsql = "select [省份],[终端指标],[实际金额],[实际数量] from ProvinceSales where [省份] like '%" & Range("H1") & "%'"
Set rs = cnn.Execute(strsql)
'复制字段名
For i = 0 To rs.Fields.Count - 1
ws.Cells(1, i + 1) = rs.Fields(i).Name
Next i
'复制全部数据
ws.Range("a2").CopyFromRecordset rs
rs.Close
cnn.Close
End Sub

复制代码

hwc2ycy · 发表于 2013-11-3 20:54

Book1.rar (32.19 KB, 下载次数: 7)

chengzhiting · 发表于 2013-11-3 21:17

hwc2ycy 发表于 2013-11-3 20:51
查询语句忘了是日期类型了。
strsql = "delete * from table1 where table1.month=#" & ActiveSheet.[yyyy ...

搞定了，谢谢老师

		自动登录	找回密码
密码			注册