viutv晚吹系列:Excel OLE DB Provider for ODBC、SQL Server、Oracle、Microsoft Jet 连接字符串 - ExcelVBA程序开发 - Excel Home论坛

来源：百度文库 编辑：偶看新闻 时间：2024/07/03 13:24:51

LE DB Provider for ODBC、SQL Server、Oracle、Microsoft Jet 连接字符串

ADO，ActiveX Data Objects，ADO是专门为OLE DB而设计的，又称为OLE自动化接口，是微软的通用数据访问技术。
OLE DB则是ODBC(Open Database Connectivity，开放数据库互连),
延伸出来的COM组件，是一个便于应用的底层接口。有了ADO以后，使得OLE DB的使用变得更加简单。

odbc.jpg (24.23 KB)
2009-8-7 17:25
使用（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，ObjectActiveConnection，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常用的OLE DB Provider for ODBC连接字符串：
ODBC Driver for Text
lcConnectionString = "Driver={Microsoft Text Driver (*.txt;*.csv)};"& "DBQ=路径\文件;" & "Extensions=asc,csv,tab,txt;" & "PersistSecurity 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;"
Microsoft OLE DB Provide for ODBC Drivers 错误 8007000 ole db 灾难性故障 ole db数据源在那里建立？ 关于OLE DB的问题!!急 ODBC与OLE DB的区别 .udl与OLE DB的关系 创建基于ole db的连接是什么意思。 请问Jet4.0 OLE DB Provider是什么? 建站点中的OLE DB连接失败. Microsoft OLE DB Provider for ODBC Drivers (0x80040E4E) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for SQL Server 求救~~ 为什么我的WIN2003里找不到OLE DB驱动 Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Service Components 错误 '8000ffff' Microsoft OLE DB Service Components 错误 '8000ffff' Microsoft OLE DB Provider for ODBC Drivers (0x80040E2F) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) Microsoft OLE DB Provider for ODBC Drivers (0x80004005)