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
0
Utf
4/19/2010 6:10:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
846 Views

Similar Articles

[PageSpeed] 50

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" <Spike@discussions.microsoft.com> wrote in message 
news:D7EE54B2-88F2-46BE-B80B-492F20D0615B@microsoft.com...
>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 

0
RB
4/19/2010 6:42:25 PM
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" <Spike@discussions.microsoft.com> wrote in message 
> news:D7EE54B2-88F2-46BE-B80B-492F20D0615B@microsoft.com...
> >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 
> 
> .
> 
0
Utf
4/19/2010 8:01:01 PM
> 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" <Spike@discussions.microsoft.com> wrote in message 
news:30087751-AAF3-482E-86A9-3D535C32AFB3@microsoft.com...
> 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" <Spike@discussions.microsoft.com> wrote in message
>> news:D7EE54B2-88F2-46BE-B80B-492F20D0615B@microsoft.com...
>> >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
>>
>> .
>> 

0
RB
4/19/2010 8:28:53 PM
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" <Spike@discussions.microsoft.com> wrote in message 
> news:30087751-AAF3-482E-86A9-3D535C32AFB3@microsoft.com...
> > 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" <Spike@discussions.microsoft.com> wrote in message
> >> news:D7EE54B2-88F2-46BE-B80B-492F20D0615B@microsoft.com...
> >> >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
> >>
> >> .
> >> 
> 
> .
> 
0
Utf
4/19/2010 9:23:01 PM
Reply:

Similar Artilces:

Validating Data on a Form with a subForm
I have a form with subforms with data validation in the main Form's Before_Update Event. The problem that I am encountering is when the user has not updated all required data but enters the subform the Form Before_Update Event fires and the validation will fail. Is there a way to know what the next object has been selected might be or how I would not perform the Form's validation when the subform has been selected? Any help will be appreciated!!! No. Access saves the main form's record at the point when you move from the main form into the subform. Any record that fails v...

coin collecting database
A coin collecting database that will allow pictures to be added. On Sun, 15 Nov 2009 05:23:01 -0800, U_R_sum_1_too <U_R_sum_1_too@discussions.microsoft.com> wrote: >A coin collecting database that will allow pictures to be added. You're talking to unpaid human volunteers, not to a search engine. Such a database can certainly be developed, but you're probably not going to find one in this newsgroup. Try going to http://www.bing.com or http://www.google.com and search for "coin collection database" or "numismatism database" to see if anyone has...

Extract phone number front block of text
I have a webpage that lists business names, address, phone number, etc. There is no definite pattern to how they entered the text. What I' like to do is something like.... -Search cell A1 for "-", return the 3 characters to the left of th "-".- In the next column do... -Search cell A1 for "-", return the 4 characters to the right of th "-".- Then I can concatenate the two and add in the area code. TIA guys/gals. PS. I tried searching but didn't know exactly what to search for an didn't get very far -- Tec ------------------------...

Biztalk or Scribe for our data integration?
We recently started using CRM 3.0 and the time has come to decide on a data migration plan. So far I've looked at Scribe and it seems promising but my higher-ups are suggesting we take a look at BizTalk. Eventually we will need to pull (two way integration will be required with at least two legacy systems) data from several legacy systems including flat files, SQL Server and a Progress database to name a few. The possibility also exists that we'll want to accept Word, Excel and HTML documents from customers and have information they contain added to our database. Is BizTalk cap...

Data value display attributes linked to table attributes
Is there a way to cause data value points to take on attributes such as bold, italic or color set at the table from which they are derived? For example, suppose I was charting hits at several web sites as function of time, and the some characteristic of the site was being altered back and forth (say font "A" and font "B") as part of an experiment. I can easily produce a line graph x=hits y=time for the various sites, but these is no way to visualize which condition (A or B) was in effect at each data value point. I'd like to be able to display the portion of each li...

Office apps won't open, report Office in use by another use
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel A few days ago, our Office applications started showing the report that "An Office program is being used by "user." your installation exceeds the number of installations permitted by thelicense agreement. Click More Information to learn about Microsoft Office licensing." But OFFICE ISN'T BEING USED BY ANYONE. All computers have been restarted to no avail. HELP! <sakas@officeformac.com> wrote: > A few days ago, our Office applications started showing the report that > "An Office p...

Business Contact Manager 2003 Database Update
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C3962C.16F7ECA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear All, I have what I believe a serious problem here. I have been using Outlook = 2003 with Business Contact Manager when it was in Beta 2 and even after = the Technical Refresh. Now, after both are released, I need to use the = BCM database that I have populated. The problem is that the database = cannot be backed up and then restored to the RTM BCM. It keeps telling = me that the version is differen...

Opening Email Program in IE8
When I try to open my email program with the envelope icon on my IE8 toolbar I get the error message: cannot perform this operation because the default email client is not properly installed. This was working fine and suddenly stopped. How can I correct? Always state your full Windows version (e.g., WinXP SP3; Vista SP1; Vista 64-bit SP2; Win7; Win7 64-bit) when posting in a forum or newsgroup. Please do so in your next reply. Also state your default Mail Client (e.g., Outlook Express; Windows Mail; Outlook; Thunderbird; Windows Live Mail; Yahoo Mail). -- ~Robear Dyer (PA...

Need to extrcat data from one Excel file to an other
Hi there I'm looking form a solution to press on button in a Excel sheet which ill exctract some datas from an other Excel sheet which can be close or opened. It should beworing on Windows XP, 2003, Vista and 7 as well with 32 bits and 64 bits. Thanks in avance for your advise and best regards Tra Browse through the example codes found at Ron de Bruin's site. http://www.rondebruin.nl/tips.htm Look at the 'Copy/paste/merge" section. Should be some good material to start with. Gord Dibben MS Excel MVP On Sat, 28 May 2011 19:53:03 +0200, Tra Sumaka <trasumak...

enter data to different tables through one form
i have 5 tables. all of them has pers_no as primary key. they have one to one relation ship through the primary key amongest them. i want to create one form having a text box pers_no where i will enter the data and i want the data to get entered in each table. so how i am not able to do that. i request all you guys to help me. thank you. Sorry for the bad news, but that's not the right way to use Access. You cannot write to 5 tables from one form like that. If you are subclassing, you could create a form for entering the main form info, and then 5 subforms (probably on tab pages) f...

Scatter plot
I have an XY scatter graph with some points in it. My data in the spreadsheet looks like this: [Title] [X-value] [Y-value] [size] Data 1 | 10 | 10 | 1 Data 2 | 33 | 44 | 5 What I want to do is to change the size of the data point according to the value I specify. Right now, I have to individually modify each point and add a number corresponding to the size. Is there anyway to take the size-value from the spreadsheet and change the size of the point in the graph? In the long run, I am also looking to modify the color. If the solution requires some VBA coding, I am up for that. Any sugges...

Importing data #2
hi I would like to import data from the web and although this is quite simple in Excel 2003 the new data (that comes in upon refresh) replaces the old. I was wondering if there is a way to keep previous history of the data, make Excel fill in adjacent cells with the new data for example. Thanks for taking the time to read this George hi, if you are using Microsoft query, the answer would be no. The Micorsoft query becomes a named range in excel that is linked to the query. by desing it expands and contracts at refresh to accommidate new data. I think to accomplish what you want yo...

Offsetting Chart data
I require to view data in chart format, the problem is as the data lines are so close I need to offset the data to view all on one chart for comparison. Can anyone help me with this?? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ What does that mean? "Offset the data?" If you want to see only a subset of the data, see the Dynamic Charts page of my site. You should be able to adapt one of the examples to your needs. -- Regards, Tushar Mehta, MS MVP -...

Outlook keywork extracter
Is there a program or a way within Outlook to "pull" all emails that contain a set of keywords into a new folder or .pst file? For example, I want every email with "XYZ" or "ABC" to be copied to a new ..pst file or Outlook folder. Thanks for the help, Scott On Tue, 14 Jun 2005 12:18:55 -1000, Scott Cadreau <scadreau@aros.net> wrote: > Is there a program or a way within Outlook to "pull" all emails that > contain > a set of keywords into a new folder or .pst file? > > For example, I want every email with "XYZ" ...

OLE Object Data Type Question
Hi, a friend of mine supports a very simple Access 2000 database for keeping tracks of his .JPG file. The database has a table with several field, and one of them is of OLE Object data typy. He opens that table, rightclicks OLE Object field, selects Insert Oject and creates a link to a .JPG file. After finishing insertion the OLE Object field contains kind of "MSPicture3" or "MSImage3" (I don't remember exactly the text). Before a month ago he moved his database to another computer and now after insertion into the OLE Object field a link, it contains "...

Data Labels on Clustered Column w/ 3D Visual effect #2
Clustered Columns can have Alignment as "Inside End" This doesn't seem possible with 3D Visual effect Is there a way to resolve this? Yes, use 2D charts. They provide a better representation of the values being plotted without the implied false third dimension. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ emwashburn wrote: > Clustered Columns can have Alignment as "Inside End" > > This doesn't seem possible with 3D Visual effect > > Is there a way to ...

Database table relationships
In visio 2007 I can not connect the relationship heads and tails to tables - Any suggestions? Thanks ...

Outlook 2007 Auto open email from known sender
There are a few people in my contact list with whom I need to keep in constant contact. A nice feature would be, for those individuals I trust, to automatically open email on my desktop, or at least to make the auto-notification window persistent. Thanks, Jeff ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader a...

XML Insert data C# ASP.net 2.0 Need to know how to insert by speci
Hello, I have the following XML Schema: <?xml version="1.0" encoding="utf-8"?> <Schedule> <Day Name="Monday"> </Day> <Day Name="Tuesday"> </Day> <Day Name="Wednesday"> </Day> <Day Name="Thursday"> </Day> <Day Name="Friday"> </Day> <Day Name="Saturday"> </Day> <Day Name="Sunday"> </Day> </Schedule> I want users to be able to input the following tiomeblocks for a scheduling s...

Outlook 2003 Bcm Cannot open outlook
Contact Manger has encountered a problem and need to be closed. Event Type:BCM P1:mapi P2: Typeinitializationexception P3: 1.0.2002.1 P4:11.0.8118 P5:nativemsproviderinit P6:msproviderinit P7:D40099TB Is there a fix for this error? Cortexmann wrote: > Contact Manger has encountered a problem and need to be closed. Event > Type:BCM P1:mapi P2: Typeinitializationexception P3: 1.0.2002.1 > P4:11.0.8118 P5:nativemsproviderinit P6:msproviderinit P7:D40099TB > Is there a fix for this error? Any changes to the system between when it worked and when it didn't work,...

Getting data from Access
I am trying to use the Get External Data function to pull data from an Access query however I keep getting an error that says: "Microsoft Query: msqry32.exe - Application error The instruction at 0x000000072 referenced memory at 0x000000072. Memory could not be read Click OK to Terminate Click on CANCEL to debug" I am using Excel and Access 2000. Does anyone have any idea why I keep getting this error? Check out this page http://www.rondebruin.nl/accessexcel.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "NChris" <NChris@discussions.micr...

Cannot get datagrid to display data
Hi, I have an XML file that I was hoping to use as a database Here is the code that I have copied that should display the data namespace File_Generation_System { public partial class vrholds : Form { public vrholds() { InitializeComponent(); XmlDataDocument xmlDatadoc = new XmlDataDocument(); xmlDatadoc.DataSet.ReadXml("vrdatabase.xml"); DataSet ds = new DataSet("vrdata DataSet"); ds = xmlDatadoc.DataSet; dataGridView1.DataSource = ds.DefaultViewManager; } But what I g...

running macro when workbook is openned
I have the following macro it should run when the workbook is opened. I keep getting an error message. Any ideas. Isthere something that is missing Option Explicit Sub Update() With Sheet5 ActiveSheet.Unprotect Application.EnableEvents = False If UserForm4.CheckBox60.Value = True Then Sheet5.ComboBox3.Visible = True Sheet5.Label5.Visible = True Sheet5.TextBox29.Visible = True Sheet6.ComboBox3.Visible = True Else UserForm4.CheckBox60.Value = False Sheet5.ComboBox3.Visible = False Sheet5.Label5.Visible = False Sheet5.TextBox29.Visible = False Sheet6.ComboBox3.Visible = F...

Cannot open database ?
Hi Got a database at work I wrote in 2003, brought a copy home to modify and use here. Now got Access 2007, can load initial form (switchboard) but none of the command buttons work. Can get to design view, all queries and tables run okay. Looked on Tools\User & Group Permissions - Open/Run is the only disabled button on the forms only. All tables, macros etc. all buttons enabled. Access has the Open/Run button disabled. Any idea how I can enable it and run my database please? Thank you Al On Sat, 30 Jan 2010 08:16:01 -0800, Al9315 <Al9315@discussions.microsoft.com>...

RMS Database Questions
We are working on a RMS project with some customization for data extraction and reporting. The data extraction and reporting mandates maximum number of characters for certain fields, such as ItemLookupCode, which cannot excede 14 characters. We are evaluating some of the possible options to meet this requirement. One of the possible solutions appears to be changing the field lengths in the RMS database tables. We have the following questions and appreciate your help if you have any answer for us. a) Is this a viable option? b) Can you resize any or all the columns? c) Can you extend...