Re: ADO connections question

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" wrote:

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"David" <David@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC37306A-80CE-4579-BD3C-90AED3117849@xxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Custom login will not work
    ... When I started a new ASP project I was eager to use the login facilities ... Dim authTicket As FormsAuthenticationTicket ... Dim cookieName As String = FormsAuthentication.FormsCookieName ... PersonID = CType, Integer) ...
    (microsoft.public.dotnet.framework.aspnet)
Loading