以Access或Excel为数据源使用ADO语句

HLAI

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 OLEDBatabase 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控件，方便文件拷贝到不同的机器上使用，而不必担心引用控件丢失的问题。