This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.
www.isladogs.co.uk
His solution didn't involve VBA but was remarkably clever.
Gents/Ladies, although I've elected to use a bound form for my 'registration' ,It still bugs me that I cannot derrive the correct SQL insert into string syntax.
This is my code & the project is for me to learn, but nothing important. I just don't wish to be beaten
And this is an example of the debug.print ;
"INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)"SELECT 'terry' AS firstName,'hill' AS LastName,'(vP.åŸ0è¢P¯Aºiµ#/Ñ' AS PWD,'#25/10/2022#' AS PWDDate;
"
The error message is also attached.
As I said, I'm just trying to learn why the string does not work.
Appreciate any assistance (go easy i'm still very much keen, but an amateur)
Code:
Option Compare Database
Option Explicit
Private Sub AddNew_Click()
Dim txtPWD As String
Dim PWD As String
Dim Key As String
Dim strSQL As String
'On Error GoTo Error_Handler
Key = GetKey
txtPWD = fRunRC4(Me.[txtEmail], Key) ' this encrypts the users email address & gets filed 'Key' from a saved database property
PWDdate = Date
strSQL = "INSERT INTO MsysUsers (FirstName, LastName,PWD, PWDDate)"
Debug.Print strSQL
strSQL = strSQL & """SELECT '" & Me.[txtFirstName] & "' AS firstName,'"
Debug.Print strSQL
strSQL = strSQL & Me.[txtLastName] & "' AS LastName,'"
Debug.Print strSQL
strSQL = strSQL & txtPWD & "' AS PWD,'"
Debug.Print strSQL
strSQL = strSQL & "#" & Format(PWDdate, "dd/mm/yyyy") & "#" & "' AS PWDDate;"
Debug.Print strSQL
strSQL = """" & strSQL
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Me.lblInfo.Caption = "New user " & " " & Me.[txtFirstName] & " " & Me.[txtLastName] & " " & "has been successfully added"
exit_proc::
Exit Sub
Error_Handler:
[ATTACH type="full"]104084[/ATTACH] Call DisplayErrorMessage(Err.Number, "Add New User")
Resume exit_proc
End Sub
In access/jet/ace you must specify a FROM table when using INSERT ... SELECT syntax, and you are then likely to get as many records inserted as there are rows in the table.
To insert a single record use INSERT ... VALUES syntax instead:
Note you must also pass dates to SQL in an unambiguous format (irrespective of you local regional date format), which means either US date format (mm/dd/yyyy) or ISO date format (yyyy-mm-dd)
With the date in your example you would have been lucky in using UK date format since it can only be interpreted as 25th October, but you would have come a cropper if you had tried to insert 11th October. It would have been interpreted as 10th November.
@cheekybuddha , thanks once again. I hate to be beaten by lack of understanding. I've elected to use a bound form, but was still not happy that i could not achieve my initial approach. Thanks will give it a go.
Equivalently, to insert only one record, you can use a Recordset-AddNew. This is somewhat clearer due to the omission of data type-specific formatting.
Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("SELECT FirstName, LastName, PWD, PWDDate FROM MsysUsers WHERE False", dbOpenDynaset)
With rs
.AddNew
.Fields("FirstName") = Me.txtFirstName
.Fields("LastName") = Me.txtLastName
.Fields("PWD") = Me.txtPWD
.Fields("PWDDate") = Date
.Update
.Close
End With
Regardless of what else you do, you should definitely have the ability to write into a table.
To all contributers. Got it working as an unbound form, but elected to go bound for simplicity. Something to be said in letting access manage it'self. Appreciate all recommendations and i achieved my 'learning' goal. Now onto the next challenge....