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