INSERT INTO or other SQL operator ?

[VAer]

I have an Access table Users_T which has three fields: Computername, LoginID, Password

Computername is the only Primary Key

Now I have three variables:


Computername = Environ("username")
LoginID = Forms("Login").TextBoxID.Value
Password = Forms("Login").TextboxPassword.Value

Those three variables are NOT NULL.

Question: now if I have those three variable values, I want to check if Computername is already in the table User_T, if Computername already exists (LoginID and/or Password could be NULL), then update Login and Password with new variable values, no matter if LoginID/Password is NULL or not. If table User_T does not have the Computername, then add those three variables as a new record.

How should I write the SQL statement?

Not familiar with SQL syntax. My code is more about the logic. How to write it correctly? Or if there is simply way to write it? Something with one line of SQL to including both UPDATE and INSERT?

Thanks.

Dim Computername As String
Dim LoginID As String
Dim Password As String


Computername = Environ("username")
LoginID = Forms("Login").TextBoxID.Value
Password = Forms("Login").TextBoxPassword.Value






SqlExistStr = "SELECT ComputerUsername FROM User_T WHERE ComputerUsername = @Computername"
SqlUpdateStr = "Update User_T SET LoginID = @LoginID, Password = @Password WHERE  ComputerUsername = @Computername"
SqlInsertStr = "INSERT INTO Users_T (Computername, LoginID, Password) VALUES (@Computername, @LoginID, @Password)"


IF EXISTS(DoCmd.RunSQL SqlExistStr)
    DoCmd.RunSQL SqlUpdateStr
Else
    DoCmd.RunSQL SqlInsertStr
End If

[ranman256]

you can do all this in a query.
NO code needed.

[pbaldy]

I'd open a recordset on SQL that included a criteria on computer name. If it comes up with no records (EOF) then use the AddNew method to add it, otherwise use the Edit method to edit the record.

[VAer]

you can do all this in a query.
NO code needed.

How to do it step by step?

[June7]

Research what is known as UPSERT. I think you will find you need to go with the conditional code you have.

[VAer]

I'd open a recordset on SQL that included a criteria on computer name. If it comes up with no records (EOF) then use the AddNew method to add it, otherwise use the Edit method to edit the record.

Can you write some key codes? Such as how to open Access table as Recordset? How to write a AddNew statement and Edit statement?

Maybe you are right, I should completely ignore SQL, since it is dealing with Access table, not database table.

Thanks.

[VAer]

Research what is known as UPSERT. I think you will find you need to go with the conditional code you have.

Does UPSERT really exist? I did not find it on https://www.w3schools.com/sql/default.asp

[June7]

No, it is not an intrinsic SQL action, it is a concept. One discussion https://stackoverflow.com/questions/...14004#37014004

[VAer]

What does "Object variable or With block variable not set" mean? I have set variable type.

I have previous code CreateObject("ADODB.Connection") to test if Username/Password is correct. If it is correct, I go with below code to either AddNew or Edit the Access table.

The code actually goes directly from Set LoginUserRS = TestDB.OpenRecordset(LoginUserSQL) to Login_Error:

Thanks.

Dim Username As String
Dim Password As String
Dim ComputerUsername As String
Dim TestDB As DAO.Database
Dim LoginUserRS As DAO.Recordset
Dim LoginUserSQL As String

.....
.....


On Error GoTo Login_Error

.....
.....

Set TestDB = CurrentDb
LoginUserSQL = "SELECT * FROM UserT WHERE ComputerUsername = " & ComputerUsername
Set LoginUserRS = TestDB.OpenRecordset(LoginUserSQL)


If LoginUserRS.EOF Then
    LoginUserRS.AddNew
    LoginUserRS.Fields("ComputerUsername") = ComputerUsername
    LoginUserRS.Fields("Username") = Username
    LoginUserRS.Fields("Password") = Password
    LoginUserRS.Update
Else
    LoginUserRS.Edit
    LoginUserRS.Fields("ComputerUsername") = ComputerUsername
    LoginUserRS.Fields("Username") = Username
    LoginUserRS.Fields("Password") = Password
    LoginUserRS.Update
End If


.......
.......

Login_Error:

[Micron]

It indicates the following possibilities
- you don't have OPTION EXPLICIT at the top of every module. See Options >always require variable declaration in vb editor. This option ensures you don't try to use a variable that hasn't been declared.
- it can also mean you have declared a variable of type OBJECT but haven't SET it (created it) before trying to use it.
- seldom have I ever had the error from not properly declaring a With block but as the message suggests, it's a possibility.

- last suggestion removed. Apparently it's still too early where I am. Leaving in what still seems relevant below.

PS - advise that you do not name variables the same as other objects (fields/tables). Should adopt a naming convention

https://access-programmers.co.uk/for...d.php?t=225837
http://access.mvps.org/access/general/gen0012.htm


EDIT 2:
Looks like a lot of unnecessary code for what you're doing, though.
I also assumed you got an error message, but upon review, don't see where you say that. Maybe you just moused over the variable before the line that assigns a recordset to the variable was executed? In that case, you're trying to examine what a variable holds before anything has been assigned to it. In that case, maybe there's no problem. That would make this about the longest useless answer I've come up with in a while.

[VAer]

The code skips IF statement.

The code actually goes directly from Set LoginUserRS = TestDB.OpenRecordset(LoginUserSQL) to Login_Error:

Maybe there is somethingwrong with CurrentDb, previous code checks Sybase connection, while I want to Set TestDB to current Access database. How to define Access database (while other Sybase database is open)?

Thanks

It indicates the following possibilities
- you don't have OPTION EXPLICIT at the top of every module. See Options >always require variable declaration in vb editor. This option ensures you don't try to use a variable that hasn't been declared.
- it can also mean you have declared a variable of type OBJECT but haven't SET it (created it) before trying to use it.
- seldom have I ever had the error from not properly declaring a With block but as the message suggests, it's a possibility.

- last suggestion removed. Apparently it's still too early where I am. Leaving in what still seems relevant below.

PS - advise that you do not name variables the same as other objects (fields/tables). Should adopt a naming convention

https://access-programmers.co.uk/for...d.php?t=225837
http://access.mvps.org/access/general/gen0012.htm


EDIT 2:
Looks like a lot of unnecessary code for what you're doing, though.
I also assumed you got an error message, but upon review, don't see where you say that. Maybe you just moused over the variable before the line that assigns a recordset to the variable was executed? In that case, you're trying to examine what a variable holds before anything has been assigned to it. In that case, maybe there's no problem. That would make this about the longest useless answer I've come up with in a while.

[Minty]

You are declaring ComputerUsername as a string but then not concatenating it as a string value in your SQL select.
Therefore because of your error handler you will get a error and go straight to the error handler. (Which you haven't shown us)

LoginUserSQL = "SELECT * FROM UserT WHERE ComputerUsername = '" & ComputerUsername & "'"

As a guide you should always debug without error handling being enabled.

[VAer]

You are declaring ComputerUsername as a string but then not concatenating it as a string value in your SQL select.
Therefore because of your error handler you will get a error and go straight to the error handler. (Which you haven't shown us)

LoginUserSQL = "SELECT * FROM UserT WHERE ComputerUsername = '" & ComputerUsername & "'"

As a guide you should always debug without error handling being enabled.

Thank you. You are correct. With LoginUserSQL fixed, it seems it can run correctly. Let me test more.