DUAL alternative in ms access database

I am working with windows forms in VB.NET and MS Access database. I need to check that if user already exist in my database or not and take further action accordingly. After searching for awhile I came across this solution.

INSERT INTO usertabble (user_name , pass_word) 
SELECT 'username', 'password' FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM usertable
WHERE user_name='username' AND pass_word='password' LIMIT 1)

But the problem is DUAL keyword. Query after that keyword shows syntax error. I learned that DUAL is exclusive to oracle databases. So I found a workaround for MS Access which is creating table named DUAL in same database. But that is also not working.

Is there any problem with my approach? or Simply it is not doable? Is there any other alternative?

Answer

Well, it not clear why you don’t simply check for the user and then take action based on this check?

You don’t mention what the alternative action is, but it would seem to be a simple matter to check for existence of the given user, and then take whatever action you desire.

So, you could check for existence like:

    Dim lngID  as integer
    lngID = CheckForUser("Albert", "Password")
    if lngID = 0 Then
        MsgBox("password not found")
    Else
        MsgBox("password found, PK ID = " & lngID.ToString)
        ' code to use PK "id" of user found goes here
    End If

And of course make a handy function say like:

Public Function CheckForUser(strUser As String, strPass As String) As Integer

    Dim MySQL As String = "SELECT ID, user_name, pass_word from usertable " &
                          "WHERE user_name = ? AND pass_word = ?"
    Dim lngID As Integer = 0

    Using MyCon As New OleDbConnection(My.Settings.test443)
        MyCon.Open()
        Dim sqlCmd As New OleDbCommand(MySQL, MyCon)
        sqlCmd.Parameters.Add("?", OleDbType.VarWChar).Value = strUser
        sqlCmd.Parameters.Add("?", OleDbType.VarWChar).Value = strPass
        lngID = sqlCmd.ExecuteScalar
    End Using
    Return lngID

End Function

I not really sure why the issue of DUAL is all that relevant here?

Now, if your question was if I check for a name, and if not, I want to add, and how can I use say one query for both operations, then we have a different question here.

It not clear as to the goal and using DUAL (that we don’t have for access) as simply opposed to checking for existence of a name/record in a table.