Adding ADODC Controls to a form without addind connections to Server

[mgardiner]

I have a vb app that allows you to log in to an SQL Server.
I then want to use that same connection for my ADODC controls
on my forms. When I use my ADODC controls though I find that
for every ADODC control I have a Connection in SQL.
Is there a way around this.
below is my ADODC control Code.



------------------------------------------------------------------------------
Private Sub Form_Load()
'This loads initial Settings
Call FormInit
'This loads the ProjectNo Field on the Form Which in Turn Loads
'the entire form with the proper records
Call ProjectNoLoad

End Sub

Private Sub FormInit()
'Positions the Top of the Form at top side of the screen
Me.Top = 0
'Positions the Left Side of the Form at Left hand side of the screen
Me.Left = 0
End Sub
Private Sub ProjectNoLoad()
'This loads the Projects ADODC control with the recordset data from SQL
With AdodcProjects
.ConnectionString = g_objConn.ConnectionString
.CommandType = adCmdText
.RecordSource = "SELECT tblProjects.* FROM tblProjects Order by tblProjects.ProjectNo "
End With
'After Setting the Connection String and Recordsource you need to refresh the control
AdodcProjects.Refresh

End Sub

Private Sub QuoteNoLoad()
'This loads the Quotes ADODC control with the recordset data from SQL
With AdodcQuotes
.ConnectionString = g_objConn.ConnectionString
.CommandType = adCmdText
.RecordSource = "SELECT tblQuotes.* FROM tblQuotes Where tblQuotes.ProjectID = " & AdodcProjects.Recordset.Fields(0) & " Order by tblQuotes.QuotationNo "
End With
'After Setting the Connection String and Recordsource you need to refresh the control
AdodcQuotes.Refresh

AdodcQuotes.Recordset.Find &quot;ProjectNoRevision <> Null&quot;

If AdodcQuotes.Recordset.EOF Or AdodcQuotes.Recordset.BOF Then
txtProjectRev.Text = &quot;&quot;
AdodcQuotes.Recordset.MoveFirst
Exit Sub
Else
txtProjectRev.Text = AdodcQuotes.Recordset.Fields(2).Value
AdodcQuotes.Recordset.MoveFirst
End If

End Sub

Private Sub QuoteLogLoad()
'This loads the QuoteLog ADODC control with the recordset data from SQL
With AdodcQuoteLog
.ConnectionString = g_objConn.ConnectionString
.CommandType = adCmdText
.RecordSource = &quot;SELECT tblQuoteLog.* FROM tblQuoteLog Where tblQuoteLog.QuoteID = &quot; & AdodcQuotes.Recordset.Fields(0) & &quot; Order by tblQuoteLog.QuoteNoRevision;&quot;
End With
'After Setting the Connection String and Recordsource you need to refresh the control
AdodcQuoteLog.Refresh

AdodcQuoteLog.Recordset.Find &quot;SalesOrderNumber <> Null&quot;

If AdodcQuoteLog.Recordset.EOF Or AdodcQuoteLog.Recordset.BOF Then
txtSalesOrderNoNOTLINKED.Text = &quot;&quot;
txtSalesOrderDateNOTLINKED.Text = &quot;&quot;
AdodcQuoteLog.Recordset.MoveFirst
Exit Sub
Else
txtSalesOrderNoNOTLINKED.Text = AdodcQuoteLog.Recordset.Fields(7).Value
txtSalesOrderDateNOTLINKED.Text = AdodcQuoteLog.Recordset.Fields(8).Value
AdodcQuoteLog.Recordset.MoveFirst
End If

End Sub

Private Sub txtProjectNo_Change()
'This code checks to see if the Recordset is reached the end or beginning and if not then
'run the code that loads data into the Quotes ADODC control
If AdodcProjects.Recordset.EOF = True Or AdodcProjects.Recordset.BOF = True Then
Exit Sub
Else
Call QuoteNoLoad
End If
End Sub

Private Sub txtQuoteNo_Change()
'This code checks to see if the Recordset is reached the end or beginning and if not then
'run the code that loads data into the QuoteLog ADODC control
If AdodcQuotes.Recordset.EOF = True Or AdodcQuotes.Recordset.BOF = True Then
Exit Sub
Else
Call QuoteLogLoad
End If
End Sub

---------------------------------------------------------------------------------

'this is my module to log into the SQL SERVER and set the connection
Option Explicit

'Declare public objects
Public g_objConn As ADODB.Connection
Public g_objError As ADODB.Error
Public strlogin As String
Public strpassword As String


Public Function EstablishConnection( _
ByVal strlogin As String, _
ByVal strpassword As String) As Boolean

'Setup error handling
On Error GoTo EstablishConnection_Error

'Declare local variables
Dim strConnectString As String

'Set a reference to the ADO Connection object
Set g_objConn = New ADODB.Connection

'Build the ADO part of the connect string
strConnectString = &quot;PROVIDER=SQLOLEDB;&quot; & _
&quot;DATA SOURCE=FIFEKBE;&quot; & _
&quot;INITIAL CATALOG=OrderEntryTest;&quot;


'Add the User ID and Password to the connect string
strConnectString = strConnectString & _
&quot;User ID=&quot; & strlogin & &quot;;&quot; & _
&quot;Password=&quot; & strpassword & &quot;;&quot;

'Open the Connection object
g_objConn.Open strConnectString

'Check connection state
If g_objConn.State <> adStateOpen Then
EstablishConnection = False
Else
EstablishConnection = True
End If

'Exit function
Exit Function

EstablishConnection_Error:
'Connection failed, display error messages
Dim strError
For Each g_objError In g_objConn.Errors
strError = g_objError.Description & vbCrLf & vbCrLf
Next
MsgBox strError, vbCritical + vbOKOnly, &quot;Login Error&quot;
End Function