4 Replies - 810 Views - Last Post: 11 August 2009 - 09:28 AM

#1 marxman89  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-July 09

How to convert mssql statement from mssql 2000 to ms access 2003 in vi

Posted 11 August 2009 - 02:11 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: How to convert mssql statement from mssql 2000 to ms access 2003 in vi

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: How to convert mssql statement from mssql 2000 to ms access 2003 in vi

Posted 11 August 2009 - 06:10 AM

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.
Was This Post Helpful? 1
  • +
  • -

#3 marxman89  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-July 09

Re: How to convert mssql statement from mssql 2000 to ms access 2003 in vi

Posted 11 August 2009 - 07:16 AM

thanks! i'll try your suggestion asap.
Was This Post Helpful? 0
  • +
  • -

#4 marxman89  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 09-July 09

Re: How to convert mssql statement from mssql 2000 to ms access 2003 in vi

Posted 11 August 2009 - 08:12 AM

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)

Was This Post Helpful? 0
  • +
  • -

#5 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: How to convert mssql statement from mssql 2000 to ms access 2003 in vi

Posted 11 August 2009 - 09:28 AM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1