Hi,

I m new to VBA could you please help me to rectify this error.

We have an Access application with Frontend and recently migrated the database to SQL server. After does the migration when we open the Access project(.adp) we unable to login the application which is running quite good before migration. I am getting an error "Run Time erro 91 : Object variable or With block variable not set" in the code line :

Set rs = DB.OpenRecordset("System Preferences", DB_OPEN_DYNASET) ' Create dynaset.

as rs = nothing.



Private Sub Command8_Click()
Dim DB As Database
Dim rs As DAO.Recordset
If DLookup("[Active]", "REVIEWERS", "[UserName] = '" & Me.UserName & "' and [Password] = '" & Me.Password & "'") Then
' If it's not valid you don't get in????
' if valid update preferences table for future use...
Set DB = CurrentDb
Set rs = DB.OpenRecordset("System Preferences", DB_OPEN_DYNASET) ' Create dynaset.
If rs.RecordCount > 0 Then
rs.MoveFirst ' Goto first record.
rs.Edit ' Enable editing.
rs!ReviewersName = DLookup("[NameFirst]", "REVIEWERS", "[UserName] = '" & Me.UserName & "'") & " " & DLookup("[NameLast]", "REVIEWERS", "[Username] = '" & Me.UserName & "'")
rs!ReviewersID = DLookup("[ID]", "REVIEWERS", "[UserName] = '" & Me.UserName & "'")
rs!ReviewersSecurityLevel = DLookup("[ReviewerSecurityType]", "REVIEWERS", "[UserName] = '" & Me.UserName & "'")
rs!ReviewersInitials = DLookup("[ReviewerInitials]", "REVIEWERS", "[UserName] = '" & Me.UserName & "'")
rs.Update ' Save changes.
rs.Close
Set rs = Nothing
DB.Close
Set DB = Nothing
DoCmd.Close
End If
Else
MsgBox "Invalid User Login. If you feel this login is valid... Please contact your system Administrator"
Me.Password.SetFocus
End If
End Sub


Is we have to change the DAO to ADO connectionstring to connect SQL server ?

You will need to go down the ADODB route. Also I noticed you are performing at least 6 DLookups in the same table for the same person. You could improve performance and reduce traffic by using the following method

StrSql = "Select * From REVIEWERS Where [UserName] = '" & Me.UserName & "' and [Password] = '" & Me.Password & "'"

Dim Rs1 As DAO.Recordset
Set Rs1 = CurrentDB.Openrecordset(StrSQL)

If Not Rs1.BOF And Not Rs1.EOF Then
rs!ReviewersID = Rs1!ID
...rest of fields
Rs1.Close
End If
This way you only open the recordset once

David