如何用ADO连Excel? 如何用ADO连Excel? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 不清楚,不过可以引用Excel 对象库来读取excel中的数据,比较方便 使用OLE DB来连接数据库的例子。1) OLE DB Provider for Active Directory Service<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=ADSDSOObject;" & _ "User Id=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>2) OLE DB Provider for DB2<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=DB2OLEDB;" & "Network Transport Library=TCPIP;" & "Network Address=MyServer;" & _ "Package Collection=MyPackage;" & "Host CCSID=1142" "Initial Catalog=MyDB;" & "User ID=MyUsername;" & _"Password=MyPassword;" oConn.Open strConn%>3) OLE DB Provider for Index Server<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=msidxs;" & _ "Data source=MyCatalog;"oConn.Open strConn%>4) OLE DB Provider for Internet Publishing<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”) StrConn= "Provider=MSDAIPP.DSO;" & _ "Data Source=http://mywebsite/myDir;" & _ "User Id=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>5) OLE DB Provider for Microsoft Jet● 标准的也是常用的访问方式。 <%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\myDb.mdb;" & _ "User Id=admin;" & _ "Password=;"oConn.Open strConn%>● 假如是一个工作组的系统数据库,那么连接字符串如下。<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\mydb.mdb;" & _ "Jet OLEDB:System Database=MySystem.mdw;", _ "admin", ""oConn.Open strConn%>注意:在使用4.0 OLE DB Provider 提供者的驱动程序时,要记得把MDB和MDW转换成the 4.0 database格式。● 如果MDB是设置密码的话,那么使用如下连接字符串。oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\mydb.mdb;" & _ "Jet OLEDB:Database Password=MyDbPassword;", _ "admin", ""● 如果MDB是位于网络中并且是共享的话,那么使用如下连接字符串。oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=\\myServer\myShare\myPath\myDb.mdb;● 如果要想使用专有的方式来访问数据库,那么使用如下连接字符串。oConn.Mode = adModeShareExclusiveoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\myDb.mdb;" & _ "User Id=admin;Password=;"6)使用OLE DB Provider for Microsoft Jet来访问Excel电子数据表。<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\myExcelSpreadsheet.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;"";"oConn.Open strConn%>说明:这里的HDR=Yes的意思是,提供者访问Excel电子数据表时没有把第一排的数据加入到数据集中;而当HDR=No时的意思是提供者访问Excel电子数据表时动作恰好跟上面所述相反。7)OLE DB Provider for ODBC Databases● 访问MS Access数据库<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=MSDASQL;" & _ "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>● 访问MS SQL Server 数据库<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=MSDASQL;" & _ "Driver={SQL Server};" & _ "Server=myServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>8) OLE DB Provider for Oracle (from Microsoft)<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=msdaora;" & _ "Data Source=MyOracleDB;" & _ "User Id=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>9) OLE DB Provider for Oracle (from Oracle)<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=OraOLEDB.Oracle;" & _ "Data Source=MyOracleDB;" & _ "User Id=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>10) OLE DB Provider for SQL Server● 标准连接方式<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=sqloledb;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User Id=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>● 通过机器的IP地址访问<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=sqloledb;" & _ "Data Source=xxx.xxx.xxx.xxx,1433;" & _ "Network Library=DBMSSOCN;" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword;"oConn.Open strConn%>说明:IP:xxx.xxx.xxx.xxx SQLServer默认端口:1433附录1:ADO数据对象及其功能简介ADO数据对象 功能 .Connection 代表与一个数据源的唯一对话 .Command 用Command对象,你可以执行带参数的存储过程,SQL查询,SQL语句。你可以使用Command对象接收Recordset对象。 .RecordSet 用于代表一数据库表。 .Error 该对象包含所有的错误和警告信息。该对象只具有属性值。 .Field Field对象代表在数据集中的某一列。 .Parameter Parameter对象用于提供要参数的SQL查询或存储过程所需的参数,或者从存储过程中返回值。 .Property 代表数据提供者的具体属性。 利用数据库驱动程序直接访问数据库的连接字符串。1) ODBC Driver for Access● 标准的也是比较常用的连接方法<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=”&Server.MapPath(“Testdb.mdb”); & _ "Uid=AdminAccount;" & _ "Pwd=Password;"oConn.Open strConn%>● 假如是一个工作组的系统数据库,那么连接字符串如下<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\datapath\Testdb.mdb;" & _ "SystemDB=c:\datapath\Testdb.mdw;", _ "admin", ""oConn.Open strConn%>● 假如数据库(MDB)是网络上共享的,那么连接字符串如下<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=\\myServer\myShare\myPath\Testdb.mdb;"oConn.Open strConn%>2) ODBC Driver for dBASE<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={Microsoft dBASE Driver (*.dbf)};" & _ "DriverID=277;" & _ "Dbq=c:\FilePath;"oConn.Open strConn%>说明:这里要注意的一点就是,在SQL查询语句中要特别指定数据库文件名,例如:oRs.Open "Select * From Testdb.dbf", oConn, , ,adCmdText3) ODBC Driver for Excel<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={Microsoft Excel Driver (*.xls)};" & _ "DriverId=790;" & _ "Dbq=c:\filepath\myExecl.xls;" oConn.Open strConn%>4) ODBC Driver for MySQL (通过 MyODBC驱动程序)● 连接到本地数据库(local database)<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={mySQL};" & _ "Server=ServerName;" & _ "Option=16834;" & _ "Database=mydb;"oConn.Open strConn%>● 连接远程数据库(remote databas)<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={mySQL};Server=db1.database.com;Port=3306;" & _"Option=131072;Stmt=;Database=mydb;Uid=myUsername;Pwd=myPassword;"oConn.Open strConn%>5) ODBC Driver for Oracle<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={Microsoft ODBC for Oracle};" & _ "Server=OracleServer.world;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>6) ODBC Driver for Paradox<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={Microsoft Paradox Driver (*.db)};" & _ "DriverID=538;" & _ "Fil=Paradox 5.X;" & _ "DefaultDir=c:\dbpath\;" & _ "Dbq=c:\dbpath\;" & _ "CollatingSequence=ASCII;"oConn.Open strConn%>7) ODBC Driver for SQL Server<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn="Driver={SQL Server};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>8) ODBC Driver for Sybase (通过Sybase System 11 ODBC Driver驱动程序)<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Driver={SYBASE SYSTEM 11};" & _ "Srvr=myServerName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>9) ODBC Driver for Sybase SQL Anywhere<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _ "DefaultDir=c:\dbpath\;" & _ "Dbf=c:\sqlany50\mydb.db;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;" "Dsn="""";"oConn.Open strConn%>10) ODBC Driver for Teradata<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Provider=Teradata;" & _ "DBCName=MyDbcName;" & _ "Database=MyDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword;"oConn.Open strConn%>11) ODBC Driver for Text<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "Dbq=c:\somepath\;" & _ "Extensions=asc,csv,tab,txt;" & _ "Persist Security Info=False"oConn.Open strConn%>12) ODBC Driver for Visual FoxPro●使用数据库容器(database container)连接方式<%Dim oConn,strConnSet oConn=Server.CreateObject(“ADODB.Connection”)StrConn= "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBC;" & _ "SourceDB=c:\somepath\mySourceDb.dbc;" & _ "Exclusive=No;"oConn.Open strConn%>● 不使用数据库容器(database container)连接方式(即Free Table Directory方式) <% <% Dim oConn,strConn Set oConn=Server.CreateObject(“ADODB.Connection”) StrConn= "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBF;" & _ "SourceDB=c:\somepath\mySourceDbFolder;" & _ "Exclusive=No;" oConn.Open strConn %> “VBMath”不是“VisualBasic”的成员??? VB中调用VC写的控件,背景色有杂物 如何判断一字符串在一个数组中 VB中操作EXCEL,单元格赋值失败的问题,谁有办法解决? datagrid中如何实现排序 DTPicker1的问题 关于QQ超大彩色文字与RTF格式的问题。。。 关于SQL的问题。请指教 承接上一问题 !!!!!!如何在VB中重建系统图标缓存!!!!!!!! 关于智能升级的问题 十万火急,installshield6.22,大家帮帮忙,先谢了,在线
功能
.Connection
代表与一个数据源的唯一对话
.Command
用Command对象,你可以执行带参数的存储过程,SQL查询,SQL语句。你可以使用Command对象接收Recordset对象。
.RecordSet
用于代表一数据库表。
.Error
该对象包含所有的错误和警告信息。该对象只具有属性值。
.Field
Field对象代表在数据集中的某一列。
.Parameter
Parameter对象用于提供要参数的SQL查询或存储过程所需的参数,或者从存储过程中返回值。
.Property
代表数据提供者的具体属性。