Dim cn As New ADODB.Connectioncn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=liuyf;password=liuyf;Data Source=lyf" cn.Open
Public Function connectDB(ByRef roraDatabase As OraDatabase) As Integer Dim loraSession As New OracleInProcServer.OraSessionClass 'creat oraSession Set loraSession = CreateObject("OracleInProcServer.XOraSession")
On Error GoTo TranErr 'creat roraDatabase Set roraDatabase = loraSession.OpenDatabase(CST_DATABASE_ID, CST_DATABASE_USER & "/" & CST_DATABASE_PASSWORD, 0&) If roraDatabase.ConnectionOK = True Then connectDB = 0 Else connectDB = -1 End If
Exit Function TranErr:
connectDB = -1 End Function
非常感谢,大概明白了,我想顺便再问一下,如何用程序创建一个oracle的数据源?
清风大虾: CST_DATABASE_ID 的值该怎么给啊?举个例子好吗?
Public Function GetAdoCon() As ADODB.Connection Dim objAdoCon As New ADODB.Connection Dim AdoRs As New ADODB.Recordset Dim strOdbcSubKey As String Dim strDsnName As String Dim strUserName As String Dim strPassWord As String Dim strCommandTimeOut As String 'CommandTimeOut Dim strConnectionTimeOut As String 'ConnectionTimeOut Dim blnRet As Boolean Const METHODNAME = "GetAdoCon" Application.ScreenUpdating = False '' Sheets("Parameter_Sheet").Select
cn.Open
Dim loraSession As New OracleInProcServer.OraSessionClass
'creat oraSession
Set loraSession = CreateObject("OracleInProcServer.XOraSession")
On Error GoTo TranErr
'creat roraDatabase
Set roraDatabase = loraSession.OpenDatabase(CST_DATABASE_ID, CST_DATABASE_USER & "/" & CST_DATABASE_PASSWORD, 0&) If roraDatabase.ConnectionOK = True Then
connectDB = 0
Else
connectDB = -1
End If
Exit Function
TranErr:
connectDB = -1
End Function
CST_DATABASE_ID 的值该怎么给啊?举个例子好吗?
Dim AdoRs As New ADODB.Recordset
Dim strOdbcSubKey As String
Dim strDsnName As String
Dim strUserName As String
Dim strPassWord As String
Dim strCommandTimeOut As String 'CommandTimeOut
Dim strConnectionTimeOut As String 'ConnectionTimeOut
Dim blnRet As Boolean
Const METHODNAME = "GetAdoCon"
Application.ScreenUpdating = False
'' Sheets("Parameter_Sheet").Select
'(DSN)
strDsnName = Sheets("Parameter_Sheet").Cells(3, 3).Value ' DataSourceName
'(strUserName)
strUserName = Sheets("Parameter_Sheet").Cells(4, 3).Value ' Oracle8 User Id
'(strPassWord)
strPassWord = Sheets("Parameter_Sheet").Cells(5, 3).Value ' Oracle8 Password '(TimeOut)
strCommandTimeOut = "3600" 'defalt value
'(ConTimeOut)
strConnectionTimeOut = "3600" 'defalt value
'ADO timeout setting
If Trim(strCommandTimeOut) <> "" Then
objAdoCon.CommandTimeOut = CLng(Trim(strCommandTimeOut))
End If
If Trim(strConnectionTimeOut) <> "" Then
objAdoCon.ConnectionTimeOut = CLng(Trim(strConnectionTimeOut))
End If
'' Sheets("MAIN_Sheet").Select
Application.ScreenUpdating = True
objAdoCon.Open strDsnName, strUserName, strPassWord
AdoRs.Open "alter session set NLS_DATE_FORMAT = 'yyyy/mm/dd'", objAdoCon, adOpenForwardOnly, adLockPessimistic, adCmdText
Set GetAdoCon = objAdoCon
End Function上面是用EXCEL的VBA做的一个用ADO连接后台数据库