Access（VBA）連接Oracle數據庫的代碼

access是包含前臺程序開發(fā)和后臺數據庫的應用軟件。

可以利用access作為前臺開發(fā)，后臺用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