ADO connections question

[David]

Is is better to open one ADO Connection to the database and persist it across
all forms or is it better to open the connection, execute the view or stored
procedure, and then close it?

Also, if I do keep the connection open can I open it in the login form and
then with all forms do something like the following?
Set cnnADO = CurrentProject.Connection

The reason I ask is that if I close the connection in the login form and
then try to open a new connection in succeding forms I am getting the
following error:
Run-time error 3709
Requested operation requires an OLE DB session object.

Thanks in advance for the help.

 

[Sylvain Lafontaine]

Without seeing your code, it's impossible to say why you have an error
message. In the case of keeping a connection across all forms, Access is
doing it already for you with the CurrentProject.Connection object. I don't
see why you are closing it after the login form.

There are cases when you want to open more connections because you are
opening more than a single recordset at a time. In these cases, I usually
take the precaution of closing them when I'm finished with them.

Finally, without seeing your code, it's impossible to tell you why you are
hitting an error message.

 

[David]

Sylvain;
I have a module which creates the ADO connection object to validates the
login:
Option Compare Database


'declare ADO objects
Public adoConn As New ADODB.Connection

Function CreateADOObjects()


End Function

Function dbLogin(txtUser As String, txtPword As String, txtServer As String,
txtDatabase As String)

On Error GoTo login_err
Dim strCnn As String

'Connect to SQL Server with the native provider
With adoConn
.Provider = "SQLOLEDB.1"
On Error Resume Next
'Try SQL Server security with credentials
strCnn = "Data Source=" & txtServer & ";User ID=" & txtUser & _
";Password=" & txtPword & ";Initial Catalog=" & txtDatabase
.Open strCnn
End With

dbLogin = True



Exit Function

login_err:
dbLogin = False



End Function


I have a specific login form that calls the dbLogin function as shown below:
Option Compare Database
Option Explicit
Dim strCriteria As String
Dim intTry As Integer
Dim strInputFileName As String
Dim strFilter As String

Dim strServer As String
Dim stLocalTableName As String
Dim stRemoteTableName As String
Dim strDatabase As String
Dim strUID As String
Dim strPwd As String

Dim ALLOWCLOSE As Variant

Dim adoCmd As New ADODB.Command
Dim adoRS As New ADODB.Recordset
Dim adoParam As New ADODB.Parameter


Private Sub Form_Unload(Cancel As Integer)
If Not ALLOWCLOSE Then Cancel = True
End Sub
Private Sub cmdCancel_Click()

DoCmd.Quit
End Sub


Private Sub cmdOK_Click()
If IsNull(Me.ServerName) = True Then
MsgBox "The Server is required.", vbCritical
Me.ServerName.SetFocus
Exit Sub
End If
If IsNull(Me.UserName) = True Then
MsgBox "The User Name is required.", vbCritical
Me.UserName.SetFocus
Exit Sub
End If
If IsNull(Me.Password) = True Then
MsgBox "The Password is required.", vbCritical
Me.Password.SetFocus
Exit Sub
End If

Me.LoginMessage = "Logging in to server..."
Me.Repaint

strDatabase = "PBICdb_v10"
strServer = Me.ServerName
strUID = Me.UserName
strPwd = Me.Password



If dbLogin(strUID, strPwd, strServer, strDatabase) Then
'// All is okay.
Me.Visible = False
Me.Password.StatusBarText = ""
ALLOWCLOSE = False
DoCmd.OpenForm "frmMenu"

Else
'// Not okay.
MsgBox "Login failed. Try again.", vbCritical
Me.UserName.SetFocus
Exit Sub
End If



End Sub



Private Sub Form_Open(Cancel As Integer)
'disable default security login form
CurrentProject.OpenConnection ""

DoCmd.SetWarnings False
Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False


strUID = GetUserName()
Me.UserName = GetUserName()
Me.ServerName = "PBICdb_v10"

End Sub

Private Sub Password_AfterUpdate()
On Error Resume Next
Call cmdOK_Click
End Sub

Up to this point I am ok. In the next form, when I try to use the adoConn
object I get an error trying to set the ActiveConnection attribute for the
command object. The connection object does exist at that point:

Private Sub Form_Open(Cancel As Integer)

' close connection
CurrentProject.OpenConnection ""

'Turn off Access messages
DoCmd.SetWarnings False
Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False


'Get the user name entered
Dim strUserName As String
Dim strPwd As String
Dim strDatabase As String
Dim strServer As String

strUserName = Forms!frmlogin.UserName
strPwd = Forms!frmlogin.Password
strDatabase = "PBICdb_v10"
strServer = Forms!frmlogin.ServerName




'create and append parameters and execute the store procedure
With adoCmd
'create and append the parameter
Set adoParam = .CreateParameter("UserName", adVarChar, adParamInput,
Len(strUserName), strUserName)
.Parameters.Append adoParam

Set .ActiveConnection = adoConn
'specify a stored prcoedure
.CommandType = adCmdStoredProc
'Brackets must surround stored procedure names with spaces
.CommandText = "sp_Get_User_Access"

'receive the recordset
Set adoRS = .Execute


End With

I have tried several iterations where I rebuild the adoConn object for each
form but it seems like that should not be necessary. Perhaps I have a basic
misunderstandng of how the object is handled?

David

 

[Sylvain Lafontaine]

First, you shouldn't use the following syntaxe:

Public adoConn As New ADODB.Connection

as it will hide many bugs such as inadvertently closing the connection. Use
this instead:

Public adoConn As ADODB.Connection

Function CreateADOObjects(ConnectionString as string)


Set adoConn = new ADODB.Connection

End Function


In your case, you'll see this error (3709) if the connection has not been
opened correctly or has been closed in the meantime or it's still has an
outstanding opened recordset on it; as you cannot have to opened recordset
on the same connection with the SQLOLEDB provider (you can do that only with
the more recent Native Provider when MARS is enabled; not sure if it's
limited to SQL-2005+).


Probably that your error comes from that: when you try to create/open the
recordset with the command « Set adoRS = .Execute », a previous recordset
already associated with this connection has not been closed (or has not
reached its EOF status; don't remember exactly which one is required).

In your case, you should test to see if adoConn is still an object (is Not
Null and IsObject()) and it's not closed before using it. However, it's
likely that the error of still having an opened recordset will slip through.
You should also make these tests right after you open the connection. You
should also check the Errors collection with something like: « If
(objConn.Errors.Count > 0) then ... ».

Connections are pooled; so your best course of action would be to store the
connection string somewhere and use it to create/open new connection objects
each time you need one; without forgetting to close them and delete them
right after that. While trying to keep and reuse the same connection object
might seem to be a valid way of increasing performance; the real gain that
you get in this way is probably close to nothing because the real work on
SQL-Server is the execution of your request and the return of the resultset.
Opening a connection is nothing in comparaison.

Finally, I don't see what all these « CurrentProject.OpenConnection "" » at
the beginning of each form are doing here and also what you are trying to
achieve but using ADP with unbound forms. If you don't want to use bound
forms, I don't see why you want to use ADP.

 

[David]

Sylvain;
Thanks forlooking into this as this is very helpful. I decided not to use
my custom login form and simply use the default login I get. This has
simplified everything so that I simply use the CurrentProject.Connection to
set the .ActiveConnection property on my adoCmd object.
The rest of the forms in the project are bound - it was just the login
form that wasn't. Also thanks for the advice on declaring the ado objects.

David