[marxman89]

Option Explicit  Private Sub cmdCancel_Click() 
Unload Me End Sub  Private Sub cmdOK_Click()		 
 If Me.txtUserName <> "" And Me.txtPassword <> "" 
And Me.cboUserRole.Text <> "" Then			 
 Call connectdb			  
query = "SELECT * FROM Users WHERE username = '" & Me.txtUserName & "' 
AND password = '" & Me.txtPassword & "' AND accessLevel =
 '" & Me.cboUserRole.ListIndex + 1 & "'"		 
Set rs = db.Execute(query)				 
 If rs.RecordCount > 0 Then			 
username1 = Me.txtUserName.Text		   
  If Me.cboUserRole.ListIndex = 0 Then				
 accessLevel1 = "Administrator"			 Else			   
  accessLevel1 = "Client"			 End If		  
   query = "INSERT INTO UserLog
(username, accessLevel, loginTime, logoutTime) 
VALUES('" & username1 & "', '" & accessLevel1 & "', 
'" & Date & " " & Time & "', ' " & Date & " " & Time & "')"		
	 db.Execute (query)			 db.Close		  
   frmMain.Show			 Unload Me		 Else	 
		MsgBox "User does not exist",
 vbCritical, "RBEI Raw Materials Inventory System"			
					  
 End If	 Else		 MsgBox "Please complete the form",
 vbCritical, "RBEI Raw Materials Inventory System"	 End If	  End Sub

How to convert mssql 2000 query statements here like

"SELECT * FROM Users WHERE username =
 '" & Me.txtUserName & "' AND password = '" 
& Me.txtPassword & "' AND accessLevel = '"
 & Me.cboUserRole.ListIndex + 1 & "'"

I don't have knowledge in ms access that's why i find it so difficult to convert all of the mssql 2000 query statements here into ms access 2003. I already created and connected this to ms access 2003 so there's no more problem regarding with connection string, etc; but still this requires a little tweaking in order to run perfectly. I need to convert this to ms access 2003 because my prof. didn't allow me to use mssql server.

Thanks in advance.

All help greatly appreciated.

[P4L]

Try this:

SELECT * 
FROM Users 
WHERE username = ' '
AND password =  ' ' 
AND accessLevel = ' '

Between the tick marks, add the values you need, or you can set it up like this:

SELECT * 
FROM Users 
WHERE username = [Input Username]
AND password = [Input Password] 
AND accessLevel = [Input Accesslevel or Role]

By using the braces, it will allow you to inout whatever values you want.

[marxman89]

thanks! i'll try your suggestion asap.

[marxman89]

thanks! it worked, however it seems that there are a bit differences in syntax in mssql and msaccess.

I think this is only for mssql and not applicable to ms access

Set rs = db.Execute(query)

after i ran the program an error appeared which red: "Data type mismatch in criteria expression.
and this line was highlighted.

Set rs = db.Execute(query)

[P4L]

Correct. That is will not work in Access since Access uses VBA code not MYSQL, and can not recognize it.

Try:

Set rs = db.Execute strSql

and change:

query = "SELECT * FROM Users WHERE username = '" & Me.txtUserName & "' 
AND password = '" & Me.txtPassword & "' AND accessLevel =
'" & Me.cboUserRole.ListIndex + 1 & "'"	   

to:

strSQL = "SELECT *"
strSQL =  strSQL & "FROM Users" 
strSQL =  strSQL & "WHERE username = [Input Username]"
strSQL =  strSQL & "AND password = [Input Password]" 
strSQL =  strSQL & "AND accessLevel = [Input Accesslevel or Role]"

When it comes to the VBA code and running a query from it, I am still a n00b. I hope this works for you.

This post has been edited by P4L: 11 August 2009 - 09:35 AM