ADP forms and SQL Server 2000 authentication

[Riz]

Hi,

I've recently upsized a MSAccess2000 database to an access database project.
The ADP links into a SQL2000 backend. I've got Chipman and Baron Microsoft
Access Developers guide to SQL server, which has been a great help so far.

I've got as far as converting all the queries to views and stored procedures
where necessary and am now testing the ADP front-end.

There are a couple of issues that I am struggling to resolve though (bearing
in mind that these were working fine in MSAccess):

Issue 1. which is related to forms - a typical form in this database is made
up of several sub forms e.g the main form is at 'company level', with a
'contact level subform', which in turn has a 'call' and 'conversations log'
sub form in that.
The company, contact and call level forms seem to link and work fine i.e.
record navigation and udates/changes are saved. The 'conversations log' is
meant to show all the previous call results for a contact and is linked by
Contact ID - however this subform is returning all the conversations for all
the contacts? Any ideas why this would be? and how to fix it?

Issue 2. Under the Tools menu - Security sub menu, I can't set the
Workgroup administrator as this is greyed out. And when I try to set a
database password (option above workgroup administrator) I get the dialogue
box 'microsoft office access cannot change the password for the logon account
because the current connection is using microsoft windows nt integrated
security'. I need to set the Workgorup administrator so that I can kep a
track of the users as well as stamping the user name when a call is made
(event AfterUpdate, me.caller = currentuser( )).

Any help with this is most appreciated.

Regards,

Riz

 

[Sylvain Lafontaine]

I have no idea about your first issue.

For your second issue, you can't set the Workgroup administrator because the
use of MDW files are limited to MDB files only, not ADP or ADE.

For the Database password, this is not for setting the database password but
for changing the password for the current SQL-Server login. When using
Integrated Security, there are no SQL-Server login in use and hence, this
option is greyed out.

Instead of currentuser(); you have to make a call to the function
SUser_SName() on the SQL-Server:


Dim s As String
s = "Select SUser_SName() as su"

Dim rs As ADODB.Recordset
Set rs = Application.CurrentProject.Connection.Execute(s)

MsgBox rs("su")