Access（VBA）連接Oracle數(shù)據(jù)庫的代碼

access是包含前颱程序開髮和後颱數(shù)據(jù)庫的應(yīng)用軟件。

可以利用access作爲(wèi)前颱開髮，後颱用sqlServer，oracle這些作爲(wèi)數(shù)據(jù)庫。

而access連接數(shù)據(jù)庫，可以利用鏈接錶。直接讀取錶記録。也可以通過ADO等數(shù)據(jù)操作方式讀取數(shù)據(jù)庫

方法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=服務(wù)器名;provider=OraOLEDB.Oracle;user id=用戶;password=密碼"
    Sql = "Select 字段名 from 錶名 where ID=1"
    rs.Open SQL, Cnn
  rs.Close
  Set Cnn = Nothing
  Set rs = Nothing