Access add new SQL data

Question

I would like to add some Data in my SQL table...

Thats the actual build. - table User (Saves Useraccess data with column UserId, UserForename,UserSurname, Mail) - Formular add new User

My formular has 3 TextFields for UserForename, UserSurname, Mail and a button for adding the details.

By clicking the button the following code should be executed...

Sub Befehl85_Click()

Dim SQLText As String

SQLText = " INSERT INTO user " & _
"(UserID,UserForename,UserSurname,Mail) VALUES " & _
"('SELECT MAX(UserID)+1','UserForename','UserSurname', 'Test2');"

End Sub

The UserID should be filled automatically with the next free ID space (descending numbered)

I am looking for a solution like... 3 hours? :D

Could somebody help me?

thanks.

Make the user.userId as AutoNumber and you don't have to worry about next free number. — Krish
– Krish, Commented Feb 28, 2020 at 11:43

Kostas K. · Accepted Answer · 2020-02-28 11:54:26Z

2

As advised in comments by krish KM, change the UserID to AutoNumber and you won't have to worry about this field.

Then setup a query that you can pass the import values as parameters.

Query with parameters:

PARAMETERS [prmForename] Text (255), [prmSurname] Text (255), [prmMail] Text (255);
INSERT INTO User( UserForename, UserSurname, Mail )
SELECT [prmForename], [prmSurname], [prmMail];

Calling the above query in VBA:

With CurrentDb().QueryDefs("QueryName")
    .Parameters("[prmForename]").Value = [Value from TextBox] 
    .Parameters("[prmSurname]").Value = [Value from TextBox] 
    .Parameters("[prmMail]").Value = [Value from TextBox] 
    .Execute dbFailOnError
End With

answered Feb 28, 2020 at 11:54

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SunKnight0 · Accepted Answer · 2020-02-28 16:11:15Z

0

Assuming that by "formular" you mean form this is what you need:

Sub Befehl85_Click()

DoCmd.RunSQL "INSERT INTO user (UserID,UserForename,UserSurname,Mail) VALUES (" & DMax("UserID","user")+1 & ",'" & Me.UserForename & "','" & Me.UserSurname & "','" & Me.Mail & "')"

End Sub

As others have said you should convert the UserID to an AutoNumber and not manually increment. then you can skip that part.

answered Feb 28, 2020 at 16:11

SunKnight0

3,3611 gold badge13 silver badges8 bronze badges

Comments

Vlado · Accepted Answer · 2020-02-28 16:12:52Z

You do not need to use stored procedure for that simple task. First of all make the UserID field AutoNumber. Than (if the data resides in SQL sever) link the table in Access. Now you have 2 options:

The most natural option is to set form's RecordSource to your table ("SELECT * FROM user") and you do not need to code almost anything. Just add button which creates a new record and button which saves it. Use commands Me.AddNew and DoCmd.RunCommand acCmdSaveRecord in buttons _Click events.
If for some reason you want to use unbound controls use this code to insert a new row:

Sub Befehl85_Click()
   Dim SQLText As String
   SQLText = " INSERT INTO user " & _
       "(UserForename,UserSurname,Mail) VALUES " & _
       "('" & Me.UserForename & "','" & Me.UserSurname & "', '" & Me.test2 & "');"
   DoCmd.RunSQL SQLText 
End Sub

I need to tell you that this belongs to the basics in Access and I recommend you read some tutorials or watch YouTube.

Collectives™ on Stack Overflow

Access add new SQL data

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related