数据库连接
Dim adoConn As New ADODB.Connection
Dim adoReco As New ADODB.Recordset
Set adoConn = New ADODB.Connection
'OLE DB + ODBC Driver 方式:
'adoConn .Open "driver=Microsoft Excel Driver受能力(*.xls);DBQ=C:\1.xls"
'Microsoft.Jet.OLEDB.4.0 方式,(建议)
adoConn .Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
数据库建立
'创建任意名及工作表名的EXCEL文件
adoConn .Execute "Create table [C:\1.xls].Sheet1" & "(" & Sql & ")" 如Sql= "Num int, Name char(20)"
数据集访问
Set adoReco = New ADODB.recordset
adoReco .Open "Select * from [sheet1$]", connExcel, adOpenDynamic, adLockOptimistic
数据插入,更新,删除
'必须以Microsoft.Jet.OLEDB.4.0 方式连接数据库才支持INSERT,UPDATA!
adoConn .Execute "insert into [sheet1$] (....) values (....)"
adoConn .Execute "UPDATE [sheet1$] Set ....=... Where ..."
adoConn .Execute "delete * from [sheet1$] where ...."
数据库关闭
adoReco .Close
Set adoReco = Nothing
adoConn .Close
Set adoConn = Nothing
Dim adoConn As New ADODB.Connection
Dim adoReco As New ADODB.Recordset
Set adoConn = New ADODB.Connection
'OLE DB + ODBC Driver 方式:
'adoConn .Open "driver=Microsoft Excel Driver受能力(*.xls);DBQ=C:\1.xls"
'Microsoft.Jet.OLEDB.4.0 方式,(建议)
adoConn .Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
数据库建立
'创建任意名及工作表名的EXCEL文件
adoConn .Execute "Create table [C:\1.xls].Sheet1" & "(" & Sql & ")" 如Sql= "Num int, Name char(20)"
数据集访问
Set adoReco = New ADODB.recordset
adoReco .Open "Select * from [sheet1$]", connExcel, adOpenDynamic, adLockOptimistic
数据插入,更新,删除
'必须以Microsoft.Jet.OLEDB.4.0 方式连接数据库才支持INSERT,UPDATA!
adoConn .Execute "insert into [sheet1$] (....) values (....)"
adoConn .Execute "UPDATE [sheet1$] Set ....=... Where ..."
adoConn .Execute "delete * from [sheet1$] where ...."
数据库关闭
adoReco .Close
Set adoReco = Nothing
adoConn .Close
Set adoConn = Nothing