Solved

Connection/DB thoughts

Posted on 1998-04-29
3
174 Views
Last Modified: 2006-11-17
In order to improve performance, I would like to use the connect method just once, when the application starts, and then to use this connection with all other SQL commands I use. My problem is how can I send the connection object between forms, if it is possible ,because it seem like after the first openrecordset execution.
I use VB5 and Oracle7.3

Thanks
Alon
0
Comment
Question by:oak022097
  • 2
3 Comments
 
LVL 5

Expert Comment

by:bin_huwairib
ID: 1434617
oak,

Yes it is possible to start one connection and pass it between forms, notice that the best way to deal with RDBMS is to use Microsoft Remote Data Object (RDO).

Example:

'Declare rdoCN as a global variable (in module1 for example)
Public rdoCN As New rdoConnection

'This code will be in form1
'Establish the connection
rdoCN.Connect = "uid=;pwd=;"DSN=WorkDB;"   'Replace WorkDB with your DSN
rdoCN.Cursordriver = rdUseOdbc
rdoCN.EstablishConnection rdDriverNoprompt 'Now rdoCN is our active connection

'This code will be in form2
Set RS = rdoCN.Execute("Select * from TestData")

'and before quiting the application you must close the connection
rdoCN.Close
set rdoCN = Nothing


Best regards,
Bin Huwairib
0
 

Author Comment

by:oak022097
ID: 1434618
It doesn't help me because I am using the professional edition of VB.
0
 
LVL 5

Accepted Solution

by:
bin_huwairib earned 70 total points
ID: 1434619
OK, can you use DAO? If so, here is the way how to do it.
 
'Declare con as a global variable (in module1 for example)  
Public con As Connection

'This code will be in form1
Dim wrkODBC As Workspace

'Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)

'Open Connection object
Set con = wrkODBC.OpenConnection("Connection1", _
            dbDriverNoPrompt, , _
            "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

'This code will be in form2
Dim rstTemp As Recordset
      
Set rstTemp = con.OpenRecordset( "SELECT * FROM stores", dbOpenDynamic)
      

'and before quiting the application you must close the connection
con.Close
wrkODBC.Close


Best regards,
Bin Huwairib            
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now