Office中国论坛/Access中国论坛

标题: 以Access或Excel为数据源使用ADO语句 [打印本页]

作者: HLAI 时间: 2005-9-2 23:05
标题: 以Access或Excel为数据源使用ADO语句
Sub ADO_Excel()'以Excel为数据源使用ADO语句

Set cnnExcel = CreateObject("Adodb.Connection")

Set rstAnswers = CreateObject("Adodb.Recordset")

cnnExcel.Open "

rovider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName

strSQL = "Select * From [Data$A

] "

rstAnswers.Open strSQL, cnnExcel, 1, 3 'adOpenKeyset, adLockOptimistic

rstAnswers.Close

Set rstAnswers = Nothing

Set cnnExcel = Nothing

End Sub

Sub ADO_Access()'以Access为数据源使用ADO语句

Application.ScreenUpdating = False

Set cnnAccess = CreateObject("Adodb.Connection")

Set rstAnswers = CreateObject("Adodb.Recordset")

Stpath = ThisWorkbook.Path & Application.PathSeparator & "Data.mdb" '设定路径

cnnAccess.Provider = "Microsoft.Jet.OLEDB.4.0"

cnnAccess.Open "Data Source =" & Stpath & ";Jet OLEDB

atabase Password=" & ""

strSQL = "Select * From MyData"

rstAnswers.Open strSQL, cnnAccess, 1, 3

rstAnswers.Close

Set rstAnswers = Nothing

Set cnnAccess = Nothing

End Sub

应用上面语句时，最好定义cnn和rst为Object。用上面语句读Access时，可以不用引用ActiveX控件，方便文件拷贝到不同的机器上使用，而不必担心引用控件丢失的问题。

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)