Solved

Connection/DB thoughts

Posted on 1998-04-29
3
175 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

911 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