|
|
ADO，ActiveX Data Objects，ADO是专门为OLE DB而设计的，又称为OLE自动化接口，是微软的通用数据访问技术。
OLE DB则是ODBC(Open Database Connectivity，开放数据库互连),
延伸出来的COM组件，是一个便于应用的底层接口。有了ADO以后，使得OLE DB的使用变得更加简单。
使用（OLE DB Provider for ODBC） Excel与其他数据的沟通：-
- Sub data_copy() '将sheet1中的数据拷贝到一个新工作表中
- Dim lcConnectionString As String, lcCommandText As String
- Dim loADODBConnection As ADODB.Connection '定义一个ADO的Connection对象
- Dim loADODBRecordset As ADODB.Recordset '定义一个ADO的Recordset对象
-
- lcConnectionString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
- "DBQ=" + ActiveWorkbook.FullName + ";" & _
- "ReadOnly=True" '连接字符串，对于ADO对象可以执行不同种类的连接字符串，上面的连接字符串主要有两个关键参数组成：Diver和DBQ，Driver针对不同的数据库，输入数据驱动。DBQ用来输入数据库的路径。
- lcCommandText = "select * from [sheet1$]" 'SQL查询语句
- Set loADODBConnection = CreateObject("ADODB.Connection") '创建Connection对象
- Set loADODBRecordset = CreateObject("ADODB.Recordset") '创建Recordset对象
- loADODBConnection.Open lcConnectionString
- loADODBRecordset.Open lcCommandText, loADODBConnection, 3, 1, 1 'Recordset的Open方法的语法：public void open（Object source，Object ActiveConnection，int cursorType，int lockType，int options）
-
- Sheets.Add before:=Sheets(1) '插入新工作表
- Dim r, f As Integer
- r = 1 '复制字段名
- For f = 0 To loADODBRecordset.Fields.Count - 1
- Sheets(1).Cells(r, f + 1) = loADODBRecordset.Fields(f).Name 'sheets(2)表示在工作簿中排在第二位的工作表，其中(2)与工作表的名称无关，即sheets(2)并非指的是sheet2
- Next
- While Not loADODBRecordset.EOF
- r = r + 1 '逐条复制字段
- For f = 0 To loADODBRecordset.Fields.Count - 1
- Sheets(1).Cells(r, f + 1) = loADODBRecordset.Fields(f).Value
- Next
- loADODBRecordset.MoveNext '后移记录指针
- Wend
- loADODBConnection.Close
-
- Sheets(1).Cells.EntireColumn.AutoFit '设置新插入的工作表的列自动调整列宽适应列中的内容
- End Sub
ODBC Driver for Text
lcConnectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};" & "DBQ=路径\文件;" & "Extensions=asc,csv,tab,txt;" & "Persist Security info=False"
ODBC Driver for Access
lcConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & "DBQ=路径\文件.mdb;" & "Uid=Admin;" & "Pdw=;"
ODBC Driver for Oracle
lcConnectionString = "Driver={Microsoft ODBC for Oracle};" & "Server=OracleServer.world;" & "Uid=myUsername;" & "Pwd=myPassword;"
ODBC Driver for SQL Server
lcConnectionString = "Driver={SQL Server};" & "Server=MyServerName;" & "Database=MyDarabaseName;" & "Uid=myUsername;" & "Pwd=myPassword;"
ODBC Driver for dBASE
lcConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};" & "Driverid=227;" & "DBQ=路径\文件;"
ODBC Driver for mySQL
lcConnectionString = "Driver={mySQL};" & "Server=MyServerName;" & "Option=16834;" & "Database=mydb;"
由tonyibm提供：
sevip = "localhost"
Db = "mydatabase"
user = "root"
pwd = "261210"
conn.Open "DRIVER={MySql ODBC 5.1 Driver};SERVER=" & sevip & ";Database=" & Db & ";Uid=" & user & ";Pwd=" & pwd & ";Stmt=set names GBK"
ODBC Driver for Sybase
lcConnectionString = "Driver={SYBASE SYSTEM 11};" & "Srvr=MyServerName;" & "Uid=myUsername;" & "Pwd=myPassword;"
ODBC Driver for Sybase SQL Anywhere
lcConnectionString = "Driver=Sybase SQL Anywhere 5.0;" & "DefaultDir=路径\;" & "Dbf=路径\文件名.db;" & "Uid=myUsername;" & "Pwd=myPassword;" & "Dns="""";"
ODBC Driver for VisualFoxPro
lcConnectionString = "Driver={Microsoft Visual Foxpro Driver};" & "SourceType=DBC;" & "SourceDBC=路径\文件.dbc;" & "Exclusive=No;"
ODBC Driver for AS/400
lcConnectionString = "Driver={Client Access ODBC Driver (32-bit)};" & "System=myAS400;" & "Uid=myUsername;" & "Pwd=myPassword;"
ODBC Driver for Paradox
lcConnectionString = "Driver={Microsoft Paradox Driver (*.db)};" & "Driverid=538;" & "Fil=Paradox 5.X;" & "DefaultDir=路径\; & "Dbq=路径\;" & "CollatingSequence=ASCII;"
（版本不同的数据库，可能会略有不同，请参考数据库的说明。注意在VBE中设置不同的引用）
[ 本帖最后由 向東 于 2009-8-7 17:25 编辑 ]
补充内容 (2015-3-16 16:32):
'==================Access 2013连接字符串（未加密）========================================================================
lcConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=路径
补充内容 (2015-3-16 16:36):
'==================Access 2013连接字符串（未加密）===================
lcConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=路径\文件名.accdb;Jet OLEDB:Database ;"
补充内容 (2015-3-16 16:41):
在VBE中菜单栏选择【工具】-【引用】，找到：Microsoft ActiveX Data Objects 6.0 Library 打钩。
补充内容 (2023-1-28 22:19):
'==================Excel连接字符串===================
lcConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;" & "data source=" & ThisWorkbook.FullName & ";Extended Properties=Excel 12.0" |
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2009-6-1 17:21
楼主
