Access（VBA）连接Oracle数据库的代码

access是包含前台程序开发和后台数据库的应用软件。

可以利用access作为前台开发，后台用sqlServer，oracle这些作为数据库。

而access连接数据库，可以利用链接表。直接读取表记录。也可以通过ADO等数据操作方式读取数据库

方法1:odbc连接方式

      Dim OracleDatabase As String
        OracleDatabase = "DSN=ODBC源名称;UID=你的用户名;PWD=你的密码;LANGUAGE=us_english; DATABASE=NOKIAKPI"
        Set conn = CreateObject("adodb.connection")
        conn.Open OracleDatabase
        If Err.Number <> 0 Then
            MsgBox ("Please Check connection to KPI Server!")
            DoCmd.Quit
        Else    

            KPITableName = "TEMPKB" & Format(Date, "ddhh24") & Format(Time, "hhss")
            LocalTable = rstrDesTbl
            '----------fetch table from oracle db-------------------
            SQL = "create table " & KPITableName & " as " & rstrSrcTbl    'nokiaadmin
            conn.CommandTimeout = 300
            conn.Execute (SQL)
            DoCmd.DeleteObject acTable, LocalTable
            '   KPITableName = "kpiview." & KPITableName
            DoCmd.TransferDatabase acTable, "ODBC Database", "ODBC;" & OracleDatabase, acTable, KPITableName, LocalTable
            conn.Execute ("DROP TABLE " & KPITableName & ";")

        End If
        Exit Function

方法2：oledb

Dim Cnn As Object
    Set Cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.recordset")
     rs.cursorlocation = 3
  cnn.Open "data source=服务器名;provider=OraOLEDB.Oracle;user id=用户;password=密码"
    Sql = "Select 字段名 from 表名 where ID=1"
    rs.Open SQL, Cnn
  rs.Close
  Set Cnn = Nothing
  Set rs = Nothing