很古老的问题，但是没找到答案的。。ADO连接的问题
一下代码可以完完全全的正确执行。
但是有个瑕疵，就是没有关闭数据库连接对象ADODB.Connection
无论怎么我都不能再本模块关闭。。。。
除非，我重新定义一个ADODB.CONNECTION，然后关闭。。。
那位高手来试试嘛，咋个整。
Dim cnn As New ADODB.Connection
Dim sql2 As String
Dim rst As New ADODB.Recordset
Dim rs As ADODB.Recordset
Public Sub inaccess()
' 智能纠错，设计当中------------
' For i = 2 To 13
'Range("a1") = Range("b4:b5").SpecialCells(xlCellTypeBlanks).Count
Err.Description
Stpath = ThisWorkbook.Path & "\alldata.mdb"
cnn.Open "provider=Microsoft.jet.OLEDB.4.0;data source=" & Stpath & ";Jet.OLEDB:"
If ActiveSheet.Index = Application.Worksheets.Item(2).Index Then
acrow = Range("d4").End(xlDown).Row
sql2 = "INSERT INTO 材料入库 SELECT * FROM [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";].[" & ActiveSheet.Name & "$b3:m" & acrow & "]; "
Else
acrow2 = Sheet3.Range("d4").End(xlDown).Row
sql2 = "INSERT INTO 本期领用 SELECT * FROM [Excel 8.0;DATABASE=" & ThisWorkbook.FullName & ";].[" & ActiveSheet.Name & "$b3:m" & acrow2 & "]; "
End If
cnn.Execute sql2
cnn.Close
End Sub
Public Sub 查询()
spath = ThisWorkbook.Path & "\alldata.mdb"
sql = "select * from 材料入库"
Set rst = ExecuteSQL(sql)
Range("b4").CopyFromRecordset rst
End Sub
Public Function ExecuteSQL(ByVal sql As String) As ADODB.Recordset
Dim cn As ADODB.Connection, connstring As String
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Stpath = ThisWorkbook.Path & "\alldata.mdb"
connstring = "provider=Microsoft.jet.OLEDB.4.0;data source=" & Stpath & ";Jet.OLEDB:"
cn.ConnectionString = connstring
cn.Open
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rs
End Function
Public Sub execut(sql As String)
Set cn = New ADODB.Connection
Stpath = ThisWorkbook.Path & "\alldata.mdb"
connstring = "provider=Microsoft.jet.OLEDB.4.0;data source=" & Stpath & ";Jet.OLEDB:"
cn.ConnectionString = connstring
cn.Open
cn.Execute sql
cn.Close
End Sub
[解决办法]
Public Sub execut(sql As String,cn as ADODB.Connection) cn.Execute sqlEnd Sub
