ado connections in access

Hi,

Is the following code correct to call the ado-connection from every form i
create. I'm getting errors if i execute it.
For testing purposes i used the code in the form's module and then it
worked, but not how i like it. The purpose of it, is to use connection
pooling.

Here is the code in the module:
option explicit
Dim cnn As ADODB.Connection

public sub ado_conn()

Set cnn = New ADODB.Connection

With cnn
.Provider = "sqloledb"
.Properties("Data Source") = "local"
.Properties("Initial Catalog") = "NEW"
.Properties("User ID") = "sa"
.Properties("Password") = "password"
End With
end sub

From the form onOpen event:

dim cnn as adodb.connection
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.CursorLocation = adUseClient
cnn.Open cnn
cnn.usp_search 7, Nz(Null, ""), rst 'this line calls a stored procedure
from a sql server database.

Set Forms("Form1").Recordset = rst

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

Hi Jason,
It seems you’ve instantiated a global connection object, but then in the
form “Load?” (I assume this is what you mean when you mention the “onOpen
event”?) event you declare another connection object, however you don’t
instantiate this one, nor set any of its properties. Try getting rid of the
“dim cnn as adodb.connection” in the form load event; that way the global
connection you created in the module will be used as the connection.
Hope this helps
Ant