Access VB to MS SQL SP

NPUser

Registered User.
Local time
Yesterday, 19:53
Joined
Jul 25, 2004
Messages
55
With the help form a this forum member i was able to get my Access VB working.

http://www.access-programmers.co.uk/forums/showthread.php?t=109875

I would really like to convert this to MS SQL SP. What is best way to approach this? I can convert recordset to cursor, but how do i deal with dlookup?

Help

Dim StrSql2 As String
Dim strSqlUpdate As String
Dim rsn As DAO.Recordset
Dim TempUserName As String
Dim lngStep As Integer

StrSql2 = "SELECT id_num, last_name, first_name, middle_name FROM Admission;"

Set rsn = CurrentDb.OpenRecordset(StrSql2)

rsn.MoveFirst

Do While Not rsn.EOF

lngStep = 1
Dim mnCheck As Boolean
mnCheck = False
'Propose the first username
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name

'Check to see if the user name exists
Do While Not IsNull(DLookup("UserName", "qryUserName", "UserName = '" & TempUserName & "'"))

'If so, try the next one

If Not IsNull(rsn!middle_name) And mnCheck = False Then
TempUserName = Left(rsn!First_name, 1) + Left(rsn!middle_name, 1) + rsn!Last_Name
mnCheck = True
Else
lngStep = lngStep + 1
TempUserName = Left(rsn!First_name, lngStep) + rsn!Last_Name
End If
Loop

strSqlUpdate = "INSERT into temp_user (id_num, last_name, First_name, Middle_name, UserName) VALUES ( " & rsn!ID_num & ", '" & rsn!Last_Name & " ', '" & rsn!First_name & "', '" & rsn!middle_name & "' , '" & TempUserName & "');"
CurrentDb.Execute strSqlUpdate

rsn.MoveNext

Loop


Set rsn = Nothing
 
Perhaps I'm over simplifying, but I'm thinking:

Code:
INSERT INTO temp_user
SELECT id_num,
	last_name,
	first_name,
	middle_name,
	LEFT(first_name,1) + 
		CASE WHEN middle_name IS NULL THEN SUBSTRING(first_name,2,1) ELSE LEFT(middle_name,1) END + 
		last_name
FROM Admission

Similar logic would work in Access, too. Maybe I am missing something.
 
Thanks for looking into this PDX_Man.

Here is what i am trying to achieve. I have current users in Active Directory and new users in admission tables. I have the ADSI link setup to query the AD where i get the current users from.

Taking admission user one at a time into consideration - i simply propose first initial and last name as their username and check it against Active Directory username if no match then him that person the proposed user name if there is a match then try first initial, middle initial if exist and last name else first two charater form first name and last name and so on.

This is what i have so for. Please help me. I am not sure about the line:

While @UserName = (Select RTRIM(UserName) from ViewOCADUsers where @UserName )


/* Drop temporary UserNames table if it already exists */
if exists(select * from sysobjects where name = 'temp_user_names')
drop table temp_user_names

/* Drop temporary Current AD Users table if it already exists */
if exists(select * from sysobjects where name = 'temp_current_AD_names')
drop table temp_current_AD_names



/* Re-create temporary user names table */
create table temp_user_names (
id_num int null,
UserName char(45) null,
First_name char(45) null,
Last_name char(45) null,
Middle_name char(45) null)

/* Re-create temporary current AD Uusers Table */
create table temp_current_AD_names (UserName char(45) null)


/*insert current list of active directory user names to temp table
insert into temp_current_AD_names (UserName)
select SAMAccountName from ViewOCADUsers


/* create a crusor and insert new students list into it.
DECLARE NewStudent cursor for
select id_num, first_name, Last_name, Middle_name from admission

open NewStudent

DECLARE @new_id_num int,
@new_first_name Char(45),
@new_last_name Char(45),
@new_middle_name Char(45),
@UserName Char (45),
@lngStep int,
@mnCheck int

FETCH NEXT FROM NewStudent
INTO @new_id_num int, @new_first_name, @new_last_name, @new_middle_name

while (@@fetch_status <> -1)
begin

set @lngStep = 1
set @mnCheck = 0
set @UserName = LEFT(@new_first_name, @lngStep) + @new_last_name

While @UserName = (Select RTRIM(UserName) from ViewOCADUsers where @UserName )
begin

if not isnull(@new_middle_name) and (@mnCheck = 0) the
set @UserName = LEFT(@new_first_name, @lngStep) + LEFT(@new_middle_name, 1) + @new_last_name
set @mnCheck = 1

else
set @lngstep = @lngstep +1
set @UserName = LEFT(@new_first_name, @lngStep) + @new_last_name
end

INSERT into temp_user_names VALUES (@new_id_num, @new_last_name, @new_First_name, @new_Middle_name, @UserName)

FETCH NEXT FROM NewStudent
INTO @new_id_num int, @new_first_name, @new_last_name, @new_middle_name
end

CLOSE NewStudent
DEALLOCATE NewStudent
 
Well, I'm guessing that line is not going to get you what you want.

While @UserName = (Select RTRIM(UserName) from ViewOCADUsers where @UserName )

where @UserName @Username equals what?

check it against Active Directory username if no match then him that person the proposed user name if there is a match then
... ?

The code I wrote should get you a username. Perhaps compare that to AD.

You should be able to do this without cursors taking the result of my code and joining it to the AD table.
 

Users who are viewing this thread

Back
Top Bottom