Oracle database login
 

I have the following code running a stored procedure against an Oracle
database; however, I want to force a user login with a password. I found if
I blank out the user id and password, the macro fails and does not prompt for
the userid and password. How can I modify this to get the macro to prompt
for these items?

Thanks!

Sub Button41_Click()
'
' Button41_Click Macro
' Macro recorded 11/24/2003
'

'Sample Database connection to an Oracle Database with 2 Input Parameters
and 1 Output Parameter, taking
'the parameters from cells in the Excel Worksheet
Dim dbConnection As New ADODB.Connection
Dim dbRecordset As New ADODB.Recordset
Dim myCurCell As Range
Dim myCurCell2 As Range
Dim myCurCell3 As Range
Dim oWorkSheet As Worksheet
Dim dbCommand As New ADODB.Command
Dim dbParameter As New ADODB.Parameter
Dim dbParameters As New ADODB.Parameter
Dim strMessage As String

On Error GoTo ErrorHandler

'Set the worksheet to pull the values from
Set oWorkSheet = ThisWorkbook.Worksheets(1)

'********Modify this range if the parameter values are actually located in
the spreadsheet itself***********
With oWorkSheet
'Grab the values from the selected cells and populate
Set myCurCell1 = .Range("C2")
Set myCurCell2 = .Range("C3")
Set myCurCell3 = .Range("C4")
Set myCurCell4 = .Range("C5")
End With

'Set the connection and open
'***************Make changes to the connection string*****************
dbConnection.Provider = "msdaora"
dbConnection.Open ("Data Source=xxxxxx;Password=xxxx;User ID=xxxxxx")


'Set the command object and the parameters
dbCommand.ActiveConnection = dbConnection

'Set the command type and name / parameters if applicable
dbCommand.CommandType = adCmdStoredProc

'***********Set the stored procedure name*****************
dbCommand.CommandText = "sp_run_sp" 'Stored Procedure Name

'Create / Append the parameters
'*********If there are no parameters you can delete the lines up to the
Execute() code************
Set dbParameter = dbCommand.CreateParameter("iCompany", adVarChar,
adParamInput, 30, myCurCell1)
dbCommand.Parameters.Append dbParameter

Set dbParameter = dbCommand.CreateParameter("ialloc_tport", adVarChar,
adParamInput, 30, myCurCell3)
dbCommand.Parameters.Append dbParameter

Set dbParameter = dbCommand.CreateParameter("iAcctDate", adDate,
adParamInput, 30, myCurCell4)
dbCommand.Parameters.Append dbParameter

Set dbRecordset = dbCommand.Execute()


'strMessage = dbCommand.Parameters(2).Value
'strMessage = dbCommand.Parameters(2).Value

'Close the Connection
dbConnection.Close

'
' Macro3 Macro
' Macro recorded 11/4/2004
'

'
Range("D5").Select
Sheets("Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Unit Costs").Select
Range("A4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Pivot").Select
Range("D5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
Sheets("Run SALESMARGIN").Select
Range("D4").Select

Exit Sub
ErrorHandler:
dbConnection.Close
Err.Raise Err.Number, Err.Source, Err.Description


End Sub

Oracle database login
 

This is one of those rare occasions when a global variable makes a lot of
sense. When I have implimented similar code I have used a login procedure at
the beginning of the program and then any time I need to login I grab the
user name and password that were populated when the program began. This
allows me to login and log back out as often as is needed without having to
constantlly ask the user. Just my two cents...
--
HTH...

Jim Thomlinson


"Tim Reid" wrote:

I have the following code running a stored procedure against an Oracle
database; however, I want to force a user login with a password. I found if
I blank out the user id and password, the macro fails and does not prompt for
the userid and password. How can I modify this to get the macro to prompt
for these items?

Thanks!

Sub Button41_Click()
'
' Button41_Click Macro
' Macro recorded 11/24/2003
'

'Sample Database connection to an Oracle Database with 2 Input Parameters
and 1 Output Parameter, taking
'the parameters from cells in the Excel Worksheet
Dim dbConnection As New ADODB.Connection
Dim dbRecordset As New ADODB.Recordset
Dim myCurCell As Range
Dim myCurCell2 As Range
Dim myCurCell3 As Range
Dim oWorkSheet As Worksheet
Dim dbCommand As New ADODB.Command
Dim dbParameter As New ADODB.Parameter
Dim dbParameters As New ADODB.Parameter
Dim strMessage As String

On Error GoTo ErrorHandler

'Set the worksheet to pull the values from
Set oWorkSheet = ThisWorkbook.Worksheets(1)

'********Modify this range if the parameter values are actually located in
the spreadsheet itself***********
With oWorkSheet
'Grab the values from the selected cells and populate
Set myCurCell1 = .Range("C2")
Set myCurCell2 = .Range("C3")
Set myCurCell3 = .Range("C4")
Set myCurCell4 = .Range("C5")
End With

'Set the connection and open
'***************Make changes to the connection string*****************
dbConnection.Provider = "msdaora"
dbConnection.Open ("Data Source=xxxxxx;Password=xxxx;User ID=xxxxxx")


'Set the command object and the parameters
dbCommand.ActiveConnection = dbConnection

'Set the command type and name / parameters if applicable
dbCommand.CommandType = adCmdStoredProc

'***********Set the stored procedure name*****************
dbCommand.CommandText = "sp_run_sp" 'Stored Procedure Name

'Create / Append the parameters
'*********If there are no parameters you can delete the lines up to the
Execute() code************
Set dbParameter = dbCommand.CreateParameter("iCompany", adVarChar,
adParamInput, 30, myCurCell1)
dbCommand.Parameters.Append dbParameter

Set dbParameter = dbCommand.CreateParameter("ialloc_tport", adVarChar,
adParamInput, 30, myCurCell3)
dbCommand.Parameters.Append dbParameter

Set dbParameter = dbCommand.CreateParameter("iAcctDate", adDate,
adParamInput, 30, myCurCell4)
dbCommand.Parameters.Append dbParameter

Set dbRecordset = dbCommand.Execute()


'strMessage = dbCommand.Parameters(2).Value
'strMessage = dbCommand.Parameters(2).Value

'Close the Connection
dbConnection.Close

'
' Macro3 Macro
' Macro recorded 11/4/2004
'

'
Range("D5").Select
Sheets("Data").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Unit Costs").Select
Range("A4").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Pivot").Select
Range("D5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
Sheets("Run SALESMARGIN").Select
Range("D4").Select

Exit Sub
ErrorHandler:
dbConnection.Close
Err.Raise Err.Number, Err.Source, Err.Description


End Sub