Solved

Connection/DB thoughts

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

856 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