ODBC Connection to Oracle - prompting for Server Name

D

dmcgimpsey

Active Member
Joined
Mar 30, 2004
Messages
268
Hi Folks:

I have a VBA script that communicates with Oracle - when I click on the refresh button, a dialog box pops up and asks for the Server Name .... I was wondering if I can pass this variable to the connection instead of having the users have to enter it each time - the problem is that they likely don't even know what the server name is.

Thanks

Don

Query is below:


If global_username = "" Then Call password_entry

strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) "
strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
strConOracle = strConOracle & " ))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"

Set oConOracle = CreateObject("ADODB.Connection")
qstr = " Select * from query_result "

oConOracle.Open strConOracle

splitsql = SplitString(qstr)

' On Error Resume Next

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;" & oConOracle
.CommandType = xlCmdSql
.CommandText = splitsql
.CreatePivotTable TableDestination:= _
"'[ABCHAMP_resp_prof_JUL10 v17.xls]MainMenu'!R23C3", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Is that a variable called HOST being inserted into the connection string? Is that where the name of the server goes? What's the value of the variable HOST when you create the connection?
 
Upvote 0
it contains the correct parameter for the HOST connection. This problem seems to happen when I pass the record set to Excel Pivot table, but does not prompt me at other points.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Retrieve data from Oracle DB using VBA Excel containing Japanese Character
Replies
0
Views
12K
abadilala
A
D
  • Question
Connect Excel to SQL developer without client installed
Replies
6
Views
2K
Dhiya
D
R
  • Question
VBA- Excel to Oracle DB Connection Issue
Replies
1
Views
2K
jimrward
jimrward
M
  • Question
Is ADODB.Connection comaptible with oracle 11g?
Replies
1
Views
3K
mouni
M
B
  • Question
VBA-ODBC(Oracle) connection poblem
Replies
0
Views
1K
Biraj
B

Forum statistics

Threads
1,214,872
Messages
6,122,025
Members
449,060
Latest member
LinusJE

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top