Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calls to extract data from an open database
I have a workbook that on the open event connects to an Oracle database
using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calls to extract data from an open database
The usual way to handle this is have a Public or Private ADO connection
object so you can set that up once and keep it alive till you don't need it anymore. So for example: Option Explicit Public oADOConn As ADODB.Connection Sub OpenConnection(strConnString As String, _ strUserName As String, _ strPassWord As String) If oADOConn Is Nothing Then Set oADOConn = New ADODB.Connection End If If oADOConn.State = 0 Then oADOConn.Open strConnString, strUserName, strPassWord End If End Sub RBS "Spike" wrote in message ... I have a workbook that on the open event connects to an Oracle database using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calls to extract data from an open database
Thank you for that. Does the connection stay open to the workbook until it
is closed; so the while the connection is open one can run a completely different macro and then run a macro that will extract data from the database. If so is the code something like as below or does one have to provide user name and password every time? With oADOConn .CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START, PB_RATES.MAT_BIN_END," _ & "PB_RATES.MAT_RATE_LOAN" _ & " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _ & " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})" ..CommandType = adCmdText Set RS = New ADODB.Recordset RS.CursorType = adOpenStatic RS.LockType = adLockReadOnly Set RS.Source = CM bolDataBaseErr = True 'changed to false if opens RS correctly RS.Open RS.MoveFirst Etc etc -- with kind regards Spike "RB Smissaert" wrote: The usual way to handle this is have a Public or Private ADO connection object so you can set that up once and keep it alive till you don't need it anymore. So for example: Option Explicit Public oADOConn As ADODB.Connection Sub OpenConnection(strConnString As String, _ strUserName As String, _ strPassWord As String) If oADOConn Is Nothing Then Set oADOConn = New ADODB.Connection End If If oADOConn.State = 0 Then oADOConn.Open strConnString, strUserName, strPassWord End If End Sub RBS "Spike" wrote in message ... I have a workbook that on the open event connects to an Oracle database using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calls to extract data from an open database
Does the connection stay open to the workbook until it is closed?
Yes, unless you close it actively with oADOConn.Close or Set oADOConn = Nothing or if you close the Workbook. If so is the code something like as below? Yes, connection has been set up once already and stays alive. RBS "Spike" wrote in message ... Thank you for that. Does the connection stay open to the workbook until it is closed; so the while the connection is open one can run a completely different macro and then run a macro that will extract data from the database. If so is the code something like as below or does one have to provide user name and password every time? With oADOConn .CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START, PB_RATES.MAT_BIN_END," _ & "PB_RATES.MAT_RATE_LOAN" _ & " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _ & " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})" .CommandType = adCmdText Set RS = New ADODB.Recordset RS.CursorType = adOpenStatic RS.LockType = adLockReadOnly Set RS.Source = CM bolDataBaseErr = True 'changed to false if opens RS correctly RS.Open RS.MoveFirst Etc etc -- with kind regards Spike "RB Smissaert" wrote: The usual way to handle this is have a Public or Private ADO connection object so you can set that up once and keep it alive till you don't need it anymore. So for example: Option Explicit Public oADOConn As ADODB.Connection Sub OpenConnection(strConnString As String, _ strUserName As String, _ strPassWord As String) If oADOConn Is Nothing Then Set oADOConn = New ADODB.Connection End If If oADOConn.State = 0 Then oADOConn.Open strConnString, strUserName, strPassWord End If End Sub RBS "Spike" wrote in message ... I have a workbook that on the open event connects to an Oracle database using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calls to extract data from an open database
thank you very much for that i will code it up tomorrow. i am most grateful.
-- with kind regards Spike "RB Smissaert" wrote: Does the connection stay open to the workbook until it is closed? Yes, unless you close it actively with oADOConn.Close or Set oADOConn = Nothing or if you close the Workbook. If so is the code something like as below? Yes, connection has been set up once already and stays alive. RBS "Spike" wrote in message ... Thank you for that. Does the connection stay open to the workbook until it is closed; so the while the connection is open one can run a completely different macro and then run a macro that will extract data from the database. If so is the code something like as below or does one have to provide user name and password every time? With oADOConn .CommandText = "SELECT PB_RATES.COB_DATE, PB_RATES.MAT_BIN_START, PB_RATES.MAT_BIN_END," _ & "PB_RATES.MAT_RATE_LOAN" _ & " FROM OPS$ORA_ADMIN.PB_RATES PB_RATES" _ & " WHERE (PB_RATES.COB_DATE={ts '" & strRateDate & "'})" .CommandType = adCmdText Set RS = New ADODB.Recordset RS.CursorType = adOpenStatic RS.LockType = adLockReadOnly Set RS.Source = CM bolDataBaseErr = True 'changed to false if opens RS correctly RS.Open RS.MoveFirst Etc etc -- with kind regards Spike "RB Smissaert" wrote: The usual way to handle this is have a Public or Private ADO connection object so you can set that up once and keep it alive till you don't need it anymore. So for example: Option Explicit Public oADOConn As ADODB.Connection Sub OpenConnection(strConnString As String, _ strUserName As String, _ strPassWord As String) If oADOConn Is Nothing Then Set oADOConn = New ADODB.Connection End If If oADOConn.State = 0 Then oADOConn.Open strConnString, strUserName, strPassWord End If End Sub RBS "Spike" wrote in message ... I have a workbook that on the open event connects to an Oracle database using ADO, this works fine. I need to code various calls to the data base to run several queries. How do I achieve this? Do I have to repeat the ADO connection string to the database complete with password etc; I am sure this is not necessary. Any code will be very gratefully received. -- with kind regards Spike . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract data from another database | Excel Worksheet Functions | |||
Extract data from database | Excel Worksheet Functions | |||
HELP: VBA Code to extract data from an SQL Server database and put it into Access Database | Excel Programming | |||
database calls | Excel Programming | |||
database calls | Excel Programming |