End Sub该范例在 OpenSchema 方法的 Criteria 参数中指定 TABLE_TYPE 查询约束。因此,只返回在 Pubs 数据库中指定的 Views 的纲要信息。然后该范例显示这些表的名称和类型。Public Sub OpenSchemaX2() Dim cnn2 As ADODB.Connection Dim rstSchema As ADODB.Recordset Dim strCnn As String
Set cnn2 = New ADODB.Connection strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " cnn2.Open strCnn
在VB6中应用ADO导出EXCEL文件代码: Dim cn As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"-------------------------------------------------------------------------------------------------
Dim oRS As New ADODB.Recordset
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS.Open "Select * from [Sheet1$]", oConn, adOpenStatic '打开Sheet1,也
可以是定义的名称,或一个Range。
..
..
..
oRS.Close
oConn.Close
参考:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q278/9/73.asp&NoWebContent=1
dim adoConnection as new adodn.connection
adoConnection.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=sa;Initial Catalog=YourDataBaseName;Data Source=YourServerName"
'注意字段类型的匹配、一致、兼容
'adoConnection.Execute "SELECT * INTO YourTableName FROM
OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver
(*.xls);DBQ=e:\temp\book2.xls','select * from [sheet1$]')"
'可在 SQL Server Query Analyzer 中执行 SQL:
' SELECT * INTO YourTableName FROM OPENROWSET('MSDASQL.1',
'driver=Microsoft Excel Driver (*.xls);DBQ=e:\temp\book2.xls','select * from
[sheet1$]')
adoConnection.Execute "INSERT INTO YourTableName SELECT * FROM
OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver
(*.xls);DBQ=e:\temp\book2.xls','select * from [sheet1$]')"
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub该范例在 OpenSchema 方法的 Criteria 参数中指定 TABLE_TYPE 查询约束。因此,只返回在 Pubs 数据库中指定的 Views 的纲要信息。然后该范例显示这些表的名称和类型。Public Sub OpenSchemaX2() Dim cnn2 As ADODB.Connection
Dim rstSchema As ADODB.Recordset
Dim strCnn As String
Set cnn2 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn2.Open strCnn
Set rstSchema = cnn2.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "VIEW")) Do Until rstSchema.EOF
Debug.Print "Table name: " & _
rstSchema!TABLE_NAME & vbCr & _
"Table type: " & rstSchema!TABLE_TYPE & vbCr
rstSchema.MoveNext
Loop
rstSchema.Close
cnn2.Close
End Sub