Excel Vba Insert Into Access Database

Creating a Button to insert Records into an Access Database with VBA.

This tutorial demonstrates how to build a database stores words and their definitions. A beginner's level of VBA is and some knowledge of Access is recommended.

Notes: Anything in brackets then followed by right arrows denotes the path to reach a certain button. For example: to create a form. (Create > Form Design)

We want to create a database that stores our wo r ds of the day. We want a database that should show the word of the day, and a short definition of what the word means. We want a form for the user to be able to upload the word and the definition. We want to be able to check the form to ensure that the form is actually filled in before the new word is submitted.

To start simple: our database should contain 1 table containing the fields 'Words' and 'Definitions'. Both Fields are short texts.

1. Open Access and create a new file

2. Create a new table design (Create > Design)

3. Go to the design view (Home > View)

4. Create both field: 'words' awornd 'definition'

5. Make sure their data type is short text

6. Go to datasheet view (Home > View)

7. Add at least 1 new word and definition as sample data

Note: Upon creation, an ID column with an Autonumber data type is created. Do not delete that autonumber.

When going through these steps. Remember

• Try to avoid spaces when naming tables and fields. This makes it easier to program with
• Use Underscores or Capital letters between words
• Keep naming convention the same.

We now want a form that has 2 blank text boxes. 1 for the new word, and 1 for the definition.

1. Create a new form design (Create > Form Design)

2. Go to the add existing Fields tab and add the word and definition fields from the table just created. (Form Design Tools > Design > Add existing Fields)

1. Click on the word textbox and click on the Property sheet (Form Design Tools > Design > Property Sheet [beside add existing fields])

2. Delete the Control source contents (Property sheet, Data, Control Source)

3. Repeat step 4 -5 with the definition textbox

Anything that the user interacts with on a form is known as a control. Textboxes, comboboxes, labels, etc… are all considered controls.

When we add fields using the drag and drop method, we are creating a connection between the form and the table via textboxes. This allows the textboxes to show us what words the datasheet has; however, this not what we want happening. We do not want to see data; instead, we want to input data to be uploaded. The action of deleting the control source solves that problem for us.

The drag and drop method is useful because it automatically names the textboxes to that of the fields in the table. If you were to make your own textbox, you would have to create a sensible name for the textbox yourself.

Run through the properties tab for the Textboxes and the label to see the different values you can update. A challenge is to change the label font to at least 24pt and have it centered.

Play with the Align and spacing of your controls (your text boxes and label) by going to (Form Design Tools > Arrange > Align/Size/Spacing)

Now, in the form design, select the command button and put one into your form. You will be guided by the wizard on making the button. The first selection is unimportant as we will be editing that using VBA. The next two prompts ask you to create a text to follow and to name your button meaningfully.

To add VBA to the button. Select the button then go to the property sheet. Go to event and click on the dropdown list and change it to an event procedure. Then click the ellipsis and the code editor should open. (Otherwise hit alt-F11)

On opening the Code editor, a new sub has been created. This is an Event sub. The button Created is called Add_new_Word and the event on clicking the button is therefore Add_new_Word_Click(). This is true for any control

To see all possible events that we can code for you can select the dropdown list to the right

When we click the button, we want to capture the values that a user has input into the textbox values.

We create 2 variables (sWord and sDefiniton) to store the textbox values by retrieving the controls value property. Then we call them using Debug.print

If we go to our form and type in some words and push the button the code editor's intermediate pane (ctrl-G) will display the values that were just input. This shows that we have successfully taken the values from the textboxes and stored them in variables.

Now we want to create our INSERT INTO statement. To do so let's just copy and paste what was written above.

INSERT INTO [table_name] (Column1, Column2, Colum3…) VALUES( Value1, Value2, Value3…)

When we copy that in we'll run into an error. VBA doesn't recognize SQL statements so we'll have to write it as a string. We do this by creating a variable called ssql and making it equal to the SQL statement wrapped in quotation marks.

Now we substitute our own Table names, and columns into the SQL statement

Note: if your column names contain spaces in them you must wrap the column name in square brackets [].

The next step is to place the variables taken into the SQL statement. We have to break up the string to do that. We first close off the string as the end of the words Values and delete everything to the right of the statement.

We now need to concatenate our variables. We do this using the & symbol. Between the variables we need a comma, so we need to concatenate this symbol between the variables ",". Furthermore, we need to ensure that the variables are turned into strings so we need to wrap them in single quotation marks ",'". The final product in code and text is shown below.

ssql = "INSERT INTO [tbl_wordbank] (Word,Definition) VALUES('" & sWord & "','" & sDefinition & "')"

For much longer SQL statements it is recommended to write new lines for ease of reading. It also makes it easier to copy and paste values. We jump lines by writing an underscore after the ampersand sign and then hitting enter. We then use tabs to align the values correctly.

To execute this SQL statement, we use the doCmd.RunSQL command. Using the SQL string variable we've created.

If we go to the Form now we can test out our button.

On returning to the table we can see that what we entered shows up on our table.

The grouping of quotation marks is the most challenging portion of writing these statements. This is how the grouping looks. The cyan and the yellow show how the single quotations wrap their respective variables.

Excel Vba Insert Into Access Database

Source: https://medium.com/@ngai_to_lo/creating-a-button-to-insert-records-into-an-access-database-with-vba-b64405260cf4

Share this post