Connection/DB thoughts

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
oak022097Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bin_huwairibCommented:
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
oak022097Author Commented:
It doesn't help me because I am using the professional edition of VB.
0
bin_huwairibCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.