Unanswered: Redundant ADO connection to an MS SQL 7 back end
Do I need to do an ADO open and close for every form
I am using Access 2000 ADP to connect to an MS SQL 7 back end.
I have several forms that use ADO to manipulate data. And I don't know if I am being redunded (and slow) or if there is a better way to make the connection to the server and close it. Right now I open and close it each time I do something.
1. Is that correct?
2. Should I use some sort of global connection string?
Here is a sample of what I am currently doing:
'Open a connection without using a Data Source Name (DSN)
Dim cnADOConnectionObject As ADODB.Connection
Set cnADOConnectionObject = New ADODB.Connection
cnADOConnectionObject.ConnectionString = "Provider=SQLOLEDB;Data Source=MERCURY;Initial Catalog = pm-data;User ID=mitch; Password=mitch;"
Set rs = New ADODB.Recordset
sql = "SELECT * FROM T_ProcessDetail WHERE T_ProcessDetail.ProcessID = "
sql = sql & Me!tbxProcessID
sql = sql & " ORDER BY T_ProcessDetail.LineNumber"
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.CursorType = adOpenStatic
Set .ActiveConnection = cnADOConnectionObject
My code goes here
Set rs = Nothing
Set cnADOConnectionObject = Nothing
Thanks for your help, Mitch