DUAL alternative in ms access database

Question

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?

Why would `pass_word='username'`? If user_name and pass_word are defined in Access table as compound unique index, duplicate record will not be inserted using `INSERT INTO usertabble(user_name, pass_word) VALUES('username', 'password')` syntax. — June7, Oct 24 '19 at 00:50
This https://stackoverflow.com/questions/28282127/insert-into-not-exists-sql-access works when pulling values from another table but can't get it to work with parameters. — June7, Oct 24 '19 at 05:43
@June7 I read that answer from link you provided. but in that there was table available but here is not. DUAL is literally imaginary table for access sql. I did tried making table named DUAL with just 1 row in it. But that also is not working. — HungryCoder, Oct 24 '19 at 06:42
I know that link showed another table in solution. As I said, could not get it to work without. Only option I can suggest is to do a lookup to table and if no match, run INSERT action. I suppose for you that means open a recordset based on parameters and if it is empty, run INSERT. — June7, Oct 24 '19 at 07:21

score 1 · Answer 1 · answered Oct 24 '19 at 10:39

1

You just can use select value1, value2 wihtout from clause (from DUAL) to achieve same result.

answered Oct 24 '19 at 10:39

Arvo

10,349
1
31
34

Gordon Linoff · Answer 2 · 2019-10-24T10:53:41.563

This query:

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
                     );

Makes no sense in MS Access.

Basically you can use a unique index or unique constraint to preserve uniqueness. Presumably, you just want the user_name to be unique:

create unique index unq_usertable_username on usertable(user_name);

You can include more columns if needed.

This will prevent the database from inserting multiple rows with the same value.

yeah agree. I should have defined username as primary key or unique index. But lets say what if i don't want to do that then what ? and real question is about DUAL. — HungryCoder, Oct 24 '19 at 04:26

score 0 · Accepted Answer · answered Oct 24 '19 at 10:29

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.

thanks for in detail explanation and making me sure that I can still achieve same result. — HungryCoder, Oct 25 '19 at 00:03

score 0 · Answer 4 · answered Jul 22 '22 at 06:02

just use your existing table name and it wont complain

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

works for me when i want to add an "*" to the top of a dropdown...

select '*' as code from codetable
union
select code from codetable

DUAL alternative in ms access database

4 Answers4