Yes, it's starting to make a little more sense. Is there anything I
have to
do on the Oracle side to get this to work?
Thanks....AGAIN!
:
Check this website and explore the site and ALL of the links.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdreforacleprovspec.asp
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
:
That's probably what I'm looking for...but, my experience with
doing this
type of connection is NIL!!! Can you expain the following:
- The DBDataProvider I use is: MSDAORA
- The DBDataSource is the TNSNAMES.ora file entry specific to the
database
you are querying.
What exactly is DBDataProvider and DBDataSource? How do I know
these?
Thanks again!
:
I use a variation of this:
First, in separate named cells I have the following:
DestWkshtName
DestRngStartCell:=[DestStartCellRef].Value, _
QryTableName
DataProvider
Sql
I also use a Form to prompt the user for UserName and Password if
they
haven't already been entered. (The password is stored in a static
variable
that is destroyed upon workbook closure)
This code performs the actual query:
**************************
Option Explicit
Global prmUsername As String
Global prmPassword As String
Sub GetDataFromDatabase( _
strDestWkshtName As String, _
strDestRngStartCell As String, _
strQryTableName As String, _
strDataProvider As String, _
strSql As String, _
strTNSNAME_entry As String, _
strUserName As String, _
strPwd As String)
Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
Dim strConnString As String
Dim intCounter As Integer
Dim strNewEntry As String
Dim qtbQTbl As QueryTable
Dim thing As Variant
'Clear previous data from the destination range
Range(strDestRngStartCell) _
.Offset(RowOffset:=1, ColumnOffset:=0) _
.CurrentRegion _
.ClearContents
'Delete the Data Destination Range Name
'so it can be replaced later in the process
With ThisWorkbook.Worksheets(strDestWkshtName)
If .QueryTables.Count <> 0 Then
For Each qtbQTbl In .QueryTables
If qtbQTbl.Name = strQryTableName Then
On Error Resume Next
.Range(strQryTableName).ClearContents
On Error Resume Next
qtbQTbl.Delete
On Error Resume Next
.Names(strQryTableName).Delete
On Error GoTo 0
End If
Next qtbQTbl
End If
End With
adoConn.Provider = strDataProvider
adoConn.Properties("Data Source").Value = strTNSNAME_entry
adoConn.Properties("User ID").Value = strUserName
adoConn.Properties("Password").Value = strPwd
adoConn.Open
adoRS.Open strSql, adoConn
With ThisWorkbook.Worksheets(strDestWkshtName) _
.QueryTables.Add( _
Connection:=adoRS, _
Destination:=Range(strDestRngStartCell))
.Name = strQryTableName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
adoRS.Close
adoConn.Close
Set adoConn = Nothing
Set adoRS = Nothing
End Sub
'------end of code---------
This code exerpt engages the query code:
************************************
'Run the query
GetDataFromDatabase _
strDestWkshtName:=[DestWkstName].Value, _
strDestRngStartCell:=[DestStartCellRef].Value, _
strQryTableName:=[DestDataRangeName].Value, _
strDataProvider:=[DBDataProvider].Value, _
strSql:=[SQLCode].Value, _
strTNSNAME_entry:=[DBDataSource].Value, _
strUserName:=UserName, _
strPwd:=Pwd
'------end of code---------
The values in square brackets are the named ranges I referred to
above
The DBDataProvider I use is: MSDAORA
The DBtaSource is the TNSNAMES.ora file entry specific to the
database you
are querying.
The above has been exerpted and edited from a complete model I
use that
holds many queries (MS Access and Oracle) that are selected via
option
buttons and then executed.
Does that give you something to work with?
***********
Regards,
Ron
XL2002, WinXP-Pro
:
I am very experienced with Excel VBA...so, don't hold back on
any VBA coding
responses ;-)
What I am NOT familiar with, is connecting to Oracle. In
particular, I want
to use a value in a Cell in Excel to query an Oracle Database.
Can someone give me directions on what I need to do? If there
is more than
one way of doing this...I'd like to hear them all (ADO, ODBC,
OLE, or
whatever).
Thanks in advance!!!!
