Solved Please Fix my Query

[HillTJ]

See query below; It doesn't Run due to a syntax error. I've split it into chunks and each chunk builds the string properly, but upon recombining. Doesn't work! Appreciate it.
CurrentDb.Execute "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)" & _
" SELECT '" & Me.[txtFirstName] & "' AS firstName,'" & Me.[txtLastName] & "' AS LastName,'" & Me.[txtPWD] & "' AS PWD,'" & "#" & Format(dtePWDDate, "mm/dd/yyyy") & "#" & "' AS PWDDate,'"

 

[bob fitz]

Just curious. Why don't you bind the form to the table.

 

[HillTJ]

@bob fitz , my attempt at an unbound form. It's designed to be a 'registration' form that i adapted from the logic of @isladogs logon system that i've been studying (and adapted successfully previously)

 

[cheekybuddha]

Dim strSQL As String

strSQL = "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)" & _
         " SELECT '" & Me.[txtFirstName] & "' AS firstName,'" & _
         Me.[txtLastName] & "' AS LastName,'" & _
         Me.[txtPWD] & "' AS PWD,'" & "#" & Format(dtePWDDate, "mm/dd/yyyy") & "#" & "' AS PWDDate,'"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

Adjust your code to put the SQL into a variable which you can then output to the Immediate Window (Ctrl+G) before executing.

Copy and paste the output here.

Spoiler: It should become obvious where the error lies

Extraneous trailing comma and quote at end of statement

 

[ebs17]

An append query in Jet in the SELECT variant requires a table, in this case a FROM part with a table that contains exactly one data record (otherwise duplication occurs). Alternatively, you use the VALUES variant of an append query. Stylistically the best way is to use a parameter query:
Stored Query: qinsUser

PARAMETERS pFirstName TEXT(255), pLastName TEXT(255), pPWD TEXT(255), pPWDDate Date;
INSERT INTO MsysUsers (FirstName, LastName, PWD,  PWDDate)
VALUES (pFirstName, pLastName, pPWD, pPWDDate)

Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery, _
                                  ParamArray QueryParams() As Variant) As Long
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' Paare Parameterfeld-Wert vorhanden
        If QueryExists(MyDB, AnyQuery) Then
            ' gespeicherte Abfrage
            Set qd = MyDB.QueryDefs(AnyQuery)
        Else
            ' SQL-Anweisung
            Set qd = MyDB.CreateQueryDef(vbNullString, AnyQuery)
        End If
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        qd.Execute dbFailOnError
        ExecuteParamQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End If
End Function

Private Function QueryExists(ByVal MyDB As DAO.Database, ByVal QueryName As String) As Boolean
    Dim qd As DAO.QueryDef
    For Each qd In MyDB.QueryDefs
        If qd.Name = QueryName Then
            QueryExists = True
            Exit For
        End If
    Next
End Function

' Call
ExecuteParamQuery CurrentDb, "qinsUser", _
      pFirstName, Me.txtFirstName, pLastName, Me.txtLastName, _
      pPWD, Me.txtPWD, pPWDDate, dtePWDDate

 

[Pat Hartman]

Back to Bob's question. The form should be bound. You are using Access for some reason to develop this project. The best feature of Access is its support of bound forms. Ignoring the best feature of your platform doesn't seem like a great idea even if you are "experimenting". Experiments should lead to useful processes.

Here's a different take on logins and security. The login form is bound and instead of being closed, it hides itself and remains open throughout. That allows other procedures to reference data on the hidden login form as each form checks the security when the user tries to do something.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

 

[HillTJ]

All, I cannot get the select query to work & admit defeat. I will adopt a bound form instead. Let Access do the work. Steep learning curve sometimes....
Thanks for the support.

 

[HillTJ]

@cheekybuddha , I ran your code. Still generated errors. The debug.print did not show anything obvious to me, but I elected to revert to a bound form. Much easier to work with for us amateurs. Appreciate your input. Got it going as we speak.

 

[Gasman]

@cheekybuddha , I ran your code. Still generated errors. The debug.print did not show anything obvious to me, but I elected to revert to a bound form. Much easier to work with for us amateurs. Appreciate your input. Got it going as we speak.

The benefit of @cheekybuddha's method is you can post the output back here if you cannot spot the error.
I always advocate the same advice. Putting code within code tags helps a great deal as well?

One error I can spot is that you have date (whilst correctly enclosed within #), is also enclosed again with ' ????

So whilst bound option is better, you still have to learn the sql syntax?

 

[isladogs]

@HillTJ
Just to point out that for security, you really should not be saving users' passwords in a separate table.
If you really must do so, make sure the passwords are encrypted.

If you are adapting my example app, there is already a table to store user login info (without the password)

As for your SQL, @cheekybuddha showed you in his spoiler what part of the problem was.
Extra single quote and comma at end ...but also errors in the date field part

This SQL should work:

Dim strSQL As String

strSQL = "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)" & _
         " SELECT '" & Me.txtFirstName & "' AS FirstName," & _
         " '" & Me.txtLastName & "' AS LastName," & _
         " '" & Me.txtPWD & "' AS PWD, #" & Format(dtePWDDate, "mm/dd/yyyy") & "# AS PWDDate;"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

However, @ebs17 explained why you should normally use the VALUES variant of the INSERT sql in this case (with or without parameters).

But to reiterate....you shouldn't be doing this at all either with a bound or an unbound form

 

[bob fitz]

Just to point out that for security, you really should not be saving users' passwords in a separate table.

So can you say where you would recommend that they be stored.

 

[cheekybuddha]

So can you say where you would recommend that they be stored.

Save a hash of the password.

Then, when the user inputs the p/w, hash it and compare the hashes.

 

[Pat Hartman]

And let us not forget, if we are that worried about passwords, we ought not to be using Jet/ACE.

 

[isladogs]

Ideally, I would use Active Directory and not store passwords within any Access app.
That would avoid the risk of any user being able to read other users' passwords - and that includes the program administrator

If AD isn't available or you do need to store passwords, then only store those in one hidden table using strong encryption such as RC4 or better still AES. Make sure the encryption cipher is not easily located and is itself encrypted using another method.

Sorry but I don't accept that hashing the passwords alone is sufficient.
However, doing that in conjunction with encryption is better than doing either method alone

Whilst Access can never be close to 100% secure, it is still wise to take reasonable precautions to avoid risks re data protection.
There are many articles on my website covering different aspects of security

 

[cheekybuddha]

Sorry but I don't accept that hashing the passwords alone is sufficient.

???

 

[isladogs]

???

???
I was referring to simple modification of text strings e.g. by the use of XOR - far too easy to crack
What were you referring to?

 

[cheekybuddha]

???
I was referring to simple modification of text strings e.g. by the use of XOR - far too easy to crack
What were you referring to?

SHA512 (with several passes)

 

[cheekybuddha]

However, doing that in conjunction with encryption is better than doing either method alone

This method leaves a vector to retrieve the actual password.

You actually have no need to know the password itself (unless you are using it to encrypt data in the app, which I doubt you would), so why leave an opening?

 

[isladogs]

Agreed. That was my original point about nobody should be able to read the passwords
If you have code to provide SHA512 encryption, I'd be very interested in looking at it.

 

[KitaYama]

Ideally, I would use Active Directory and not store passwords within any Access app.

Unfortunately it doesn't work for shared PCs. And I think you're aware any company or organization has a load of them.

then only store those in one hidden table using strong encryption such as RC4 or better still AES.

I have seen and studied your RC4 sample database. First time hearing AES. Do you have an article on this too?

@cheekybuddha
I'm also interested in the method you're suggesting. But hard to digest it. Do have a database to show how you do it?

thanks.