[AccessD] Linked to SQL Server in a domain

Jim Lawrence accessd at shaw.ca
Fri May 22 13:26:38 CDT 2009


Just doing all your work through MS SQL, via SPs and setting the security in
the SQL server should be more than enough security. 

On the other hand as MS SQL is secure you could just have a table (maybe
encrypted) where you just cross reference the username and password... Below
is some code I used with a simple configuration. Note that I have used ADO
as I have no ODBC references at all to referr to:

Public Function GetUserInfo() As Boolean
'Given:  gstrConnection = "Provider=SQLOLEDB;Initial
Catalog=Registration;Data Source=imgserver;Integrated Security=SSPI"    

    On Error GoTo Err_GetUserInfo
    
    Dim lsUserCode As String
    Dim objCmd As ADODB.Command
    Dim adoRS As ADODB.Recordset
    
    GetUserInfo = False
    lsUserCode = GetUser()
    gsUserID = Left(lsUserCode, 4)
    
    Set objCmd = New ADODB.Command
    With objCmd
        .ActiveConnection = gstrConnection
        .CommandText = "REGetUser"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@chrUserID", adChar,
adParamInput, 15, lsUserCode)
    End With
    
    Set adoRS = New ADODB.Recordset
    adoRS.Open objCmd, , adOpenForwardOnly, adLockReadOnly
    With adoRS
        If .BOF = False And .EOF = False Then
            gbAccessLevel = !AccessLevelCode
            GetUserInfo = True
        Else
            MsgBox "The user login credentials does not" & vbCr & _
                    "match a known user. Please contact" & vbCr & _
                    "the Application Administrator to have" & vbCr & _
                    "the appropriate security established.", vbCritical
            gbAccessLevel = 0
        End If
    End With
    
Exit_GetUserInfo:
    adoRS.Close
    Set adoRS = Nothing
    Set objCmd = Nothing
    
    Exit Function
    
Err_GetUserInfo:
    
    If Err.Number = -2147467259 Or Err.Number = -2147217843 _
       Or Err.Number = 3706 Then
        If MsgBox("The application could not achieve a" & vbCr & _
            "connection to the SQL Server. Please" & vbCr & _
            "contact System Support to have the" & vbCr & _
            "appropriate permissions set up." & vbCr & _
            "(" & Err.Number & " - " & Err.Source & " - " & _
		Err.Description & ")" & vbCrLf & _
            "CHECK Server Connection?", vbCritical + vbYesNo +
vbDefaultButton1) = vbYes Then

           CheckConnection
        End If

    ElseIf Err.Number = 429 Or Err.Number = 430 Then
        If MsgBox("The required ADO (Active Data Object) and" & vbCr & _
            "ActiveX components have not been set-up or" & vbCr & _
            "referenced on the current work-station. Please" & vbCr & _
            "contact your System Support person and them update" & vbCr & _
            "this computer with a full ACCESS97 installation." & vbCr & _
            "(" & Err.Number & " - " & Err.Source & " - " & _
		Err.Description & ")" & vbCrLf & vbCr & _
            "RUN FULL REFERENCE CHECK?", vbCritical + vbYesNo +
vbDefaultButton1) = vbYes Then
            CheckReferences
        End If

    ElseIf Err.Number = 3706 Or Err.Number = 3021 Then
        MsgBox "The REGISTRY requires adjustment." & vbCr & _
            "Please contact your System Support person" & vbCr & _
            "and have them update the Registry in this computer." & vbCr & _
            "(" & Err.Number & " - " & Err.Source & " - " & _
 		Err.Description & ")", vbCritical, "Registry Error"
    Else
            ShowErrMsg ("GetUserInfo")
    End If

    GetUserInfo = False
    On Error GoTo 0
    GoTo Exit_GetUserInfo

End Function

I hope this helps and if you need further code fragments I can send that
along as well.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Friday, May 22, 2009 5:25 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linked to SQL Server in a domain

Jim,

The client is a very small company (6 or so employees) that helps seniors
with their medical 
insurance plans, researching billing issues and so forth.  They wanted a new
network administrator 
(which I am definitely not) so they went out and hired a new company to do
this.

This company advised them that in order to be HIPAA compliant they needed to
move the data into SQL 
Server (for encryption reasons AFAICT).  It seems this company quoted a very
low ball price to 
convert the system to SQL Server, and in the end it APPEARS that all they
did was run the upgrade 
wizard.  AFAICS out in the SQL Server there are no views or stored
procedures at all, only the data. 
  Even there they appear to have screwed some stuff up.  The database, which
I did not write but 
which I maintained and added new functionality to for many years, was
working fine (as stated by the 
company owner) before the upgrade but has "a bunch of problems" since the
upgrade.

The owner is in the process of hiring a new network admin company and has
asked me to come back and 
straighten out the problems in the db.  To be honest I was just brought back
in a few days ago so I 
really don't know the details yet on the "bunch of problems".

In any event, the DB FE uses simple ODBC links to get at the data.  They
have very few employees and 
performance seems to be pretty adequate doing things this way.  I understand
(form a theoretical 
perspective) the concept of using ADO recordsets for the bound forms and as
data sources for the 
combos etc. hitting stored procedures out on the SQL Server but none of that
is presently used.

I am trying to come in and get some additional functionality happening
quickly, for example a fairly 
strong login to the database and possibly additional presentation layer
security.  I really don't 
want to get bogged down in trying to move the database away from ODBC at
this point, though I would 
love to take this opportunity to do this as things settle down.

John W. Colby
www.ColbyConsulting.com


Jim Lawrence wrote:
> Hi John:
> 
> I have been working with MS Access to ADO-OLE to MS SQL/Oracle DBs since
> 1997 and Access does not work with ODBC. There is a simple and stupid way
to
> up date an old Access MDB to a MS SQL BE using connection/links. 
> 
> This system actually works fine for delete, add and update... but as soon
as
> you start grabbing recordsets of data for reports, subforms or start
rolling
> out a new application to a remote desktops the whole system grinds to a
> halt. At that point you have reached the end of the usefulness of the ODBC
> connections.
> 
> Unfortunately there is no short cut, the client and you just have to bite
> the bullet and go straight ADO-OLE... It is not that difficult but the
nice
> gui interface within Access can not do it.
> 
> Jim

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list