Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Queries to Oracle from Excel 2000
Greetings! I have a number of ODBC queries in Access that I run against an
Oracle database. I later use the result set in Excel. I would like to run these queries directly from Excel and save the whole Access step so that the process is more seamless for my enduser. Would someone please point me to some primer on this kind of data access programming in Excel VBA. I'm very familiar with VBA, but this is new territory for me and I need a good place to start. I have several books that I have consulted, but I need something more specific to what I am trying to do. Thanks in advance! -- Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Queries to Oracle from Excel 2000
Try this, it worked the last time I used it:
Sub GetFlowCalGQSourceList() Dim strUserName As String Dim strPassword As String Dim strDatabaseName As String Dim strProviderName As String Dim strConnString As String Dim Cnn As New ADODB.Connection Dim SQL_Text As String Dim CurrentRS As ADODB.Recordset Dim i As Integer Dim j As Integer strProviderName = "OraOLEDB.Oracle" strDatabaseName = "your server name" strUserName = "your user ID" strPassword = "your user password" strConnString = "Provider=" & strProviderName & ";" & _ "Data Source=" & strDatabaseName & ";" & _ "User ID=" & strUserName & ";" & _ "Password=" & strPassword Cnn = strConnString Cnn.Open Set CurrentRS = New ADODB.Recordset SQL_Text = "This is your SQL for the query" CurrentRS.Open SQL_Text, Cnn, adOpenStatic 'Do with your recordset what you will CurrentRS.Close Set CurrentRS = Nothing Cnn.Close Set Cnn = Nothing End Sub You'll probably want to turn on the latest version of "Microsoft ActiveX Data Objects X.X Library" in the VBE Tools\References dialog - it won't work without it. Regards, VBA Dabbler "Kevin" wrote: Greetings! I have a number of ODBC queries in Access that I run against an Oracle database. I later use the result set in Excel. I would like to run these queries directly from Excel and save the whole Access step so that the process is more seamless for my enduser. Would someone please point me to some primer on this kind of data access programming in Excel VBA. I'm very familiar with VBA, but this is new territory for me and I need a good place to start. I have several books that I have consulted, but I need something more specific to what I am trying to do. Thanks in advance! -- Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Queries to Oracle from Excel 2000
Thanks VBA Dabbler,
Is it possible to pass a list of values to the sql string from a range of cells in excel (to filter) "VBA Dabbler" wrote: Try this, it worked the last time I used it: Sub GetFlowCalGQSourceList() Dim strUserName As String Dim strPassword As String Dim strDatabaseName As String Dim strProviderName As String Dim strConnString As String Dim Cnn As New ADODB.Connection Dim SQL_Text As String Dim CurrentRS As ADODB.Recordset Dim i As Integer Dim j As Integer strProviderName = "OraOLEDB.Oracle" strDatabaseName = "your server name" strUserName = "your user ID" strPassword = "your user password" strConnString = "Provider=" & strProviderName & ";" & _ "Data Source=" & strDatabaseName & ";" & _ "User ID=" & strUserName & ";" & _ "Password=" & strPassword Cnn = strConnString Cnn.Open Set CurrentRS = New ADODB.Recordset SQL_Text = "This is your SQL for the query" CurrentRS.Open SQL_Text, Cnn, adOpenStatic 'Do with your recordset what you will CurrentRS.Close Set CurrentRS = Nothing Cnn.Close Set Cnn = Nothing End Sub You'll probably want to turn on the latest version of "Microsoft ActiveX Data Objects X.X Library" in the VBE Tools\References dialog - it won't work without it. Regards, VBA Dabbler "Kevin" wrote: Greetings! I have a number of ODBC queries in Access that I run against an Oracle database. I later use the result set in Excel. I would like to run these queries directly from Excel and save the whole Access step so that the process is more seamless for my enduser. Would someone please point me to some primer on this kind of data access programming in Excel VBA. I'm very familiar with VBA, but this is new territory for me and I need a good place to start. I have several books that I have consulted, but I need something more specific to what I am trying to do. Thanks in advance! -- Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running Queries to Oracle from Excel 2000
Yes. The sql string is just that, a string. You can concatenate variables
into the string to make a dynamic string. You could also drive the string value from a userform, if you like - I've done it many times. "codecom" wrote: Thanks VBA Dabbler, Is it possible to pass a list of values to the sql string from a range of cells in excel (to filter) "VBA Dabbler" wrote: Try this, it worked the last time I used it: Sub GetFlowCalGQSourceList() Dim strUserName As String Dim strPassword As String Dim strDatabaseName As String Dim strProviderName As String Dim strConnString As String Dim Cnn As New ADODB.Connection Dim SQL_Text As String Dim CurrentRS As ADODB.Recordset Dim i As Integer Dim j As Integer strProviderName = "OraOLEDB.Oracle" strDatabaseName = "your server name" strUserName = "your user ID" strPassword = "your user password" strConnString = "Provider=" & strProviderName & ";" & _ "Data Source=" & strDatabaseName & ";" & _ "User ID=" & strUserName & ";" & _ "Password=" & strPassword Cnn = strConnString Cnn.Open Set CurrentRS = New ADODB.Recordset SQL_Text = "This is your SQL for the query" CurrentRS.Open SQL_Text, Cnn, adOpenStatic 'Do with your recordset what you will CurrentRS.Close Set CurrentRS = Nothing Cnn.Close Set Cnn = Nothing End Sub You'll probably want to turn on the latest version of "Microsoft ActiveX Data Objects X.X Library" in the VBE Tools\References dialog - it won't work without it. Regards, VBA Dabbler "Kevin" wrote: Greetings! I have a number of ODBC queries in Access that I run against an Oracle database. I later use the result set in Excel. I would like to run these queries directly from Excel and save the whole Access step so that the process is more seamless for my enduser. Would someone please point me to some primer on this kind of data access programming in Excel VBA. I'm very familiar with VBA, but this is new territory for me and I need a good place to start. I have several books that I have consulted, but I need something more specific to what I am trying to do. Thanks in advance! -- Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open all Excel spreadsheets/fles before running queries | Excel Discussion (Misc queries) | |||
Running Access queries from Excel | Excel Programming | |||
External Queries - Excel 2000 | Excel Programming | |||
Running MS Queries | Excel Programming | |||
Editing Queries in Excel 2000 | Excel Programming |